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 ...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 14, 2022
    Hi
    =INDIRECT(A2 & "!B2")
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 14, 2022
Hi
=INDIRECT(A2 & "!B2")
  • 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.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jun 14, 2022

      Durata 

      Perhaps

       

      =INDIRECT(INDIRECT(Main!C2)&"!B2")

      • Durata's avatar
        Durata
        Copper Contributor
        Jun 14, 2022
        Nah, that didn't work sadly.

        When I test out just "=Indirect(Main!C2)" I get a 0 even though the value of Main!C2 is "ID000123" because it's referencing "ID000123!F1".

        Am I misunderstanding how =Indirect() works?

Resources