Forum Discussion
Populating cells with information based on if one of two cells have contents
Hey Lary,
Can you clarify for me? Will there either be a product code entered in B8 OR an option code entered in D8 but not both?
If that is the case, I would change your VLOOKUP function to check for a blank in the cell B8 first. I put this in E8:
=IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,2,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,2,FALSE)),"")
Hi Bryant,
Thanks for the response. Column 8 will not always have a model code. I need columns E, F, and G to know if I enter a value in column B there will be no option on that row, then if I enter a value in column D there will be no model.
Column E, F, and G just need to be populated based on which (B or D) cell has data.
Think of it as a car:
B column=car model
D column=options for that car (D9 wheels, D10 color, D11 trim...etc) Here's what it will look like.
Qty Model Board Options MSRP MAP Dealer Cost 1 ASATD MP $2,857.00 $1,999.99 $1,380.00 2TS $0.00 $0.00 $0.00 3PB $72.00 $50.00 $35.00 BIB $215.00 $150.00 $104.00 My trouble is that on the spreadsheet, I may have to enter multiple models with options, but if I enter a model number on B10, the vlookup won't recognize it since it is formatted for the option column. Sometimes there will be two models, sometimes 6 or 7 with different option quantities. (hope that makes sense).
Thanks again!!
Lary
Hey Lary,
Thanks for the clarification. Your example table makes it very clear.
I actually think the formula I wrote to you last time will do the trick! Here is the table that I have using that formula:
Qty Model Board Options MSRP MAP Dealer Cost 1 ASATD MP $2,857.00 $1,999.99 $1,380.00 2TS $0.00 $0.00 $0.00 3PB $72.00 $50.00 $35.00 BIB $215.00 $150.00 $104.00 In cell E8, the formula is
=IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,2,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,2,FALSE)),"")
In F8
=IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,3,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,3,FALSE)),"")
in G8
=IFERROR(IF($B$8="",VLOOKUP($D$8,Data!$E$2:$H$52,4,FALSE),VLOOKUP($B$8,Data!$A$2:$D$52,4,FALSE)),"")
And drag the three formulas down. They will do a VLOOKUP on the Model if there is one, or on the options if there is no model on that same row.
I hope I've understood you! Reach out if I'm still confused :)
Hey Bryant!!
Thanks so much. Works great on the models, not so much on the options. Here's what I mean:
Qty Model Board Options MSRP MAP Dealer Cost 1 ASATD MP 2857 $1,999.99 $1,380.00 2TS 2857 $1,999.99 $1,380.00 3PB 2857 $1,999.99 $1,380.00 BIB 2857 $1,999.99 $1,380.00 I'm not sure what values to change on E/F/G9 through 39 and so on. Sorry if this should be easy, but i'm not wrapping my head around it. Also, if there are no values entered (blank), nothing should show up.
Best,
Lary
