Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-24-2021, 10:28 AM
oscarlimerick oscarlimerick is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2013
Advanced Beginner
Auto populate a cell with a price
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default Auto populate a cell with a price

Hi folks Im 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
Reply With Quote
  #2  
Old 10-24-2021, 02:44 PM
Marcia's Avatar
Marcia Marcia is offline Auto populate a cell with a price Windows 10 Auto populate a cell with a price Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 450
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Attached Files
File Type: xlsx ProductPrice.xlsx (11.9 KB, 2 views)
Reply With Quote
  #3  
Old 10-24-2021, 03:07 PM
oscarlimerick oscarlimerick is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2013
Advanced Beginner
Auto populate a cell with a price
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 10-24-2021, 03:12 PM
Marcia's Avatar
Marcia Marcia is offline Auto populate a cell with a price Windows 10 Auto populate a cell with a price Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 450
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Im using Office 365. Try re entering the INDEX/MATCH formula. It should return the price of the item that you selected.
Reply With Quote
  #5  
Old 10-24-2021, 03:36 PM
Marcia's Avatar
Marcia Marcia is offline Auto populate a cell with a price Windows 10 Auto populate a cell with a price Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 450
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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)),)
Reply With Quote
  #6  
Old 10-24-2021, 05:24 PM
oscarlimerick oscarlimerick is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2013
Advanced Beginner
Auto populate a cell with a price
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

I am having no luck with this method is there a way to do this using vlookup or something?
Reply With Quote
  #7  
Old 10-24-2021, 05:47 PM
Marcia's Avatar
Marcia Marcia is offline Auto populate a cell with a price Windows 10 Auto populate a cell with a price Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 450
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

=VLOOKUP(C3,Table1,2,FALSE)
Attached Images
File Type: png ProductPrice1.png (24.2 KB, 16 views)
File Type: png ProrductPrice2.png (20.9 KB, 16 views)
Reply With Quote
  #8  
Old 10-24-2021, 11:44 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Auto populate a cell with a price Windows 7 64bit Auto populate a cell with a price Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,524
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Be careful with IFERROR, it hides ALL errors which might not be what you want
Reply With Quote
  #9  
Old 10-25-2021, 01:21 AM
ArviLaanemets ArviLaanemets is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

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.
Attached Files
File Type: xlsx ProductPrice.xlsx (12.3 KB, 1 views)
Reply With Quote
  #10  
Old 10-27-2021, 04:28 PM
oscarlimerick oscarlimerick is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2013
Advanced Beginner
Auto populate a cell with a price
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 10-27-2021, 11:01 PM
ArviLaanemets ArviLaanemets is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2016
Expert
 
Join Date: May 2017
Posts: 656
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

Quote:
Originally Posted by oscarlimerick View Post
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
In case VLOOKUP for some reason don't find the searched record, it returns an error, and IFERROR handles such cases. It will be especially useful when you have calculated controls on your form - in case a source for such calculations returns an error, the calculation itself returns an error too.

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.
Reply With Quote
  #12  
Old 10-28-2021, 08:06 AM
oscarlimerick oscarlimerick is offline Auto populate a cell with a price Windows 8 Auto populate a cell with a price Office 2013
Advanced Beginner
Auto populate a cell with a price
 
Join Date: Jul 2020
Posts: 33
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto populate a cell with a price 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 populate a cell with a price auto cell populate 2 Stevejd58 Excel 3 08-20-2013 04:23 PM
Auto populate a cell with a price 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft