New to Excel...need help with formula

[brentgrigsby]

I have an Excel spreadsheet that I need help on.
I just need the general code and I can import it from there...
If my assistant enters the letter XP, it grabs a particular cell that has XP cost and multiplies it by the amount of gallons used in another cell.
If however, my assistant uses only X, it grabs a different call that has X cost and multiplies it by the amount of gallons used in another cell.

Don't know how to post the Excel spreadsheet, which would probably help you see the flow.

Thank you.

Regards,
Brent

 

[sheetspread]

Which cell has the XP cost and which cell has the description label?

 

[Smitty]

Welcome to the Board, Brent!

If you look at the HTML Maker link in my sig you'll be able to post a shot(s) of your sheet.

As for the question, you might want to look at VLOOKUP

HTH,

EDIT: I'm moving your thread to the Excel Questions forum...

 

[brentgrigsby]

https://www.flickr.com/photos/123379672@N02/

You can see my excel sheet on the link.

I tried to use your link Smitty but did not understand it very well. Sorry.

I want the formula to show that if my painter uses 1 product and puts in the legend, then the legend will dictate the price based upon the cost next to the legend and multiply that price x the difference

For example:

If an M was input in cell B8, I would know that it would mean the painter used

Macro 646 Kit/Gal. (at $39.71/gallon)

<colgroup><col width="94"><col width="66"><col width="54"></colgroup><tbody>
</tbody>

If the difference in the field in cell E8 was 2 then the formula in cell
c19 would calculate at the cost of the Macro 646 ($39.71) x 2 gallons = $79.42

Any thoughts?

 

[sheetspread]

https://www.flickr.com/photos/123379672@N02/
You can see my excel sheet on the link.

There wasn't anything there when I clicked on it

I tried to use your link Smitty but did not understand it very well. Sorry.

Which Excel version do you have?

 

[brentgrigsby]

oops sorry Sheetspread,

here is a better link....

http://i865.photobucket.com/albums/ab218/Rastus8/MREXCELPICFORFORMULA_zps7da86bff.jpg


Excel Version 2010

 

[sheetspread]

If B2 has XP, this formula in another cell:

=INDEX(C$15:C$21,MATCH(B2,$I$15:$I$21,0))

will return the value in C15:C21 whose corresponding legend value in column I = XP.


(You probably know this but the row/column reference components without dollar signs will change in the direction you copy and paste)


Is that close to what you're after?

 

[sheetspread]

Actually, this is closer to what you're asking for (if column I's legend is the same as G's but with a letter P each time)

in C2 paste this:

=VLOOKUP(LEFT(B2,1),$G$15:$J$21,2+2*(RIGHT(B2)="P")+0,0)

 

[sheetspread]

Oh wait the mix of text and numbers is better handled by the ctrl-shift-enter formula:

=VLOOKUP(SUBSTITUTE(B2,"P",""),TEXT($G$15:$J$21,0),2+--(RIGHT(B2,1)="P")*2,0)

in C2

 

[brentgrigsby]

sheetspread, I do appreciate everything. The formulas did not work.


My goal is: If my assistant enters in the legends from B8 to B11 for coatings, the values calculate automatically in C19 for coatings. So it would take the coating cost x the quantity of that product used.

If my assistant enters in the legends from B2 to B5 for blasting media, the values of how much I used multiplied by the cost of the legends id would calculate automatically in C20.
There are only 4 values for blasting media cost, (2040 and 2040P) and (G and GP). Everything else belongs to coating/paint cost.

I can email the spreadsheet to someone if it helps.