Advance Excel: Auto Populate Cells

[Sid59]

im working on this excel sheet and i need 2 fields to auto populate with data AFTER i've chosen an item in a drop down menu. (created via Validation)

example:
Item - Cost - Sale Price

Apple - $1.00 - $0.85
Orange - $0.88 - $0.45
etc

So i's have multiple items in a validated drop down menu. For every item in the menu, i need the 2 sequential cells to auto populate with values that correlate to that item.

I've tried going the path of a VSlookup but i couldnt grasp it enough to make this work with a drop down / validation list.

Any help would be great. Thanks.

PS. i know, i'd need a 2nd worksheet to keep track of all the items and values. So that's a a step in teh right direction.

 

[giantpinkbunnyhead]

Originally posted by: Sid59
im working on this excel sheet and i need 2 fields to auto populate with data AFTER i've chosen an item in a drop down menu. (created via Validation)

example:
Item - Cost - Sale Price

Apple - $1.00 - $0.85
Orange - $0.88 - $0.45
etc

So i's have multiple items in a validated drop down menu. For every item in the menu, i need the 2 sequential cells to auto populate with values that correlate to that item.

I've tried going the path of a VSlookup but i couldnt grasp it enough to make this work with a drop down / validation list.

Any help would be great. Thanks.

PS. i know, i'd need a 2nd worksheet to keep track of all the items and values. So that's a a step in teh right direction.

Hi,

If I understand you right, you want to select a food item from a drop-down list you've created, and then have the cells to the right of it automatically show two prices associated with that particular item?

That's pretty simple. Firstly, a vlookup formula will work great here (and I'll help you through it). Second, the vlookup formula doesn't care if the thing it has to look up are from a drop-down list. All it cares is, what's typed in the box. Be it apple or orange or whatever. You also don't need your price data on a separate sheet. It can all live together.

To start, let's say you are using column A, starting at A1, to pick items from your drop down menu. Apple, orange, whatever... all that is going in Column A. Then we'll have columns B and C populate with the prices you attach to the food items. Let's say column D has your validation list, and we'll use column E,F, and G for listing out your food items and their two prices.

First thing is to make sure that your full list of items and associated prices is in alphabetical order, else the vlookup won't work right. After that, put the following formulas into B1 and C1: (Or whichever cells mark the top of the rows you want to populate)

For cell B1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,2),"")
For cell C1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,3),"")

In these formulas:
A1 = whichever cell has the drop down list that you pick from;
E1:G8 = dimensions of your price table (note that you'll have to replace this with whatever the actual dimensions of your table are);
The number "2" after the G8 means, Excel is going to take whatever data is in the 2nd column of your table, that is next to whatever food item it's looking up. In the 2nd formula, the 2 is replaced with a 3, which will grab your second price.

These formulas are a bit more than vlookups; they will also cause the cell to remain blank if you haven't yet selected anything from the drop down menu. (Otherwise the cells will show #N/A). After that, Columns D through G don't really need to be visible so you can hide those columns. Everything will still work if they are hidden.

Once you have the formulas working, you can "drag", or copy, them as far down the columns as you need so that you can pick 50 or 500 items in column A and all their prices should populate.

If this isn't what you are looking for, then please provide more information so we can help if you still need it.

 

[Sid59]

Originally posted by: giantpinkbunnyhead

Originally posted by: Sid59
im working on this excel sheet and i need 2 fields to auto populate with data AFTER i've chosen an item in a drop down menu. (created via Validation)

example:
Item - Cost - Sale Price

Apple - $1.00 - $0.85
Orange - $0.88 - $0.45
etc

So i's have multiple items in a validated drop down menu. For every item in the menu, i need the 2 sequential cells to auto populate with values that correlate to that item.

I've tried going the path of a VSlookup but i couldnt grasp it enough to make this work with a drop down / validation list.

Any help would be great. Thanks.

PS. i know, i'd need a 2nd worksheet to keep track of all the items and values. So that's a a step in teh right direction.

Hi,

If I understand you right, you want to select a food item from a drop-down list you've created, and then have the cells to the right of it automatically show two prices associated with that particular item?

That's pretty simple. Firstly, a vlookup formula will work great here (and I'll help you through it). Second, the vlookup formula doesn't care if the thing it has to look up are from a drop-down list. All it cares is, what's typed in the box. Be it apple or orange or whatever. You also don't need your price data on a separate sheet. It can all live together.

