Hello all
I’m a bit of a novice with excel but I’m trying to teach myself to make a simple calculator for carbohydrates in different food ingredients. For this I have made a formula to take the ingredient type and the unit of measurement and use VLOOKUP to take the relevant carb count from the table/cell range.
The end result is to be able to list all the ingredients in a recipe to give me a total carb count (after I research and input all the ingredients/counts etc)
On the Testing sheet I have a row of data which includes:
The Units sheet simply has the list of measurements, for the data validation lists.
As you will see I have it working well, however the problem I have is in the dropdown list on column C it has each onion type listed 5 times, as per my data on the vegetables sheet. I understand it is doing this because of my data validation is taking all the cells in the column A on the vegetables sheet.
Is there a way to use a formula or something within the “source” box in data validation dialog window to ignore duplicates? All googling I have done simply shows you how to remove the duplicates within the cell range, however if I do this it will destroy my other formulas.
Is there a better/easier method for what I’m trying to achieve?
Any tips on other features/formulas/comments I could study to make my “calculator”?
I hope this all makes sense,
Thank you
P.S Ignore all the values etc its just in testing stage ATM.
P.S.S I hope the "minisheets" work, my first time doing so.
I’m a bit of a novice with excel but I’m trying to teach myself to make a simple calculator for carbohydrates in different food ingredients. For this I have made a formula to take the ingredient type and the unit of measurement and use VLOOKUP to take the relevant carb count from the table/cell range.
The end result is to be able to list all the ingredients in a recipe to give me a total carb count (after I research and input all the ingredients/counts etc)
On the Testing sheet I have a row of data which includes:
- An Amount cell for the user to enter the value/amount of the ingredient.
- A Data Validation dropdown list for an ingredient (column C)
- A Data Validation dropdown list for unit measurement (column D)
- Column E combines the ingredient/measurement (this is the only way I could work out how to make this work, I plan to hide this column)
- Column F just displays the carbs per ingredient/measurement (I plan to hide this too)
- Column G uses a simple =Sum to calculate the data from column B and F to show the total carbs.
The Units sheet simply has the list of measurements, for the data validation lists.
As you will see I have it working well, however the problem I have is in the dropdown list on column C it has each onion type listed 5 times, as per my data on the vegetables sheet. I understand it is doing this because of my data validation is taking all the cells in the column A on the vegetables sheet.
Is there a way to use a formula or something within the “source” box in data validation dialog window to ignore duplicates? All googling I have done simply shows you how to remove the duplicates within the cell range, however if I do this it will destroy my other formulas.
Is there a better/easier method for what I’m trying to achieve?
Any tips on other features/formulas/comments I could study to make my “calculator”?
I hope this all makes sense,
Thank you
P.S Ignore all the values etc its just in testing stage ATM.
P.S.S I hope the "minisheets" work, my first time doing so.
| My Carb Calculator V1.1.1freshtest.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Amount | Type | Unit | Hide This | Carbs per/Hide | How many carbs | |||
| 2 | Test01 | 3 | Onion, Red | tbsp | Onion, Red, tbsp | 2 | 6 | ||
| 3 | Test02 | 1 | Onion, Red | tbsp | Onion, Red, tbsp | 2 | 2 | ||
| 4 | Test03 | 2 | Onion, Brown | cup | Onion, Brown, cup | 20 | 40 | ||
| 5 | Test05 | 1 | Onion, Spring | tsp | Onion, Spring, tsp | 1 | 1 | ||
| 6 | Total: | 49 | |||||||
Testing | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E5 | E2 | =C2&", "&D2 |
| F2:F5 | F2 | =VLOOKUP(E2,Vegetables!B2:C21,2,FALSE) |
| G2:G5 | G2 | =SUM(B2*F2) |
| G6 | G6 | =SUM(G2:G5) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| C2:C5 | List | =Vegetables!$A$2:$A$21 |
| D2:D5 | List | =Units!$A$2:$A$6 |
| My Carb Calculator V1.1.1freshtest.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Onions | Comb/Hide | Carbs | ||
| 2 | Onion, Brown | Onion, Brown, grams | 10 | ||
| 3 | Onion, Brown | Onion, Brown, ml | 5 | ||
| 4 | Onion, Brown | Onion, Brown, tbsp | 2 | ||
| 5 | Onion, Brown | Onion, Brown, tsp | 1 | ||
| 6 | Onion, Brown | Onion, Brown, cup | 20 | ||
| 7 | Onion, Red | Onion, Red, grams | 10 | ||
| 8 | Onion, Red | Onion, Red, ml | 5 | ||
| 9 | Onion, Red | Onion, Red, tbsp | 2 | ||
| 10 | Onion, Red | Onion, Red, tsp | 1 | ||
| 11 | Onion, Red | Onion, Red, cup | 20 | ||
| 12 | Onion, White | Onion, White, grams | 10 | ||
| 13 | Onion, White | Onion, White, ml | 5 | ||
| 14 | Onion, White | Onion, White, tbsp | 2 | ||
| 15 | Onion, White | Onion, White, tsp | 1 | ||
| 16 | Onion, White | Onion, White, cup | 20 | ||
| 17 | Onion, Spring | Onion, Spring, grams | 10 | ||
| 18 | Onion, Spring | Onion, Spring, ml | 5 | ||
| 19 | Onion, Spring | Onion, Spring, tbsp | 2 | ||
| 20 | Onion, Spring | Onion, Spring, tsp | 1 | ||
| 21 | Onion, Spring | Onion, Spring, cup | 20 | ||
Vegetables | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B6 | B2 | =A2&", "&Units!$A2 |
| B7:B11 | B7 | =A7&", "&Units!$A2 |
| B12:B16 | B12 | =A12&", "&Units!$A2 |
| B17:B21 | B17 | =A17&", "&Units!$A2 |
| My Carb Calculator V1.1.1freshtest.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | Units | ||
| 2 | grams | ||
| 3 | ml | ||
| 4 | tbsp | ||
| 5 | tsp | ||
| 6 | cup | ||
Units | |||
