consolidated data in summary sheet

Hi there, Im a beginner here trying to figure out how to consolidate my data from selected worksheet as below :


items cost vol A vol B
1
2
3
4

sheet 1 sheet 2 sheet 3


Basically I just want to get a summary page from the multiple worksheet


item Vol Total Sum
1 A
2 A
3 B
4 A



So some of the items will follow Vol A and some of them will follow Vol B which is demonstrated to see the budget impact in the summary page.
Then since the sheet1 , sheet2 and sheet 3 maybe amended by adding some column or comment in between by different users, therefore I wonder how to use vlookup or sth else to make sure i capture the cost and then times the Vol A or B ( will be decided in the summary page)

not sure if i get myself clear

thanks for the help
Book1.xlsx

try this, copied down, and adjust the sheet name accordingly for the other sheets...

=INDEX(Sheet2!$A$1:$B$5,MATCH(Summary!A2,Sheet2!$A$1:$A$5,0),MATCH("Cost",Sheet2!$A$1:$B$1,0))*INDEX(Sheet2!$A$1:$D$5,MATCH(Summary!A2,Sheet2!$A$1:$A$5,0),MATCH(IF(B2="A","vol ( plan A)","vol ( plan B)"),Sheet2!$A$1:$D$1,0))

Also please note, that some of your headings on sheets 2-4 have a space at the end. remove that space,or the formula will not work.

let me know how you make out please

my VBA is very weak, but you could try using find/replace?