Help with Excel formula

[///AMG]

Any Excel wizards out there help me with a formula

I'll try explain it but can email the workbook to anyone who wants a go..

I have a catalogue of equipment on a sheet called 'Master'. Each item has a unique part number in column A and a total quantity of item in column E. The info in column B is irrelevant.

In the same workbook I have several sheets that personnel have submitted for items they require from the catalogue (not the full catalogue just the lines for items they require). The sheets are named order 1, order 2 etc....The item part numbers are in column A and qty required in column E. Again info in column B is irrelevant.

I need a formula that will update the total quantity column in 'Master' (column E) using the quantities of each part number requested in column E on each sheet submitted by personnel, so I can order the total qty required without going through each sheet adding them up (lots of items and lots of sheets)

I've tried using a SUMIF formula but the problem I'm having is the multiple worksheets and part numbers on different lines in each

Can anyone help

I may treat anyone who can sort me out with a gift from the club shop

 

[The_Notorious_C_A_T]

Would it not just be in the total cell, = master cell whatever - (sheet 1cell blah blah - sheet 2 cell blah blah)

Sorry mis read the part where they just put in sheets iwth what they want in no order.

Could you not give them like the master sheet layout and they only fill in the ones they need so all teh cells that are needed will be the same place on each sub workbook sheet?

 

[J-J]

Not 100% if this is the best way, but could you use a separate sheet called 'Orders' then have a table of all product numbers, then using VLOOKUP from all the other sheets as mentioned above, so;

CellXXX=(VLOOKUP(Partno.),sheet1(partno.)(Quantity))+(VLOOKUP(Partno.),sheet2(partno.)(Quantity)).......

Then have a Sum cell next to this to give you the total quantity, which can then be put into your original master.

Alternatively just have a big table with loads of columns and each column relating to a sheet, then sum them all at the end, wouldn't take long to throw together a sheet of say 200 columns, but depends how many orders you get.

 

[Rhino172]

Would it not just be in the total cell, = master cell whatever - (sheet 1cell blah blah - sheet 2 cell blah blah)

Sorry mis read the part where they just put in sheets iwth what they want in no order.

Could you not give them like the master sheet layout and they only fill in the ones they need so all teh cells that are needed will be the same place on each sub workbook sheet?

this tbh. will be easiest way.

One tab as the master and several other tabs (order 1, 2, 3 etc) as exact copies of the master.

Then simply on the master sheet in cell E2 for example do =SUM('order 1'!E2+'order 2'!E2)

will be much easier rather than the people creating the order sheets having to enter part numbers and quantaties a in different order to the master sheet.