Thread: Dynamically Reference Worksheet Cell/Range

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:

Code:

='123456789'!B25

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.

have a look at the INDIRECT function. something like this:

Code:

=INDIRECT(A1& "!B3")

A1 being the field that contains the worksheet name
B3 being the cell that contains the value.

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

Thanks, mehere.