#1
|
|||
|
|||
Auto populate a cell with a price
Hi folks I’m guessing I use the lookup function for this but I want to do a drop down arrow for items and have it auto populate a cell with the price for that item. So for example hoodies cost $5.99 and hats cost $3.99. I want to have column a show that item based on a drop down menu that would display the item hoodie or hat, and I want the column b to display the price of the item, $5.99 or 3.99 in this example. Any suggestions please and thanks
|
#2
|
||||
|
||||
How about doing a validation list taken from a list of items sold? I have attached a sample. In sheet 1, you add your product/item and their corresponding prices.
|
#3
|
|||
|
|||
Hi Marcia. looking at your example spreadsheet, although it does pull in product hoodie from the first tab 1 as a product in cell B4,, it does not bring in the price value for hoodie beside that in cell C4. Am I doing something wrong? If I try and edit cell C4, I get an error saying "You can't change part of an array". Am I doing something wrong here? Many thanks
|
#4
|
||||
|
||||
I’m using Office 365. Try re entering the INDEX/MATCH formula. It should return the price of the item that you selected.
|
#5
|
||||
|
||||
Or, recreate a new table. Type the headings. Click mouse in the celle below product>>Open the Data tab>>Open Data Validation in the Data Tools group>>Open the dropdown of the Allow: box, select List>>in the Source: box type =Product>>OK
Under price, enter the code =IFERROR(Index(Price,Match(B3,Product,0)),””) |
#6
|
|||
|
|||
I am having no luck with this method is there a way to do this using vlookup or something?
|
#7
|
||||
|
||||
=VLOOKUP(C3,Table1,2,FALSE)
|
#8
|
||||
|
||||
Be careful with IFERROR, it hides ALL errors which might not be what you want
|
#9
|
|||
|
|||
Slightly edited version of workbook from Marcia.
Both tables are defined as Tables (this ensures, you don't have to edit formulas whenever you add new data to either of Tables); The products list referred in Data validation is read as column of product list Table and is now dynamic; To get price for data entry table, VLOOKUP is used instead of INDEX; All formulas are using Table syntax. |
#10
|
|||
|
|||
This all works but I don't understand why it's necessary to have the iferrror in there, what does this do? Can I get the reuslts i want just using vlookup? How would I do this? Thanks
|
#11
|
|||
|
|||
Quote:
You can of-course simply ignore this, but taking care of such cases, however small the probability of them will be, is simply a good programming style. |
#12
|
|||
|
|||
Ok, good to know, I have found all the answers to my questions so I will mark this as resolved. Thanks to everyone who helped me through this
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto populate cell with today's date | Phil H | Excel Programming | 7 | 10-18-2017 09:41 AM |
Auto populate cell based on certain situations | YW3 | Excel | 10 | 05-26-2017 02:02 AM |
auto cell populate 2 | Stevejd58 | Excel | 3 | 08-20-2013 04:23 PM |
auto cell populate | Stevejd58 | Excel | 14 | 05-13-2013 02:22 AM |
Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |