function to auto-populate a cell?

[lincolnelectra]

Hi all!
I like to think I'm a pretty savvy excel user, but when it comes to functions I'm definitely the most inferior.

I have a spreadsheet I use to automate soap recipes. I would like a cell to return a value based on what I type in a cell beside it (well, a couple over to be exact). the value it would return would be based on another sheet within the book.

what I do right now is type in the oil I'm using (eg olive), how much of it, the SAP value of the oil, and then it calculates how much sodium hydroxide I need (using very basic formulas of F3=D3*B3). I have all the SAP values entered on another sheet at the back of the workbook so I just flip over and enter it in the appropriate cell (D3), but in my dream world when I type "olive oil" in A3, D3 will magically produce "0.134". "0.134" being the SAP value that I presently enter manually after looking it up on the sheet at the back.
is there any way to automate this? I started looking at IF functions, but I can't figure out how to get it past 2 arguments, and it seems like a LOT of typing since I'm using text instead of a number that could be greater than or less than.

if someone could offer some help I would greatly appreciate it! even if you can tell me which function would be appropriate I can find some tutorials I'm sure.

Thanks very much!
Bianca

[TMS]

Hi Bianca,

You will, very likely, be asked to change your thread title because it breaches forum rules.

Please do that. In the meantime, have a look at VLOOKUP. Should be exactly what you want.


Regards, TMS

[lincolnelectra]

Bah first post and I'm already making mistakes!
My apologies!

Thanks very much for the suggestion, I will check that out!

[FDibbins]

Hi and welcome to the forum

Yes, sorry but Trevor is correct

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[jhren]

Bianca,

See attached. Check out Jay's olive oil soap sheet (3-3070). Click on either of the two oils, and the drop down button that appears to the right (this is called Data Validation with in-cell drop-down list, though the button technically is outside the cell ). As a demonstration change oil, and then change back.

[lincolnelectra]

I know I responded to this, but evidently my phone didn't complete the transaction.

jhren! you = magic! thank you so much!!! what wizardry created this magical spreadsheet? please teach me your secrets!

Bianca,

See attached. Check out Jay's olive oil soap sheet (3-3070). Click on either of the two oils, and the drop down button that appears to the right (this is called Data Validation with in-cell drop-down list, though the button technically is outside the cell ). As a demonstration change oil, and then change back.

[jhren]

...
jhren! you = magic! thank you so much!!! what wizardry created this magical spreadsheet? please teach me your secrets!

Your quite welcome! ...but no magic involved ...and no secret either

What I did was make use of Data Validation coupled with named range and VLOOKUP techniques. There's plenty of info already on the 'net regarding these topics...

[lincolnelectra]

awesome! thanks very very much to you both (jhren and TMShucks) now that I know what to look for, find it I will!

[TMS]

jhren has used a Named Range for the VLOOKUP.

In its simplest form, the formula in D3 would look like: =VLOOKUP(A3,SAPs!$A:$B,2,FALSE)

To take jhren's Named Ranges one stage further, you could have a Dynamic Named Range. Then you can add entries to the table and not need to change the range definition.

Define oil as:

=SAPs!$A$1:INDEX(SAPs!$A:$A,COUNTA(SAPs!$A:$A))

and oil_n_SAP as:

=SAPs!$A$1:INDEX(SAPs!$B:$B,COUNTA(SAPs!$A:$A))

Or, better, add headers and convert the list to a table. That gives you the capability to sort and filter the list.

If you add headers, the Dynamic Named Ranges would become:

oil:

=SAPs!$A$1:INDEX(SAPs!$A:$A,COUNTA(SAPs!$A:$A))

oil_n_SAP:

=SAPs!$A$1:INDEX(SAPs!$B:$B,COUNTA(SAPs!$A:$A))

See the updated workbook.



Regards, TMS

[jhren]

...

Or, better, add headers and convert the list to a table. That gives you the capability to sort and filter the list.

If you add headers, the Dynamic Named Ranges would become:

oil:

=SAPs!$A$1:INDEX(SAPs!$A:$A,COUNTA(SAPs!$A:$A))

oil_n_SAP:

=SAPs!$A$1:INDEX(SAPs!$B:$B,COUNTA(SAPs!$A:$A))

...

If headers are added and 2D range is converted to a Table, you can use structured references in the respective Named Range formulas.

Using your revised workbook...

oil: =Table1[Oil]
oil_n_SAP: =Table1

This would also make the named ranges dynamic... as long as any modification to the list is within the bounds of the Table.

Actually, oil_n_SAP named range could be deleted , and simply use "Table1" in the VLOOKUP formulas... and "Table1" could be renamed to something more indicative of its contents.

[TMS]

@jhren: good thought.

You could actually ditch the Dynamic Named Ranges and use the Table entries directly.

Regards, TMS

[lincolnelectra]

sweet!
you guys rock!! thanks again!

[TMS]

Thank you.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.