Discuss "Dynamically Reference Worksheet Cell/Range" in the Microsoft Office forum.

Okay, I am not sure if this is already a built-in function in Excel, but I have not yet found it. What I need to do, is build a function, so one worksheet can reference another worksheets cell/range dynamically.

Basically, say I have a worksheet named Summary. In this worksheet, it summarizes a list of part numbers. So, in column A, it will list part numbers. These part numbers each have their own individual worksheet, which is named the same as the part number.

How do I create a function to reference the name of the other worksheet from the part number in the Summary tab.

For instance, if I want to reference the worksheet for part number 123456789, and cell B25, how would I build this function. The code in excel to reference such worksheet from another is:

You cannot create that dynamically, hence why I think I need a function. I would also like the ability to reference a range as well and not just a single cell.

__________________
"You'll never be as perfect as BLaaaaaaaaarche."

have a look at the INDIRECT function. something like this:
A1 being the field that contains the worksheet name
B3 being the cell that contains the value.

__________________
Quote of the Month:
Regret: It hurts to admit when you make mistakes - but when they're big enough, the pain only lasts a second.

Questions to Ponder:
Could it be that all those trick-or-treaters wearing sheets aren’t going as ghosts but as mattresses?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright © 2008 sbenj69

Yep, that with a combination of ADDRESS() worked perfectly.

Thanks, mehere.