How do I use HLOOKUP over multple worksheets

[tmd_63]

I have a multiple sheet workbook that has 7 dates on a row with values listed under those dates in up to 7 rows.
I also have a collection sheet with a moth of dates in a column.
I want to search all 53 sheets for a date on the collection worksheet. When a date is found, I need to copy the data from the rows below that date into the cell referenced.
The dates are stored in Row B5 to H5 on each sheet and I need the data from row 7 or row 11 for example where the date in row 5 matches the date in cell AB4
I have tried to use this code but it doesn't work!!!

Code:

Please Login or Register  to view this content.

How to do it?

[JohnTopley]

Formula has to entered as array formula ...

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Glenn Kennedy]

Your syntax was a bit out. Try this:

=HLOOKUP(AB4,INDIRECT("'"&INDEX(Sheetlist,MATCH(TRUE,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$B$5:$H$11"),AB4)>0),0))&"'!$B$5:$H$11"),3,0)

See my mock-up sheet. and yes, it does have to be entered as an array formula.

[Glenn Kennedy]

John's correct (I think) . On reflection you just needed to array enter it.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[tmd_63]

Hi. Thanks for the reply.
Unfortunately , I still get #N/A as the result.
I copied the formula. Ctrl V to paste it into the cell then pressed Ctrl+Shift+Enter and the curly brackets appeared. but still no results.

[Glenn Kennedy]

Can you confirm whether or not it was working, as expected, in the mock-up sheet that I attached at Post 3? Is it possible to attach your file here?

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.


2/3 sheets will do.

[tmd_63]

I do not understand it???
I downloaded the sample workbook from above and that works fine. It was a .xlsx file not an excel 2003. But even when I save it as a 2003 .xls file, it still works. But my files?? Nothing.
I will need to remove some data from my file first as it contains sensitive data.

[tmd_63]

Ok. I have attached the spreadsheet.
This is an excel 2003 file.
The lookup is currently around cells AB4 on tab Month10.
What I am looking for is a column of data for each wage row so that I can sum the hours per month for each wage type (normal, overtime, double time etc).

[Glenn Kennedy]

The problem is caused by the fact that you are back-saving your file to an out-dated (pre-Excel 2007) format. there are problems associated with using Named Ranges in older versions of Excel that are resident in other sheets. There may be a workaround (dim memory..).

Can you either use a less prehistoric Excel format (.xlsx) or move the NR to the sheet where you want to use it?

I'll try to remember the workaround....

[tmd_63]

As the NR needs to be on each Month worksheet, Can the named range be the same on each sheet? Or would I need to name each differently for each sheet (not a problem as there are only 12 sheets).
I need to ensure this can be used on older PC's that only have Office 2003 as the latest version. Microsoft screwed the pooch when they brought out Office 2010 as it cannot be dual installed with 2003.

[JohnTopley]

Try

=HLOOKUP(AB4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$5:$H$5"),AB4)>0),0))&"'!$B$5:$H$11"),3,FALSE)



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


returns 7.5

This was Glenn's formula which you did not use.

[Glenn Kennedy]

=HLOOKUP(AB4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$5:$H$11"),AB4)>0),0))&"'!$B$5:$H$11"),3,0)

seems to be OK. I'm out for a while...

[tmd_63]

Tried

Please Login or Register  to view this content.

and got #REF instead
Tried

Please Login or Register  to view this content.

And.....


WooHoo, it worked. Thank you Glenn

[tmd_63]

I have had to change the other columns

Please Login or Register  to view this content.

But I can now get all 5 rows in separate columns and it doesn't matter which week sheet holds that months dates. Perfect.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[tmd_63]

Thanks. Was looking for the [SOLVED] tools.

[Glenn Kennedy]

You're welcome and thanks for the rep.