Excel Magic Trick 675: Double VLOOKUP Custom Formula Dynamic Data Validation

See how create a data validation using Custom Logical Formula with 2 VLOOKUP functions that will change when different products are entered. For example, if shirt product is entered, the data validation will let only values between $50 and $100 into the cell, but if a different product is selected, then a different range of dollar values will be used.

Download file: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Double
• VLOOKUP
• Custom
• Formula
• Dynamic
• Data
• Validation
• Logical
• Mike
• Gel
• excelisfun
• Girvin
• Highline
• Community
• College
• Validations
• change
• when
• different
• products
• are
• entered
• into
• cell
• next
• to
• DV

[aprima]

If there is a CHANGE to the values in the UPPER or LOWER reference table, is there a way to validate the already-populated PRICE values using conditional formatting (say highlight the cells in red) so that we can identify the PRICE is no longer valid?

[ExcelIsFun]

Try this video:

Excel Magic Trick 683: 3 AND criteria & 3rd AND has OR criteria -- Add, Count & Conditional Format

[ExcelIsFun]

Mr Excel and I will make a duel about your question soon. Watch for it.

[marcoantonio4311]

Thanks, one other question. The sheet has formulas to compare price per once. It is the price per once boxes I need to work with. They are in the SAME row but are seperated by 4 cells between each value. and the cells already contain a formula to find price per once. It is those price per once variances that I need it to highlight the lowest price in each row, for each product. I have about 150 products I purchase weekly. hope you can steer me in the right direction...... Thanks

[ExcelIsFun]

working 80+ hours a week. no time... try posting to THE best Excel question site:

mrexcel[dot]com/forum

[marcoantonio4311]

I need help, I am comparing a price of one product from 4 dif companies. I want to know how I can highlight the lowest price in each row, in yellow so that I can see what is the best price for the same product. I do it for multiple products from the same 4 purveyors. what video should I watch. Thanks

[ExcelIsFun]

Try:

Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)

That video does not have the exact trick your are looking for, but if you highlight table in the range A2:D12 and place formula in textbok, it should look like this:

=A2=MIN($A2:$D2)

[ExcelIsFun]

If the videos help people, then it is a success!

[totalfreeloader]

Hey, i'm incredibly impressed with your videos. Keep up the good work and I hope it's worth it the effort!

[ExcelIsFun]

I am glad that you like them!