Dynamic Named range formula not working on another sheet.

0
votes

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)
1
excelexcel-formulanamed-ranges
"...I am getting nothing." Do you mean the cell shows [empty] , or is there some error (#REF or such)? - BruceWayne
@BruceWayne thank you for the response. It just shows nothing. No error, the cell is blank. - djblois
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
@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
@rwilson, thank you that fixed. I did not realize I missed it on C - djblois

1 Answers

0
votes

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.