Using a Cell's Contents to Find the Name of Another Sheet Within The Same Workbook

I have worksheets called KPA001, KPA002, KPA003 etc. and another called Wages.

When I enter a number (e.g. 004) in Cell N12 of the wages worksheet, I need to perform a Lookup function in Worksheet KPA004.

If I enter 006 in Cell N12 of the Wages Worksheet, I need to perform a Lookup function in Worksheet KPA006 etc etc.

I don't mind losing the 'KPA' part if that makes it any easier.

The code below works, but will obviously only look at KPA004
(Replacing this with KPA006 or KPA009 etc also works):

=IF(OR(G12="",N12=""),"",LOOKUP(N12,KPA004!C13:C24,KPA004!Q13:Q24))

*******

But I cannot find any way to 'CALCULATE' the reference KPA004 or KPA006 etc.

n.b. These KPA numbers represent employee payroll numbers and the KPAXXX sheets are where their salary details are.

But for this setback I've got a 99% finished, working programme.... It's such a drag!!!!

See the example in attached file... it works with macro.. I hope this solution is good for you.

Regards,
Antonio

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Hi Antoka05,


Thanks for getting back so soon.

I can see from using your excellent example, that you are indeed accessing different worksheets based on your input.

What I can't see however is HOW you're doing it! As I can't find the macro which is making the calculations.

(I am using Excel 2007): I've used the 'View Macros' buton; but it doesn't show any there. There is also no sign of any formulas on the worksheet.

I need the contents of the macro to be able to adapt it and build it into my spreadsheet.


Thanks in anticipation,

John

I don't have excel 2007 (here I am at the work) but try to right click on 'Wages' sheet label and choose 'view code'.

This is the code (I used the Worksheet change event that starts when you change the value in a cell):

Please Login or Register  to view this content.

Regards,
Antonio

To: Antoka05 & Greg M

You two are obviously quite a way 'above' me in terms of Excel knowledge!!!

I've tried out both of these methods and they work just fine.

I've wasted hours and hours trying to sort out this problem - - - and then in a couple of messages the solution is right there.


Thanks loads you guys

Hi again John,

Many thanks for the feedback. You're welcome - delighted to have helped.

All the best,

Greg M