Using VLOOKUP across multiple worksheets for calculations

See if this custome lookup function will do as you need

If you are asking to pull a value from all the sheets, then you will need code to loop through all the worksheets using the FIND method

See this add in from Chip Pearson: Find All VBA Function

You'll find a way to do this, here.

https://sites.google.com/a/madrocket...ssistant/files

See if this is of any help: FlexFind

If Palmetto's suggestion does not works for you, if you like upload a small sample workbook to work on it. I have an idea using sumif and Indirect functions..

This one looks to work for me. For you?

I have an idea using sumif and Indirect functions..

Yes, that is what I would do.

Can you post the formula you came up with?

It seems that folks use the file attachment option a lot here!

That's a good option to have but then we can't see the solution unless we download the file.

Did you create the Named Range "List" as Fotis' example has?

You would need to list the names of each sheet you need searched, then select that range and apply the Named Range: List

Then it should work

- Moo

@ Tony Valko

We create a list with all the sheets and Named it as "List"

Then i used this formula.

Please Login or Register  to view this content.

..It seems that folks use the file attachment option a lot here!

Yes this is true. For me(as i don't speak English very well) is the best way to understand the point!

Nice to see here Tony! I have learned a lot from you.

@ neljan

I just saw your reply.

Did you do these that MOO(thanks) told?

The reason I suggest posting the formula is...

Say an hour after you post I post the exact same formula.

However, I didn't download your file so I don't know what solution you came up with. Our formulas are exactly the same so someone may think I just copied your solution and pasted it into a reply when that is not the case.

Anyhow...

For this application there is no need to make a list of the sheet names since they appear to be a sequence of numbers. We can do something like this:

Sheets named 1 to 133...

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:133"))&"'!B5:B1000"),$B5,INDIRECT("'"&ROW(INDIRECT("1:133"))&"'!C5:C1000")))

Also note that we don't need to use the dollar sign for cell references that are quoted in the INDIRECT function. They will automatically be evaluated as absolute references and they will not change if the formula is copied to other locations.

Nice to see here Tony! I have learned a lot from you.

Thank you!

I hope to make this forum my "home forum" so you'll see me here often.

..Say an hour after you post I post the exact same formula.

However, I didn't download your file so I don't know what solution you came up with. Our formulas are exactly the same so someone may think I just copied your solution and pasted it into a reply when that is not the case.

Here the things are different Tony. You have to download my file if you answer a hour...later. And as i said for many of us that we don't speak English well, this is a valuable tool.

.

.Also note that we don't need to use the dollar sign for cell references that are quoted in the INDIRECT function. The will automatically be evaluated as absolute references and they will not change if the formula is copied to other locations.

Thanks for this. I didn't know.

...For this application there is no need to make a list of the sheet names since they appear to be a sequence of numbers. We can do something like this:

I know this,if the sheets are sequence of numbers. In this case i didn't thought that could be. . From whom i know?

http://www.mrexcel.com/forum/excel-q...uct-sumif.html

If cell B4 contains the criteria and it contains 'TrainName1' then only cells with that specific entry will be summed. It should not sum cells that contain 'TrainName1 with 10 carriages' .

I downloaded your file but the sheet names don't match your description in your first post:

=VLOOKUP($B4,'1'!$B$3:$AZ$1000,3,0)
+VLOOKUP($B4,'2'!$B$3:$AZ$1000,3,0)
+VLOOKUP($B4,'3'!$B$3:$AZ$1000,3,0)
+VLOOKUP($B4,'4'!$B$3:$AZ$1000,3,0)
+VLOOKUP($B4,'5'!$B$3:$AZ$1000,3,0)

(and so on - 113 worksheets)

In the file the sheet names are just random numbers. Is that just to demonstrate what you want or do the sheet names follow the numbering sequence as shown above?

If the sheet names follow some sort of sequential naming pattern then you don't need to make a list of the sheet names.

OK, then in that case you would need to use a sheet name list.