Deleting rows causes formula to change

**jgupte** · 08-15-2012, 12:44 AM

In my spreadsheet I have the following formula:

=IF(ISBLANK(Z4),"",IFERROR((VLOOKUP(Z4,'New Assessments'!$B$2:$U$227,10,FALSE)),X4))

I keep adding and deleting rows to the New Assessments sheet.
When I delete lines, the part of the formula $U$227 decreases, but when I paste new lines, this number does not increase.
Therefore my vlookup is only checking part of the range I need to check.

How can I fix the formula so that the $U$999 stays the same no matter what I do to the New Assessments sheet?

Regards,

Julio

**FDibbins** · 08-15-2012, 01:05 AM

have you considered using a named range? that should take care of the bottom row stays "fixed"

alternatively, play around wit the indirect() function to make sure the cell reference doesnt change

**jgupte** · 08-19-2012, 07:29 PM

I am not very familiar with named ranges, and when I tried in the past could not get them to work.

How do I use the indirect () function?

**FDibbins** · 08-19-2012, 08:23 PM

for a named range, highlight the range you are dealing with, right click and select "name a range"

the indirect() function is hard to explain without seeing your data, but it basically uses a "text" reference, which it then converts to a cell reference.

if you can upload a sample of ypur file, i can help you get started