Trying to simplify a lookup

[wageslave101]

I'm trying to reduce the amount of manual re-entry to a spreadsheet which contains many lookups in the format;

=IF(VLOOKUP(TODAY(), '[FirstName LastName.xls]Entry'!A1:A501, 1, TRUE)<TODAY(), "0", '[FirstName LastName.xls]Entry'!$L$32)

Is there an way that I can get form the string used for the .xls from a cell reference.

For example if the FirstName is in cell B1 and the LastName is in C1 can I join the two together to use in the lookup rather than the name manually entered in?

Cheers,
WageSlave

 

[ravishankar]

Hi
D1 = B1 & " " & C1
Ravi

 

[wageslave101]

Hi Ravi,

Thanks for the reply. I had tried donig that, however the problem I encountered was when I tried to use it in the lookup.

The lookup is checking for todays date in the Entry Sheet of the persons workbook.

Assuming that I use D1 to concentate B1 and C1 to make the name component of the sheet or even

D1 = C1 & " " & D1 & ".xls"

When I try to use it in the lookup

=IF(VLOOKUP(TODAY(), D1!A1:A501, 1, TRUE)<TODAY(), "0", 'D1Entry'!$L$32)

I had tried creating the entire string in the D1 cell including the range, but this also generates an error.

Still pondering,
WageSlave

 

[ravishankar]

HI
your syntax is not right. if D1 is a file name you should have
"[" & D1 & "]" & entry!A1:A501
Ravi

 

[Aladin Akyurek]

Hi Ravi,

Thanks for the reply. I had tried donig that, however the problem I encountered was when I tried to use it in the lookup.

The lookup is checking for todays date in the Entry Sheet of the persons workbook.

Assuming that I use D1 to concentate B1 and C1 to make the name component of the sheet or even

D1 = C1 & " " & D1 & ".xls"

When I try to use it in the lookup

=IF(VLOOKUP(TODAY(), D1!A1:A501, 1, TRUE)<TODAY(), "0", 'D1Entry'!$L$32)

I had tried creating the entire string in the D1 cell including the range, but this also generates an error.

Still pondering,
WageSlave

One of:

=IF(LOOKUP(TODAY(),INDIRECT("'["&B1&" "&C1&".xls]Entry'!A1:A501"))<TODAY(),0,INDIRECT("'["&B1&" "&C1&".xls]Entry'!L32"))

=IF(VLOOKUP(TODAY(),INDIRECT("'["&B1&" "&C1&".xls]Entry'!A1:A501"),1,1)<TODAY(),0,INDIRECT("'["&B1&" "&C1&".xls]Entry'!L32"))

INDIRECT requires that the target workbook file is open. If you want to work with closed books, substitute INDIRECT.EXT for INDIRECT, which is available from the free morefunc.xll add-in.