vlookup same value across multiple sheets

[shakethingsup]

Hi,

I used the forum search but can`t exactly find what I`m looking for. I didn`t click on every post but I read through relevant titles. Maybe someone can point me to a thread that has this question.

I have a summary sheet which I need to populate based on multiple sheets in my workbook.

	Property 1	Property 2	Property 3	Property 4
Hydro
Water
Gas

<tbody>
</tbody>

In each sheet named property 1, property 2, property 3, etc., there will be a hydro line with a value. I need to populate the rows across with values from each sheet.

solution 1:
Use a vlookup and simply search each sheet individually.

solution 2:
is there another way to do this?

Thank you

 

[MrKowz]

I'm assuming the value we're comparing against is in column A on each sheet, and the returned value is in column B.

Try:
=VLOOKUP(A1,INDIRECT(B$1&"!A:B"),2,0)

 

[shakethingsup]

Hey MrKowz - why use the indirect function?

I tried it with the indirect function, I got #REF!

I tried it without but still referencing B$1&"!A:B" - I got #value

My actual value is in column 11 in tab 1 - Queens variances.

 

[MrKowz]

The INDIRECT function allows us to use cell values to affect the reference we want to look at. In this case, we want to adjust the worksheet reference by using the values in row 1. Because your worksheets have spaces in their names, the formula I provided initially didn't work. Try the below function, it wraps the worksheet name (B$1) in single quotes, which will allow the function to operate as intended.

=VLOOKUP(A3,INDIRECT("'"&B$1&"'!A4:S100"),11,FALSE)

 

[shakethingsup]

Never mind, figured it out. It was a matter of putting quotes in the right places!

THank you!

 

[shakethingsup]

lol thanks!!! yes that was all I needed.

 

[MrKowz]

lol thanks!!! yes that was all I needed.

Wonderful - glad we were able to sort it out and it works for you. Thanks for the feedback!

 

[SpillerBD]

solution 2:
is there another way to do this?

If there will only be one line on each of the Property sheets you're generating your summary from, not really. Your description of your current data suggest very simple and limited number of lines.
However, once things (if?) get more complicated where you could have multiple lines to sum together you would want a more flexible solution.
Currently with Excel 2016, there is the ability to combine the multiple tables to generate a "new" data source (via Data Model) and then plug that data into a Pivot Table.

 

[shakethingsup]

Let me look into "data model" - I'm not sure what this is but I saw an icon.

You're right could get more complicated. I didn't go into the full process as to how I combine aggregate the data. Let me check out and learn the function you suggested and see how I could apply it.