Excel help

Sunstrand

Hey guys, I'm terrible at using excel because I have no training and use it once every 5 years or so. What im trying to do is help me speed up my creating monthly bonus sheets for me and my crew. On the excel sheet i have colums for the customer names, dates, service order number, bonus level, and dollar amount of the bonus. What i would like to do is have it so I can enter all of our bonus levels, A1-A8 B1-B8 and C1-C8, so when i type in bonus level B-2 the dollar amount comes up automaticly.

I'm not sure if im explaining what i want to do clearly enough but thats what I'd like to accomplish. Can anyone help me out please?

Edit: For clarification here is a screen grab of an almost completed bonus sheet.


If i could get the end item to look like this one i would be extremely happy, thanks in advance.

Post

Sounds like what you want is the =IF(CELL REFERENCE=A1,400,"")


Where 400 would be the bonus that corresponds to that bonus level and the cell reference would be the cell that the bonus code would be typed in. You would put this formula in the code where you want the bonus amount to show up.


There may be a better way to do this but this seems like a simple way to achieve what you want.


The above formula also puts no value if not equal to A1.


Hope this helped at least a little bit, if not I am sure others will have other ideas!

Sunstrand

OK i've figured out how to add multiple values, now my question is how to I format the whole colum to refer to the box to its left?

I hope there is a way to change all the refrencees at once..
this is what i mean eg,

From this:
"=IF(H8="B1",165,IF(H8="B2",185,IF(H8="B3",210,IF(H8="B4",280,IF(H8="B5",315,IF(H8="B6",345,IF(H8="B7",385,IF(H8="B8",420,0))))))))"
to this:
"=IF(H9="B1",165,IF(H9="B2",185,IF(H9="B3",210,IF(H9="B4",280,IF(H9="B5",315,IF(H9="B6",345,IF(H9="B7",385,IF(H9="B8",420,0))))))))"
Without typing a shit load of formulas.

Cauld

Copying and pasting the cell with the formula will make all the references in the cell where you paste it change so the formula is relatively the same.

For example. the formula you have listed above is in cell c6 and you copy it and paste it into cell E6 all of the H8 in your formula will change to J8 since E6 is to columns greater than C6

Likewise if you move it one column to the right all of the cell reference numbers will increase by one. so H2 will become H3.

If there's a part of your formula you don't want to change this way put the $ sign in front of it, that will keep that reference absolute despite copying and pasting it into different cells. Putting $ in front of the letter will keep the column from changing and putting the $ in front of the number will keep the row from changing, You will need a $ in front of both the letter and number of a cell reference for it to not change at all.

Mr Obersmith

You could also this.

On Sheet 2 create two columns with the grade range in column A and the bonus amount in column B so it looks something like this.

Grade Bonus
C-1 100
C-2 200
C-3 300

Then, back on sheet 1, let's say you type in the grade range C-1 in cell A1. In cell B2 enter the following formula =VLOOKUP(A1,Sheet2!$A$2:$B$4,2). What this will do is lookup the value in cell A1 (in this case C-1) and match against the table on Sheet 2 and return whatever value is in the column next to it (in this case 100). This way you have a smaller formula and in the case the bonus amounts change, you only have to change the values in the Sheet 2 table instead of all of the formulas.

If you want to copy this to other cells you can also click on the lower right corner of the cell with the original formula and drag it down.

Sunstrand

Thanks for the help guys I have a workable set up spreadsheets now.