Spreadsheet with multi formulae help.

[glassmanreg]

Hi everyone. This is a difficult one (for me at least).
I have a spreadsheet for the office. Rows depicting stock items, columns depicting vehicles stock is taken for. With totals at the end of each column and totals at the end of each row.
What i'm looking for is the correct formula/ or formulae that allows the following.
If somebody takes a quantity of 2 (for example.degreaser tins) and places them onto vehicle 1, i want to just place the number 2 in that relevant box, the total for that vehicle column will show 2. But at the same time, the total at the end of that row will display the total cost of the items taken. Is it possible to have a value hidden within the relevant rows which calculate for each quantity entered?
I've Attached the empty sheet for you to see what i mean

[pmalen]

Take a look at the attached. Is that what you are looking for?

[glassmanreg]

that is perfect, exactly what i was after. I knew it could be done as i've used it many moons ago but i couldnt for the life of me remember how it ws done.
many thanks for that.

[glassmanreg]

i've had a closer look, the first 4 stock items are working. but when i enter a quantity in the lower items the figure comes out wrong?

[Apelcius]

the formula wasn't really populated throughout the spreadsheet, it looks like. so, you'll have to go through, and populate the vlookup formula to the whole thing. currently, what it seems that pmalen did was to show you a method to achieve the calculations, by use of the chart. the equations in the first few should give you an idea of how to apply it to the rest of the 'J' column.

[pmalen]

the formula wasn't really populated throughout the spreadsheet, it looks like. so, you'll have to go through, and populate the vlookup formula to the whole thing. currently, what it seems that pmalen did was to show you a method to achieve the calculations, by use of the chart. the equations in the first few should give you an idea of how to apply it to the rest of the 'J' column.

Apelcius is correct. I just did the first section at the top to validate what you were looking for. You should be able to copy/paste the formulas to the rest of the worksheet.

Sorry about that.

Paul

[glassmanreg]

Apelcius is correct. I just did the first section at the top to validate what you were looking for. You should be able to copy/paste the formulas to the rest of the worksheet.

Sorry about that.

Paul

no i wasnt asking you to do the whole of the sheet buddy, just a pointer is all. You've already helped loads, just the final part of the first section that isnt working properly

[pmalen]

See new version of file.

Last argument of the vlookup function is range_lookup. I made the range lookup 0 and now it works like you want.

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Paul

[glassmanreg]

No i know it wasnt populated througout the sheet, what i mean is the calculations work perfectly for the:-
ADHESIVE
DEGREASER
SINGLE EDGED BLADES
STANLEY BLADES
rows, but the calculations are nt working for the:-
LONG BLADES
TISSUE ROLL
CUTTING GLOVES
RUBBER GLOVES
rows. Once i can get a solution to the latter part of this problem. I'll be able to populate the rest of the sheet.

[glassmanreg]

Yes that works perfectly many thanks.
Looking at the formula for the totals column, if say i wanted to add another stock item in the future, placing a new item in row 11, then the correct formula for that line would be

=SUM(B11:I11)*VLOOKUP(A11,Costtable,2,0)

as long as i enter the new item on costtable?

[glassmanreg]

ignore my last reply, i have sorted everything.

Thanks again for your help gents, its been very much appreciated.

[glassmanreg]

please mark as solved, not too sure how to do this myself. Thanks

[Apelcius]

no problem

to mark as 'solved': just click 'Edit' on your original post, then click 'Advanced', and change prefix to 'SOLVED'.

i wish those that ran these forums would put an easier way of doing it, but... meh what can ya do.

[glassmanreg]

This isnt solved, sorry admin.
I've populated the list following all the excellent pointers by Paul changing the perameters where and when needed. But, when i go to add extra stock items, the *vlookup code wone allow me, i'm still getting the #N/A message.
Is there a hidden code in the costtable that i need to edit?

[pmalen]

I moved the table to a different sheet and left room to put more entries in.

Paul

[glassmanreg]

Thanks Paul, that should save what hair i have left on my head. I hate being a novice lol.