CODEWITHSUNDEEP
excelexcel-formulanamed-ranges
djblois
djblois

Reputation: 805

Dynamic Named range formula not working on another sheet.

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)

Upvotes: 0

Views: 924

Answers (1)

rwilson
rwilson

Reputation: 2135

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.

Upvotes: 0

Related Questions