All Sales Sheet

[Legacy 436357]

Hi,

Is it possible to have an updated master sheet to keep track of all sales from other sheets? As sales are added on those sheets it will be also put on this updated master sheet.

I have sheets: Region 1, Region 2, and Region 3.

These sheets have the same headings except for Column A on those sheets which just have in cell A1 the sheet name.

Thank you very much for your help,
XJ

Excel Workbook
	A	B	C	D	E	F	G
1	Region	Date	Company	Product	Quantity	Price Each	Extended
2
3
4
5
Summary

 

[sandy666]

try PowerQuery and Append feature

eg.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Region[/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Company[/td][td=bgcolor:#70AD47]Product[/td][td=bgcolor:#70AD47]Quantity[/td][td=bgcolor:#70AD47]Price Each[/td][td=bgcolor:#70AD47]Extended[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Region1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Region1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Region2[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Region3[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Region3[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]

 

[Legacy 436357]

Thank you for the reply but is it possible to use formula arrays to do this please?

 

[sandy666]

is it possible to use formula arrays to do this please?

maybe someone else

 

[Dave Patton]

Why not use one sheet?

Try Pivot Table Consolidate

 

[Legacy 436357]

How can I do that please? I want the other sheets to have the data entered but have the Summary sheet keep the running entries.

Thank you for helping

 

[sandy666]

you can try Alt+D+P as suggested above but you will get SUM for each region, not individual values

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7]Column[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Row[/td][td=bgcolor:#DDEBF7]Company[/td][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Extended[/td][td=bgcolor:#DDEBF7]Price Each[/td][td=bgcolor:#DDEBF7]Product[/td][td=bgcolor:#DDEBF7]Quantity[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Region3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[/table]

 

[Dave Patton]

Converse of Pivot Table Consolidation

If one has all the data for several regions on one Sheet, a Pivot Table can create summaries etc.
All detail for a particular region can be seen by expanding the summary for that region.

 

[sandy666]

or Data - Data Tools - Consolidate

don't forget check Create links to source data

 

[Legacy 436357]

I appreciate the help but this seems way too complicated.

I will restructure my data instead.