Error check formula for Tab name in cell reference

[Guz99]

Hi,
I am using a heap of INDIRECT statements on a Reconciliation tab of my workbook, this makes the updating of these formula very easy by just changing the cell data to a different tab name to alter my lookups. The problem I am trying to adress is ...... is there a way to check that the data entered into the indirect cell is an actual tab name?

Example.
Tab names
Reconciliation, Apr10, Mar10, Feb10, Jan10, Dec09, etc, etc
In the Reconciliation tab in cell A1 I have "Apr10", and am using the following formula for my lookups =IFERROR(IFERROR(VLOOKUP(A9,INDIRECT("'" & $A$1 & "'!$A$2:$G$37"),4,FALSE),VLOOKUP(A9,'NRV 2YR'!$A$1:$G$102,4,FALSE)),0)
The idea of this is that if the lookup doesn't exist in the first lookup then it reverts to older costing data, but if the user inputs the data in cell A1 as "APR 10" instead of "APR10" then it will revert to the older data just because the sheet name does not exist.

How do I verify that the data entered into cell A1 is an actual tab in my work book.

 

[taigovinda]

How about wrapping your whole formula in an IFERROR() statement and have that statement make sure that the bottom left cell on the sheet in question ("APR 10" or "APR10") is not an error. You would be presuming that the very last cell on the sheet is not used and therefore not an error (IV65536 in Excel 2003, dunno what it is in 2007...). Hope that helps.

 

[Sandeep Warrier]

You could create a validation list in A1 that contains all the proper sheet names.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Guz99]

You could create a validation list in A1 that contains all the proper sheet names.

If the all the sheets already existed this would work fine but the users create new sheets each month as required and so I can't create a validation list

 

[Guz99]

How about wrapping your whole formula in an IFERROR() statement and have that statement make sure that the bottom left cell on the sheet in question ("APR 10" or "APR10") is not an error. You would be presuming that the very last cell on the sheet is not used and therefore not an error (IV65536 in Excel 2003, dunno what it is in 2007...). Hope that helps.

Excellent that worked. Thanks for your time.

Guz