Linking Specific Text to a Formula

[R. Ford]

Good morning everyone,

I am creating a worksheet to show the profit margin of asset recovery from
returned goods (in this case cellular phones). I have a data validation list
of the four ways we recover as much money as we can based on the return:

Ebay
DOA Dealer Return
Device Recovery
Wholesale

I would like to like the "Amount Recovered" column to be directly correlated
with those 4 categories so when we have someone entering data, they don't
have to memorize formulas/prices... they just select the type of sale from
the drop down menu and everything is done for them.

What I need is the following:

Ebay - =("undetermined fixed cost"*.14) to take into account Ebay/PayPal fees

DOA Dealer Returns - =119.99

Device Recovery - =7.89

Wholesale - =("undetermined fixed cost"*"undetermined shipping cost")

Any suggestions on linking those words to those formulas/numbers would be
awesome!

Thank you in advance,

Ryan F.

 

[Sean Timmons]

So, you have your 4 options in, say, sheet2 A2 - A5. Give this list a defined
name ( Insert - Name - Define)

In B2 - B5, you have your formulas.

In your worksheet, use data validation to ensure they use the exact spelling

Data - Validation - Pick from list. and you reference your defined name. =NAME

Then, in your calculation field, enter
=if(A2="","",VLOOKUP(A2,sheet2!A2:B5,2))

make sense?

 

[R. Ford]

Yes, that makes sense... however...

I already have the worksheet set up:

Type of sale starts in P12, which is my drop down menu of the four options.
Amount recovered begins in cell Q12.

I have very little experience with VLOOKUP although I HAVE used it before.
Can you give me a formula example for just the Ebay one? I can figure it out
for the other ones if I have something to go off of.

Last time I used VLOOKUP it was just linking numbers and turning them into
words... this time I need it to be formulas, does it work the same way?

Sorry for the confusion!