Hlookup with external reference

**sahjid** · 07-02-2018, 11:55 PM

Hi

I am trying to write a HLOOKUP formula that retrieves data from a tab in another workbook. This tab name changes each week (It is the weekending date). Is it possible to place the tab name in a different cell and only have to update that cell each week to change the tab referenced? So the formula could be in A1 and the tab name in A2. I have about a hundred HLOOKUPs and have to update them every week. I was trying to do it with Concat but can not seem to make it work.

HLOOKUP($C6,'G:\XXXXXX\Finance\Payroll\Income Schedule\2018\[2018 Group Income Schedule.xlsx]24 JUNE 2018'!$C$6:$AB$200,8,FALSE)

Any help appreciated!!

Thanks..

**FDibbins** · 07-03-2018, 12:04 AM

To include a reference to a sheet name, you would need to use INDIRECT(), however, this only works on open workbooks. If this would be OK, let us know and we can try and put something together for you - and its always better if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

On a side note, please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. The file in your post indicates at least 2007

**scottiex** · 07-03-2018, 12:58 AM

He will need about 3 more posts to be able to upload a file wont he?

Until then Sajid,
use up another post by giving feedback on your attempt with giving the indirect a go using &, "" and the cell references to build your string eg the very basic example below

Please Login or Register  to view this content.

Obviously the info we are missing is where you want to put the date (and maybe the year?) and if it needs to be reformatted into your dd mmmm yyyy format you have there (BTW will the first of July be 01 July or just 1 July?)