I have a named cell in my "Menu" tab called "OrangeJuice.Cost" which references "Menu!$D$2". On a new tab I have the String "OrangeJuice" in cell A1. I want to use this cell to construct the named cell above. I have the formula in this tab as =A1&".Cost". This just writes the string "OranceJuice.Cost" in the cell, without actually putting the cell value "Menu!$D$2". I guess I need to wrap it up in an indirect or lookup but whatever I try fails with #Name? What am I missing?


Indirect is the way to go:

=INDIRECT(A1 & ".Cost")

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.