VLOOKUP referencing subsequent cells in table / Data Validation Error

plantperson

New Member
Joined
Feb 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.

1613154698572.png


Pesticide Reporting.xlsx
JKLMN
2PARK NAME (SELECT FROM DROPDOWN)PRIOR NOTICE (SELECT FROM DROPDOWN)DATE OF APPL. (mm/dd/yyyy)COUNTY CODEPARK PROPERTY # (SELECT FROM DROPDOWN IF INFO DO NOT AUTO-FILLED)
3Yellowstone Park61#N/A
4Yellowstone Park61Q425
561#N/A
661#N/A
761#N/A
861#N/A
961#N/A
1061#N/A
1161#N/A
1261#N/A
1361#N/A
1461#N/A
1561#N/A
1661#N/A
1761#N/A
1861#N/A
1961#N/A
2061#N/A
2161#N/A
Technican
Cell Formulas
RangeFormula
N3:N21N3=IF(J3="Greenstreet","Select Property ID from Drop Down Menu",VLOOKUP(J3,'Property Info'!A2:C949,2,FALSE))
Named Ranges
NameRefers ToCells
'Property Info'!_FilterDatabase='Property Info'!$A$2:$C$2N3
Cells with Data Validation
CellAllowCriteria
N3:N21List='Property Info'!$B$2:$B$950
J3:J21List='Property Info'!$A:$A
K3List='Prior Notice'!$A$2:$A$3
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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.
Rich (BB code):
=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
 
Solution

plantperson

New Member
Joined
Feb 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
Rich (BB code):
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I could help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top