Excel 2007 : Need Help with Indirect Hlookup.

**lmuncy** · 06-06-2012, 05:36 PM

This works but i need the sheet name to be variable. Thanx for any Help
Would also like to be able to get sheet names from drop down list if possible
without creating a list of sheets.

=HLOOKUP(E9,'Day1'!$C$12:$AC$38,27,FALSE)

**JBeaucaire** · 06-06-2012, 05:50 PM

You will need to create a list of sheet names (on a hidden sheet perhaps) then you will need to NAME that range of cells holding the list. Once you have your named range, you can now use it as a Data Validation drop down List source.

Assuming the drop down is in A9, the formula would become:

=HLOOKUP(E9, INDIRECT("'" & A9 & "'!$C$12:$AC$38"),27,FALSE)

**lmuncy** · 06-06-2012, 06:36 PM

Thanx for the quick response and help.

It works but not quite the way i was hoping. E9 Thru ac9 are drop down list of Headers and b10 Thru B60 is a list of dates (sheet Name).
But unfortunately Row 9 is not variable to the sheet names in Column B.
What i want to happen is the columns of info to be client locateable so he can put the data for summary in order of his preference.
Column B is sheet names and Row 9 is data columns. So when column b (sheet) is selected and row 9 data is selected be it c9 d9 e9
the drop downs allow it to be d9 c9 f9 z9 l9 or whatever order my client prefers to see. Preferably whithout #ref! when nothing is selected
in the the column or row.

**JBeaucaire** · 06-06-2012, 06:38 PM

Guess it's time to post a workbook showing all this. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

**lmuncy** · 06-06-2012, 06:44 PM

Thanks for the help again

**JBeaucaire** · 06-06-2012, 06:57 PM

In D10, put this formula, then copy down and across the table, no editing needed:

=IFERROR(HLOOKUP(D$9, INDIRECT("'" & $B10 & "'!$C$12:$AC$38"),27,FALSE), "")

**lmuncy** · 06-06-2012, 07:22 PM

Sorry to be your worst night mare Jerry. But its getting better. Now the problem is since row 9 (Drop Downs) refers to sheet (day1) for Headers. It still wont calculate the other sheet. It returns blanks. The worksheet automaticaly name themselves when a date is entered in A32. Then when you put coresponding date in the lookup i need it to get data from corresponding sheet. I tried using indirect in the source on the drop down tabs with no luck. I dont know if its the syntax or just not possible. Thanx again

**JBeaucaire** · 06-07-2012, 12:30 AM

No, it appears the problem is inconsistent sheet design.

Day1, the headers are in row12, so your HLOOKUP works fine on that sheet.

The sheet new has headers in row11, so HLOOKUP cannot see the headers. If you add the same empty row 11 into the sheet new as you have in sheet Day1 the formula works on that sheet, too. Hmm, it appears the new sheet has the totals on a different row, too, because your time ranges are different. Consistency in sheet design would eliminate all these issues.

**JBeaucaire** · 06-07-2012, 12:37 AM

Put the empty row11 into sheet new so the top row is the same on all sheets, then use this formula instead, it will INDEX the data table no matter how far down it goes (up to 100 rows), then it will find the Totals row on that sheet and bring back the answers from the row in the correct columns:

=IFERROR(INDEX(INDIRECT("'" & $B10 & "'!$A$12:$AC$100"), MATCH("Daily Totals:", INDIRECT("'" & $B10 & "'!$A$12:$A$100"), 0), MATCH(D$9, INDIRECT("'" & $B10 & "'!$A$12:$AC$12"), 0)), "")