Consolidation Referencing

[Excelexplorer]

Hi Fellows


My question for todays post is how to consolidate the referencing in Excel. For example if you have mixed data in 3 sheets and you want to consolidate these 3 sheets into one sheet with relevant sheet reference how we do it in Excel 2003.


All sheets have following head rows:

Seller's name; Region; Item1; Item2; Item3; Total;Sheet Ref


Input Data

A,North,100,20,30,150 = Sheet1

B,North,100,0,0,100 = Sheet1

B,South,20,40,60,120 = Sheet2

C,West,50,40,100,190 = Sheet3


Output sheet

A,North,100,20,30,150,Sheet1

B,North;South,120,40,60,220,Sheet1;Sheet2

C,West,50,40,100,190,Sheet3


Each sheet name is unique and different, my task is to consolidate all sheets data into one sheet with the region's name and sheet name reference into single row infront of Seller's name row.


I tried consolidate feature it only consolidate nos and left column i.e. seller's name.


Pl. help


Best regards

 

[Luke M]

I'd try on of Ron de Bruin's methods. Here's got several good examples dealing with copy/merging data.

My best guess:

http://www.rondebruin.nl/copy2.htm


But this might help too:

http://www.rondebruin.nl/summary.htm

 

[Excelexplorer]

Thanks Very much LUKE. I will try and let you know about results.


BR.