New Contributor

# formula to auto entry an amount based on the kind

I am not sure the best way to describe what I am trying to do. I have different tables and I want to be able to enter a name then have it get a price from a different table and automatically an amount for that particular product. I am trying to enter an if formula but I can not figure out how to do the last part.

I need to have the price per ounce updated when the chemical name is inputted in the blue table it will auto populate from the table to the right for the correct chemical. Any help would be greatly appreciated. Thanks

8 Replies

# Re: formula to auto entry an amount based on the kind

You wrote:  I am trying to enter an if formula but I can not figure out how to do the last part.

I'd recommend using the VLOOKUP function instead of IF. Here's a reference on how VLOOKUP works that should help. If it doesn't get you through to a solution, come back and post again.

https://exceljet.net/excel-functions/excel-vlookup-function

# Re: formula to auto entry an amount based on the kind

Thank you, I will try that.

# Re: formula to auto entry an amount based on the kind

I am still struggling. I have one table that has a drop down list, when someone clicks on a name from the drop down list, I need the price per ounce to auto populate in that same table. I tried to use the following and get an error.

=VLOOKUP([@[Chemical Name]],Table1[Chemical],Table1[per Ounce])

If there is a better way to show this to you please let me know.

Thanks for any help.

# Re: formula to auto entry an amount based on the kind

That VLOOKUP is incomplete.

I think would be the correct formulation. Here's a screen grab on the correct syntax for VLOOKUP from one of my favorite on=line references ( https://exceljet.net/excel-functions/excel-vlookup-function )

You have the formula written as =VLOOKUP([@[Chemical Name]],Table1[Chemical],Table1[per Ounce])

It should be more along these lines

=VLOOKUP([@[Chemical Name]],Table1,2,0)

• In short, I think you're specifying the lookup_value correctly, but not the rest.
• table_array would be simply "Table1"
• column_index_num would be 2 for the second column of Table 1
• and 0 would be requesting an exact match

If making that change doesn't work, refer to the website I link to above. If you still can't resolve it, please post a copy of the actual spreadsheet either here or in OneDrive or Google Drive or the equivalent, and give us a link

# Re: formula to auto entry an amount based on the kind

@mathetes I also am having this issue. I have a spread sheet with names, elevations and columns for each material with the quantity being used. I need to get the material quantities to auto populate when the elevation is selected from a drop down menu and can’t figure out how to get that to work.

# Re: formula to auto entry an amount based on the kind

You'd need to provide more information, an image of the sheet at the very least, but ideally a copy of the actual spreadsheet posted to a shareable website like OneDrive or Google.

# Re: formula to auto entry an amount based on the kind

@mathetes I have a link below. What I’m trying to do is get a drop down menu in columns next to each other and then the final one to populate the counts in the correct columns.
I want to have any builder we use names, when one is selected it brings up the plans for that specific builder in a drop down menu, when a plan is then selected in the drop down I’m trying to get the amount of material needed to populate in the corresponding columns next to it. Currently these all need to manually be entered and it’s time consuming.

for instance the first column would select any one of our builders In the tabs below, lets say we choose MI tab, in the next column I want it generate only the the MI plan names, buckingham, flintwick etc on MI tab, once a plan is selected then next column would be elevation, using MI tab as example and say buckingham was chosen, It would  then generate the next column options of elevations A,B,C. One an elevation is selected I need it to then auto populate the quantity of material needed in the columns next to it, example 30 yr bundles, 25 yr bundles, starter bundles etc…