Hi John,
Seems like a good place to use the INDIRECT function.
In cell A1, enter the numeric suffix (e.g. 004) of the worksheet you want to access.
Now enter the following formula wherever you want to display the results of the lookup:
If you change the value in cell A1 the lookup will be directed to the appropriate sheet.
As you know, cell A1 must be formatted to show TEXT values - otherwise when you enter 004, Excel will assume it's a number, just display 4, and be unable to find a worksheet labelled KPA4.
If you want the convenience of entering just 4 in cell A1, then use the following formula instead:
Finally, a point to note when using the INDIRECT function - the ranges "C13:C24" and "Q13:Q24" are enclosed in quotes, so if you copy the formula to another cell, these ranges will NOT be updated relative to the cell containing the formula, but will remain unchanged.
Hope this helps - please let me know how you get on.
Best regards,
Greg M
Bookmarks