Variable Range (1 Viewer)

[spalmateer]

Hi,
I am using VLOOKUP functions throughout my workbook that looks at the last worksheet which contains equipment costs. An example is:

=IF(B30<>"",IF(ISERROR(VLOOKUP(B30,'Equipment Costs'!$A$2:$D$895,4,FALSE)),"No Match",VLOOKUP(B30,'Equipment Costs'!$A$2:$D$895,4,FALSE)),0)

The problem is that I often update my equipment costs worksheet, which then could grow or decrease in size. Then the range for the hundreds of VLOOKUP functions are incorrect. Is there a way instead of using the range 'Equipment Costs'!$A$2:$D$895, I could change the formula to one cell that will define the range? When I type a cell reference in the VLOOKUP function, it won't use that cells value for the range. Excel is looking at it as the range. Any ideas? Thanks in advance!
Scott

 

[reclusivemonkey]

Use a range name, rather than a specific reference to cells.

 

[shades]

Specifically you want a dynamic named range so that it can change as your data changes. (Also consider making all worksheet names without spaces (i.e., EquipmentCosts)

In the Insert > Name > Define box put your named ranged, i.e. ECcosts
and in the reference put this:

=OFFSET(EquipmentCosts!$A$2,0,0,COUNTA(EquipmentCosts!$A:A$),4)
________
Bmw K1

 

[spalmateer]

Dynamic Range

Hi - Thanks for the reply. Apparently I was overthinking the problem. I had another reply on another forum suggest that I define the range high enough to where I wouldn't have to worry about not having enough rows. I've set my range at 8000, and its working like a charm.

 

[shades]

Good that you found a solution. Keep in mind that the more intensive the formulas/data, the more important it is to limit the range to the actual used range.
________
Weed Vaporizers