Calc Defined Range needs to be static

[OkiePC]

I am using a workbook as a master price list and in other workbooks I am attempting to reference a specific range in multiple different cells using the VLOOKUP function. It seems that each cell that references the named range offsets the cell reference within that definition and I always want it to be cells A2:E500.

My goal is to be able to keep that master price sheet updated with 5 columns of info and on the other sheets to be able to enter a part number and have it find the manufacturer name, supplier, description and price from my master list. I first made a direct reference to the range I wanted and then copied the text from that cell into a named range, then went in and put $ in the cell coordinates to get

'file:///MASTER-PRICE-LIST.ods'#$price_list.$A2:$E500

But when I look at about the 16 row on the spreadsheet where I auto-filled and there is a “#N/A” result for a part number known to be valid, I can click on the named range and it shows up as:

 'file:///MASTER-PRICE-LIST.ods'#$price_list.$A19:$E517 

Why are the cell numbers changing and how do I lock them down?

[OkiePC]

I came up with a dumb workaround. I inserted about 60 blank rows at the top of my price list. Since I only have about 300 parts in my list and I padded the reference out to 500 rows, this ugly bandage will get me by for now since all of the sheets I build that reference that master list should not go past 60 parts each. So on my build lists by the time I get to the end of a 50 item list and my Defined Range is looking at $A52:$E502 instead of the desired $A2:$E500 it will still cover the valid parts I need to search through. I don’t like it, but after two hours jacking around with what is probably something simple due to my own ignorance, I can live with it. I guess for now I will write out my frustrations in those empty cells to remind me and others not to delete them!

[karolus]

as @dscheikey already said, you need to fix the row-numbers with a $prefix:

………$A$2:$E$500

[dscheikey]

Hi, you just need to put the dollar sign in front of the line numbers in your reference as well. So like this:

$price_list.$A$2:$E$500

[OkiePC]

There is a dollar sign there. There is also a pound symbol (or hashtag for younger folks)

I truncated the file name/path for publicly posting this, but otherwise the text in my named reference is copied and pasted from my workbook.

[dscheikey]

I saw a $ sign before the column “A” but not before the row “2”! Excuse me, please.

[OkiePC]

Thank you! I misunderstood line number as file name somehow. Your solution does seem to have it fixed for me.

I sincerely apologize for my density and for not reading carefully your answer the first time.

[robleyd]

You might want to read about Absolute, relative and mixed references