To start, let's say you are using column A, starting at A1, to pick items from your drop down menu. Apple, orange, whatever... all that is going in Column A. Then we'll have columns B and C populate with the prices you attach to the food items. Let's say column D has your validation list, and we'll use column E,F, and G for listing out your food items and their two prices.

First thing is to make sure that your full list of items and associated prices is in alphabetical order, else the vlookup won't work right. After that, put the following formulas into B1 and C1: (Or whichever cells mark the top of the rows you want to populate)

For cell B1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,2),"")
For cell C1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,3),"")

In these formulas:
A1 = whichever cell has the drop down list that you pick from;
E1:G8 = dimensions of your price table (note that you'll have to replace this with whatever the actual dimensions of your table are);
The number "2" after the G8 means, Excel is going to take whatever data is in the 2nd column of your table, that is next to whatever food item it's looking up. In the 2nd formula, the 2 is replaced with a 3, which will grab your second price.

These formulas are a bit more than vlookups; they will also cause the cell to remain blank if you haven't yet selected anything from the drop down menu. (Otherwise the cells will show #N/A). After that, Columns D through G don't really need to be visible so you can hide those columns. Everything will still work if they are hidden.

Once you have the formulas working, you can "drag", or copy, them as far down the columns as you need so that you can pick 50 or 500 items in column A and all their prices should populate.

If this isn't what you are looking for, then please provide more information so we can help if you still need it.

Hi,

Thanks for the response. It works perfectly, sorta. I have this large list that needs to scale and i'm finding pricing errors when the numbers/price populate.

I'm not sure if my items list are so closely related. For instance, i have the same product but different variations. Lets say i have Apple - Basic, Apple - Pro, Apple - Premium with all the respective prices in place AND the list is sort alphabetically by list items. what i'm finding, the two prices will not populate correctly and is stuck on populating the prices for the first item in the list.

suggestions?

 

[giantpinkbunnyhead]

Hey there,

Yeah, a couple suggestions. First, are any entries in your list, listed twice? For instance, if you have Apple - Basic in there twice, the formula will only look for the FIRST occurrence of it. That's one thing to look for.

The other option, which you may want to do anyway, is to modify the formula as follows:

Using the example formulas above...

For cell B1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,2),"")
For cell C1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,3),"")

Change them to:

For cell B1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,2,FALSE),"")
For cell C1: =IF(A1>0,VLOOKUP(A1,$E$1:$G$8,3,FALSE),"")

Then re-copy the formula to all the cells in your table that need it. All I did was to add the word FALSE in the appropriate place (with the preceeding comma) What this does is, it tells Excel to ONLY look for exact matches. If one is not found, you'll get an N/A error instead of the value from something close.

If you get N/A errors, make sure that the listed items are spelled exactly as they are supposed to be. You said that your list was in the form of a drop-down so as long as your formulas refer back to your dropdown table, everything should work. Again, if anything is listed twice in your table, this will only return the value for the first time it is listed.


See if this works for ya... if not we'll try something else.

 

[Sid59]

Perfect thanks!

I also learned you can update two values to make it correct. There's a section of the formula where you adjust how long the LIST goes, my value was not long enough. I also just updated my Drop Down List to pull from the "products" column.

Works Perfect! Thanks.

This has to do less with populating values and more of a IF argument. Hopefully you can answer this one.

I have my items with pricing good to go. I also have a quantity cell and a total cell. The formula is easy where i multiple the cell quantity * pricing cell = TOTAL Cell. Easy enough.

Which steps can i create a formula, in which the formula looks for a value in 1 cell, returns a NULL value, and then looks in a secondary cell.

So i have A1=no value and A2=10 ... the formula would multiple PRICE * A1, if no value ..A2 = Total

thanks


PS. I'm also trying a secondary function not related to the above multiplication.

