Using Worksheet name as a Lookup value

[AussieExcelUser]

Is there anyway of using the name of a worksheet in a HLookup formula?

I have worksheets named after months of the year and a master table containing data in columns where the column headings are the months of the year.

What I am wanting to do is, rather than have to insert the month into a cell in the worksheet and reference the cell, is use the actual worksheet name in the formula such as (but this does not work):

=HLOOKUP(sheetname,BFSDATA!H1:AE20,14,0)

[PeterB]

What I think you want to do is use a function to call up the sheetname within your HLOOKUP. Not aware of such a function.

However, just in case you want to just use the sheet name in the HLOOKUP, then you do it this way:

=HLOOKUP("January",BFSData!H1:AE0,14,FALSE) for an exact match;
=HLOOKUP("January",BFSData!H1:AE0,14,TRUE) for nearest match.

[dominicb]

Good morning AussieExcelUser

How about this :

=HLOOKUP(MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255),BFSDATA!H1:AE20,14,0)

Just be aware that the file has to be a saved file for this formula to work.

HTH

DominicB

[PeterB]

Nice one ... but where did that come from? - The Help file doesn't tell us the format of the data coming back from the CELL function. Would be nice to know where to look for such info.

Ignore this - tried function on its own and you get the format.Shame Help file does not clarify.

[PeterB]

It appears that data has to be present in A1 at some point for this to work, as well?

Again, ignore this - sheet needs to be recalculated, which is why it does not work straight after saving, but does when data put in a cell.

You could, however, shorten the equation slightly , by not referring to A1 in the CELL functions, so it becomes :

=HLOOKUP(MID(CELL("filename"), FIND("]", CELL("filename"))+ 1, 255),BFSDATA!H1:AE20,14,0)

[dominicb]

Hi PeterB

You could, however, shorten the equation slightly

Ah yes. Well spotted.

This function isn't really intended to do what it does but is one of those formulae that has been cleverly manipulated to deliver something else - in perhaps much the same way as SUMPRODUCT has over the years. This is probably why it is completely overlooked in the help file.

There are quite a few oddities like this to be found in Excel - try finding any reference to the DATEDIF function in the help files. It was there in XL2000 (but not before or since... strange).

DominicB

[AussieExcelUser]

Absolutely bloody marvelous – works a treat. Thanks very much gents!

Now why won’t the range lookup reference advance 1 in each cell that I copy the formula to? I’ll copy the formula (now looks as follows) and then paste down the column and the lookup reference stays at “14” when I need it to advance to 15, 16, 17, 18, 19, 20 and so on?

=HLOOKUP(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),Data_Range,14,0)

Such a simple thing is really bugging me, any tips?

[Paul]

To update 14 to 15, 16, etc. as you copy downward, replace the 14 with row(a14)

This way, when you copy down, it will change to row(a15), row(a16), etc., equivalent to 14, 15, 16..

[AussieExcelUser]

Thanks Paul, problem solved.

Cheers,

Grant.

[PeterB]

Thanks Dominic for the input - I will now have to find out about the SUMPRODUCT and DATEDIF tho'...

[daddylonglegs]

You could, however, shorten the equation slightly , by not referring to A1 in the CELL functions, so it becomes

That's not a good idea. Using A1 (or any other cell reference) ensures that you get the sheetname of the sheet containing the formula. If you remove A1 then this formula refers to the last cell changed within the workbook, if that cell was on another worksheet then your formula will refer to the wrong worksheet.

...also using just row(a14) in the formula can be dangerous. If you add or delete rows on the worksheet you could get the wrong result, easier to use INDEX/MATCH, i.e.

=INDEX(BFSDATA!H14:AE14,MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),BFSDATA!H$1:AE$1,0))

when you copy this down the lookup range is still fixed, by $, to row 1 but the return range will increment