VLOOKUP referencing subsequent cells in table / Data Validation Error

[plantperson]

Hi all,
After copying my formula into the cells in the column it seems to change the referenced table range. See in bold below.
This spreadsheet is based on looking up a property/property number in a different tab.

=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A2:C949,2,FALSE))
=IF(J4="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J4,'Property Info'!A3:C950,2,FALSE))
=IF(J5="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J5,'Property Info'!A4:C951,2,FALSE))

my Property Info table is A2:C950, why is it being changed in same column? I only want the J column reference to change.

If I manually change it to A2:C950 I get an error "This value doesn't match the data validation restrictions defined for this cell"
I checked my data validation and it seems correct to me because the Park Property number the Vlookup is using to return is in the B column as referenced in the data validation.

Pesticide Reporting.xlsx
	J	K	L	M	N
2	PARK NAME (SELECT FROM DROPDOWN)	PRIOR NOTICE (SELECT FROM DROPDOWN)	DATE OF APPL. (mm/dd/yyyy)	COUNTY CODE	PARK PROPERTY # (SELECT FROM DROPDOWN IF INFO DO NOT AUTO-FILLED)
3	Yellowstone Park			61	#N/A
4	Yellowstone Park			61	Q425
5				61	#N/A
6				61	#N/A
7				61	#N/A
8				61	#N/A
9				61	#N/A
10				61	#N/A
11				61	#N/A
12				61	#N/A
13				61	#N/A
14				61	#N/A
15				61	#N/A
16				61	#N/A
17				61	#N/A
18				61	#N/A
19				61	#N/A
20				61	#N/A
21				61	#N/A
Technican

Cell Formulas
Range	Formula
N3:N21	N3	=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A2:C949,2,FALSE))

Named Ranges
Name	Refers To	Cells
'Property Info'!_FilterDatabase	='Property Info'!$A$2:$C$2	N3

Cells with Data Validation
Cell	Allow	Criteria
N3:N21	List	='Property Info'!$B$2:$B$950
J3:J21	List	='Property Info'!$A:$A
K3	List	='Prior Notice'!$A$2:$A$3

 

[Joe4]

Welcome to the Board!

=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A2:C949,2,FALSE))
=IF(J4="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J4,'Property Info'!A3:C950,2,FALSE))
=IF(J5="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J5,'Property Info'!A4:C951,2,FALSE))

my Property Info table is A2:C950, why is it being changed in same column? I only want the J column reference to change.

You need to understand how to use Absolute/Variable/Mixed cell references. Just place a $ in front of the column or row reference you wish to lock, i.e.

=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A$2:C$949,2,FALSE))

Then those numbers won't change as you copy the formula down.

See here for further explanation: Absolute, Relative, and Mixed Cell References in Excel

 

[plantperson]

Welcome to the Board!


You need to understand how to use Absolute/Variable/Mixed cell references. Just place a $ in front of the column or row reference you wish to lock, i.e.

=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A$2:C$949,2,FALSE))

Then those numbers won't change as you copy the formula down.

See here for further explanation: Absolute, Relative, and Mixed Cell References in Excel

Thank you! That did the job.

 

[Joe4]

You are welcome.
Glad I could help!