Forum Discussion

Durata's avatar
Durata
Copper Contributor
Jun 14, 2022
Solved

Create Reference from Existing Text and Exact Cell Location

Hi,

 

I'm trying to create a cell reference using the sheet name and the exact cell I'm trying to reference from that sheet, but when I use Indirect I get a "#REF!".

 

For example:

A2 is Sheet1

A3 is Sheet2

I'm trying to reference Sheet1!B2

 

I have tried:

=""&A2&"!B2" Which just gives me "Sheet1!B2"

=INDIRECT(""&A2&"!B2") Which gives me the "#REF"

 

Any advice would be greatly appreciated!

excel
Formulas and Functions
    • Durata's avatar
      Durata
      Copper Contributor
      Jun 14, 2022
      Okay,

      So first, thanks. That worked!

      I tried to make it more complicated after, and again I'm getting #REF.

      For example:
      I've named sheets after an identifier already in said sheet
      - ID000123!F1 = ID000123
      I use IF(AND()) to see if a specific item and date from the main sheet both show up in ID000123, and if it does, I make Main!C2 = ID000123!F1.
      From there I try =INDIRECT(C2 & "!B2") but I get #REF!
      - If I manually type ID000123 in cell Main!C2, I get the value of cell ID000123!B2 instead of #REF!, but this doesn't help me when I need cells to self populate when filling in Main!A2:A10.

Resources