Consolidating data from multiple worksheets

please attach a zipped example

heres a cut down version^

sorrt mate its still not too clear
could you fill in the consolidation (and highlite cells)with what you'd expect too see?

need to search all worksheets specific for part #s(column e) getting the total amount (from column c) of that part form all sheets and adding up into the consolidated worksheet ( into column c)


Starting in worksheet consolidate C3 find worksheet consolidate E3 in worksheet unit 1 (column e) and worksheet unit2 (column e) and then taking the amount (in column c) associated with the part # and add them up in worksheet consolidate C3


Hope this clarifies not confuses

thanks

this simply does just that
there is more you could do like return part name as well when code put in

Please Login or Register  to view this content.

you need quotation marks if there is a space in sheet name
so .... sheet1! is ok but.... sheet 1! ....needs to be 'sheet 1'!

IF(ISBLANK(E64),0,"(INDEX('Battalion 1 (1287)'!$C:$E,MATCH(E64,'Battalion 1 (1287)'!$E:$E,0),1))+(INDEX(Battalion 2 (889)'!$C:$E,MATCH(E64,Battalion 2 (889)'!$E:$E,0),1)))")

still have the problem of the " before the first INDEX and at the end of the function befor the last )

Another question regarding the same code is the a way of say incorporating all the worksheet excluding the one the code is on. I have over 28 worksheet was wondering do i have to input all the worksheet separately into the code.

=IF(ISBLANK(E64),0,",(INDEX('BATTALION 1'!$C:$E,MATCH(E3,'BATTALION 1'!$E:$E,0),1))+(INDEX('BATTALION 2'!$C:$E,MATCH(E3,'BATTALION 2'!$E:$E,0),1)))")


i cut and pasted what you put but it puts in the " mark infront of the first ,(INDEX and at the very end )))")

I m confused does it matter what version Excel I'm using

OK here's a cut down version of my actual workbook hope this helps

The last page (combination list or consolidation list )starts a line 64

Thanks for all the help

ok thats sorted
but i'm not really sure if this is the best way to go about it
it would be easier if all tabs had same same listings
with zero in if they havent got it
then your consolidation sheet could simply sum the same cell from each tab
you could still group by comms/emergency kit/consumables or such
and in the long run it would be easier to manage

which bit gives n/a ?

The in the consolidated sheet where the numbers are suppose to add up if one of the sheets doesn t have the part# the value givin in Column c in N/A#

to get rid of that ,formula will have to read (in sheet combination c64)
wait for it.

Please Login or Register  to view this content.

i think we could do with some help here!!!! can anyone out there sort this?