0

I am using this formula in a named range that will dynamically get the row that matches the text in the first column and what the LastColumn is as indicated on another sheet. When I am in the edit named range box as you can see in the pic, it is referencing the correct range.

enter image description here

However, when I reference this range on another sheet, I am getting nothing. Here is the Formula:

=INDIRECT("C" &MATCH("Frozen: ",Input!$A:$A, 0)&":"&Ass_LastColumn&MATCH("Frozen: ",Input!$A:$A, 0))

and here is the formula I am using on the next sheet:

=IF(In_Item_Date="","",In_Item_Date)
djblois
  • 963
  • 1
  • 17
  • 52
  • "...I am getting nothing." Do you mean the cell shows `[empty]` , or is there some error (`#REF` or such)? – BruceWayne Aug 19 '15 at 18:24
  • @BruceWayne thank you for the response. It just shows nothing. No error, the cell is blank. – djblois Aug 19 '15 at 18:30
  • You are going to need to put the sheet name in front of the address before referencing it. Such as "Sheet1!C" or whatever the sheet name is. – rwilson Aug 19 '15 at 18:30
  • @rwilson - I thought if it's in the same workbook, you shouldn't have to do that. I just checked and I am able to refer to Named Ranges in various worksheets (in the same workbook) without using the sheet name. ...does it have to do with using `Indirect()` at all perhaps? djblois - can you use your formula *without* indirect? Try re-writing without Indirect, just to make sure you're able to reference Named Ranges correctly. – BruceWayne Aug 19 '15 at 18:33
  • @rwilson, thank you that fixed. I did not realize I missed it on C – djblois Aug 19 '15 at 18:38
  • Named ranges have specific scopes. This can be either for the Workbook or individually on each sheet. Check on the Name Manager what the scope is on your named range. Though seems like not the issue thought it was pertinent to the discussion. Regards, – nbayly Aug 19 '15 at 18:40
  • @BruceWayne You need to add an explicit reference because if you are on sheet1 and you reference column C, you reference column C of sheet1. If you are on sheet2 and you reference column C, you reference column C of sheet2. – rwilson Aug 19 '15 at 18:44
  • @rwilson - D'oh, yep you're right! I was just thinking of the named ranges. – BruceWayne Aug 19 '15 at 18:46

1 Answers1

0

When you use INDIRECT and you want to reference a specific address on a specific sheet it is important to put "sheet1!" (or whatever your sheet name is) in front of the address. In this case, 

=INDIRECT("C" &MATCH("Frozen: ",Input!$A:$A, 0)

would need to be changed to

=INDIRECT("sheet1!C" &MATCH("Frozen: ",Input!$A:$A, 0)

Without the sheet name, Column C is relative depending on what sheet you write the formula on.

rwilson
  • 2,105
  • 11
  • 18