0

This isn't a question about the scope of names. It's a question about specifying the cells in a name.

Imagine I've created a name =A1+B2.

That gets hard-coded by Excel into Sheet1!A1+Sheet1!B2. But I don't want those cells to be tied to Sheet1.

I want to use this name on multiple worksheets. If I use this name in Sheet1, I want the name to be equivalent to Sheet1!A1+Sheet1!B2. But if I use this name in Sheet2, I want it to be equivalent to Sheet2!A1+Sheet2!B2.

Is that possible? If so, how?

  • Mark, are you talking about Multi Sheet reference. Like if I use data range across the sheet with function as named range? – Rajesh S Mar 9 '18 at 12:29
0

edit: this probably won't work with Named Ranges.

You can use this formula (found on extendoffice.com) to get the current sheet name (note that the file must be saved first, this will not work on a new Blank workbook):

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Combine that with an INDIRECT to get the value of A1:

=INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"!A1")

And then the final formula to combine your A1 and B2:

=INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"!A1")+INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"!B2")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.