Using Defined Names

Mr Nick · Mar 21st, 2002, 04:49 AM

Is it possible to use a defined name to reference a tab on a worksheet like in the example below where I have used the name "ValidTAB"

=IF(ISNA(VLOOKUP(T12,'[VD.xls]ValidTAB'!$A$1:$R$957,2,FALSE))

If it is indeed possible, how do I format the reference to this defined name?
defined name

paelinck · Mar 21st, 2002, 07:31 AM

I think it should be possible using the Indirect function. Your function would look like this:

=IF(ISNA(VLOOKUP(T12,INDIRECT("'[VD.xls]'"&A1&"'!$A$1:$R$957"),2,FALSE))

where A1 would have the value ValidTAB.

Marc

Mr Nick · Mar 21st, 2002, 08:06 AM

I have tried this method and get a #REF! value returned to the cell.

I'm getting a bit fed up now and think I may resign!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 04:49 AM	#1
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	Is it possible to use a defined name to reference a tab on a worksheet like in the example below where I have used the name "ValidTAB" =IF(ISNA(VLOOKUP(T12,'[VD.xls]ValidTAB'!$A$1:$R$957,2,FALSE)) If it is indeed possible, how do I format the reference to this defined name? defined name

Mar 21st, 2002, 07:31 AM	#2
paelinck Board Regular Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	I think it should be possible using the Indirect function. Your function would look like this: =IF(ISNA(VLOOKUP(T12,INDIRECT("'[VD.xls]'"&A1&"'!$A$1:$R$957"),2,FALSE)) where A1 would have the value ValidTAB. Marc

Mar 21st, 2002, 08:06 AM	#3
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	I have tried this method and get a #REF! value returned to the cell. I'm getting a bit fed up now and think I may resign!!