Forum Discussion
Populating cells with information based on if one of two cells have contents
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
Lary,
My apologies! I tried what I told you to do and it didn't work! I had a few absolute references misplaced. If Sergei's response doesn't work for you, I hope this will.
Here's what you should be putting in the cells:
In cell E8, the formula is
=IFERROR(IF($B8="",VLOOKUP($D8,Data!$E$2:$H$52,2,FALSE),VLOOKUP($B8,Data!$A$2:$D$52,2,FALSE)),"")
In F8
=IFERROR(IF($B8="",VLOOKUP($D8,Data!$E$2:$H$52,3,FALSE),VLOOKUP($B8,Data!$A$2:$D$52,3,FALSE)),"")
in G8
=IFERROR(IF($B8="",VLOOKUP($D8,Data!$E$2:$H$52,4,FALSE),VLOOKUP($B8,Data!$A$2:$D$52,4,FALSE)),"")
Then, drag the formulas down. I have attached the spreadsheet with the changes.
Hi Lary,
I didn't catch you figures. In your latest table you have retail, reseller and dialer price the same for the model without any options and for the model with set of options.
If you'd like to have the price for the model without options and each next option shows total price of the model with this and all previous options when in E8 it could be like
=IFERROR( IF(ISBLANK($D8), INDEX(Data!$B$1:$D$500,MATCH($B8,Data!$A$1:$A$500,0),COLUMN()-COLUMN($D$7)), INDEX(Data!$F$1:$H$500,MATCH($D8,Data!$E$1:$E$500,0),COLUMN()-COLUMN($D$7)) + INDIRECT("R[-1]C",FALSE) ), "")copy this to F8 and G8 and all 3 cells till end of the list.
If to show the price with each option separately (i.e. "clean" model price plus this concrete option price) when that could be
=IFERROR( IF(ISBLANK($D8), INDEX(Data!$B$1:$D$500,MATCH($B8,Data!$A$1:$A$500,0),COLUMN()-COLUMN($D$7)), INDEX(Data!$F$1:$H$500,MATCH($D8,Data!$E$1:$E$500,0),COLUMN()-COLUMN($D$7)) + OFFSET(E$1,LOOKUP(2,1/($B$8:$B8<>""),ROW($B$8:$B8))-1,0) ), "")Figures are in attached files with indexes (1) and (2) accordingly.
For the first case (if that's what you need) that shall be an improvement for the case if you remove one of the options in the middle of their list - now it shall be no blank options in the middle. Could be done, but entire logic is not clear for me.