Lets say i'm using the Lookup you provided above and use a formula to calculate the PRICE*QTY + a dynamic discount.

How do i edit the "Totals" Formula to DISOBEY a discount if certain letters appear in the product list.

Example.

Discount 10%
QTY 1
Product - Apple
Cost - 100
Total = [Cost - 10% discount] * QTY

but if i have

Discount 10%
QTY 1
Product - Apple - bulk
Cost - 100
how would the "Total" formula look if it sees the "BULK" in the Product line and does NOT apply the discount to the total?

 

[giantpinkbunnyhead]

OK, we'll take this one step at a time.

Your first question about the null value and deciding which cell to multiply against the quantity... is easy. If I understand you right, you're saying you want the price to multiply by whatever quantity is in A1. But, if A1 is empty and A2 has the value, you then want the price to multiply by A2. Is that correct?

If so, Using your examples...
A1 = a quantity, or empty
A2 = another quantity, or empty
a3 = price
a4 = either A1 or A2, depending on which is populated, times price.

For cell a4:

=IF(ISNUMBER(A1)=TRUE,A1*A3,A2*A3)

This simply says that if A1 is a number, it will multiply it with the price in A3. if A1 is NOT a number (i.e. blank), it will multiply A2 with the price. If both A1 and A2 have a value, this formula will favor A1.

NEXT UP... your "bulk" issue.

I'll assign some cells to your examples to give my formula some relevance.

Discount 10% (Cell B1 = 10%)
QTY 1 (Cell B2 = 1)
Product - Apple (in cell B3)
Cost - 100 (Cell B4 = 100)
Total = [Cost - 10% discount] * QTY (in Cell B5)

What you're asking is for the total price in Cell B5 to NOT include the discount IF the word "bulk" appears in cell B3, correct? If so, then for cell B5:

=IF(ISNUMBER(SEARCH("bulk",B3))=TRUE,B2*B4,B2*B4*(1-B1))

THis way, you can have the total in B5 not apply the discount if the word "bulk" appears anywhere in the product name. Also, you can vary the percentage in cell B1 and the formula will pick the change up. If you change it to a 20% discount, no big deal. Plug this into your spreadsheet and get a feel for how it works; then you should be able to move things around to wherever you want them.

Note that I used the SEARCH function, rather than another function called FIND. The difference is simply that FIND is case-sensitive whereas SEARCH is not. So SEARCH will pick out "bulk", "BULK", "Bulk", etc.... whereas FIND would only pick out "bulk" as spelled out in the formula.

I hope this helps you out, but if I misunderstood what you were asking, give me some clarification and we'll have another go at it.

 

[Sid59]

thanks so much. Everything i learned here has helped immensely.

Again, thanks!

 

[Sid59]

ooh new question, i think i reach my limitations on how handle this portion. i tried to incorporate some your tips into a more advance formula to cut down on items in my list area.

Lets say i have product (column L), Price per dozen (Column M), Price per 1 item (Column N) - in the past, i just created extra products to compensate for price per 1 item but the list grows rather large with the more products i add.
Example: Apples - 1 - .10

In our examples above my lookup would be =IF(D15>0,VLOOKUP(D15,$L$1:$M$100,2),"") and it would return anything in my validation list and input the price in the corresponding cell.

I want to take this a step further and eliminate "price per 1" in the product list and instead incorporate a "search", Yes / No and instead of returning the "price per dozen", return the price per 1.

If the Cell with the True/False (corresponding to price perdozen or price per 1) is A13, how is this done?

I tried to use something like =IF(ISNUMBER(A13)=TRUE and =IF(D15>0,VLOOKUP(D15,$L$1:$M$100,2),"") --- but the formula i tried had an error and i couldn't figure it out. For fun sake .. this is how i tried to approach this

=IF(ISNUMBER(SEARCH("Yes",A13))=TRUE,(D15>0,VLOOKUP(D15,$L$1:$N$100,3),""),(D15>0,VLOOKUP(D15,$L$1:$M$100,2),"")

dont laugh too hard

 

[NeoV]

you can nestle if functions, so you can have =if(a=1,"X",if(b1=1,"Y",if(c=1,"Z","NA")))