Thread: N/A Error on a dynamic named range

Hi, I am having the oddest problem with a named range. I have made a dynamic named range, and saved it globally. The "code" is as follows:

=OFFSET(INDIRECT(CELL("address";OFFSET(INDIRECT("B"&MATCH("Resultat ekskl. Værdireguleringer";$B:$B;0));4;0;;)));0;0;
MATCH("Resultat ekskl. Værdireguleringer";INDIRECT(CELL("address";OFFSET(INDIRECT("B"&MATCH("Resultat ekskl. Værdireguleringer";$B:$B;0));4;0;;))&":B1000");0);6)

This is probably not the best way to write this range, but in the end it was the way I could figure out how to bypass the trouble of finding the second occurence of a "Resultat ekskl. Værdireguleringer" in column, and making the named range based on this second occurence's cell address.

Anyhow, when I use this named range in the worksheet where I made it, it works perfectly. Absolutely no problems, I can use VLOOKUP, INDEX etc. and get the correct values. But then when I want to use the same range in another sheet (same woorkbook) i just get the N/A error.

Hopefully someone here can tell me what I'm doing wrong, because its quite infuriating :D

Thanks in advance,
Pelle

Maybe you should specifically identify the sheetname in the named range formula before all the references to column B.

Did you try it like this?

=OFFSET(INDIRECT(CELL("address";OFFSET(INDIRECT("'Sheet 2'!B"&MATCH("Resultat ekskl. Værdireguleringer";'Sheet 2'!$B:$B;0));4;0;;)));0;0;
MATCH("Resultat ekskl. Værdireguleringer";INDIRECT(CELL("address";OFFSET(INDIRECT("'Sheet 2'!B"&MATCH("Resultat ekskl. Værdireguleringer";'Sheet 2'!$B:$B;0));4;0;;))&":B1000");0);6)

Where 'Sheet 2' is the name of the sheet. Note that you need the apostrophes around the sheetname it it not all one word.