Excel - Finding Certain Data

[Darthshearer]

Guys

I have a spreadsheet with approx 15 sheets in. In the sheets are approx 150 lines of data each sheet.

I want to locate certain data that is on a line with a certain text string and sum their total.

I.e on a line I have 150 in one cell and the word UDL in the other. Is there a way I can on one sheet run a macro or something to find all the cells that have 150 in that are UDL's?

For example accross 5 sheets I have 10No. 150's therefore my sum cell will show 1500.

Cheers

 

[MYstIC G]

Buh? Not following ya amigo.

Are you saying that you've got 15 sheets of completely random data, i.e. your "UDL" could be in row 1 on sheet 1, row 132 on sheet 2, etc?

 

[Darthshearer]

Buh? Not following ya amigo.

Are you saying that you've got 15 sheets of completely random data, i.e. your "UDL" could be in row 1 on sheet 1, row 132 on sheet 2, etc?

Indeed.

 

[MYstIC G]

Pretty sure the best way to do that would be with a lookup

 

[MYstIC G]

Here's an example for ya

=LOOKUP(Value,Column to look for value in,Column to return value from).

This would only pick up though if there is a single instance of the value your looking for.

 

[Darthshearer]

That does work, however I want this, not sure I have been clear so apologies.

I have say 10 cells with different numbers in. Alongside those cells are 10 more cells with a description of the numbers.

I want to be able to get the totals of a certain desciption. I.e out of them 10 numbers 5 belong to a group for example 'A'. Now I could simply use SUM but as I have 15 sheets and a lot more data I a) dont want to miss cells and b) spend a long time.

So is there a way of finding all the A's and adding them together?

 

[MYstIC G]

Will all the A's be the same value?

 

[Darthshearer]

Will all the A's be the same value?

No

Im asking the impossible aint i?

 

[MYstIC G]

Personally I'd just copy & filter the tables but I'm guessing you want this to be automated.

 

[Darthshearer]

Aye because I have another 20 work books to do lol

 

[MYstIC G]

Can't you dump it into Access, do a union query and then output the results using filtered reports with totals?

 

[Vae]

What about using sumif

If column A has all the items you want to look up and column b the values to sum and B1 is a cell containing what you're looking for e.g. UDL

=SUMIF(Sheet1!A:A,B1,Sheet1!B:B)

You can then copy this formula for each sheet and also create several lines just changing the contents of B1 if you are looking for several different items.

Another option could be to try pivot tables?

 

[Darthshearer]

Vae, that my friend is spot on!