summaries on alternative sheets

hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???


--
L Hibbert

--
L Hibbert


"Mr L Hibbert" wrote:

hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???


--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....


Please help excel wizards !

On Mar 17, 8:52*pm, Mr L Hibbert
wrote:
--
L Hibbert





"Mr L Hibbert" wrote:
hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only *a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???

--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....

Please help excel wizards !- Hide quoted text -

- Show quoted text -

try this formula:
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$P
$48)+2*(COLUMN(A1)-1))
*u can drag it to ur right, but not to ur left.
assume the data u wanted to extract starts from column P, and it's in
the 48th row in sheet 'Customer 1'
change the sheet_name, row, and/or starting_column to cater to ur
need.

hope this helps.

MinYeh

--
L Hibbert


"minyeh" wrote:

On Mar 17, 8:52 pm, Mr L Hibbert
wrote:
--
L Hibbert





"Mr L Hibbert" wrote:
hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???

--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....

Please help excel wizards !- Hide quoted text -

- Show quoted text -

try this formula:
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$P
$48)+2*(COLUMN(A1)-1))
*u can drag it to ur right, but not to ur left.
assume the data u wanted to extract starts from column P, and it's in
the 48th row in sheet 'Customer 1'
change the sheet_name, row, and/or starting_column to cater to ur
need.

hope this helps.

MinYeh
.

thanks for your help althou i cannot get it to work,

it may be a problem if the table is trying to get data horizontally, in a
format vertically
If you see Customer 1
D E
F G
line ITEMS Units sold (Jan) Sales Value (Jan) Units sold (feb) sales
value (Feb)
45 A 1 2500 0
0
46 B 3 6000 3
2500
47 C 5 5000 1
1000
48 TOTALS 9 13500 4
3500

then into summary

Date Customer 1 Customer 2 etc....
units/value
JAN so this formula needs D48 and and then follow the pattern for
alternative columns
FEB this will need F48
MAR

can anyone help with the formula query, i have other customers with detail sales, so one formula would be very simple than that = that and that = that and so on


Regards
Lloyd

On Mar 18, 10:55*pm, Mr L Hibbert
wrote:
--
L Hibbert





"minyeh" wrote:
On Mar 17, 8:52 pm, Mr L Hibbert
wrote:
--
L Hibbert

"Mr L Hibbert" wrote:
hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only *a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???

--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....

Please help excel wizards !- Hide quoted text -

- Show quoted text -

try this formula:
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$P
$48)+2*(COLUMN(A1)-1))
*u can drag it to ur right, but not to ur left.
assume the data u wanted to extract starts from column P, and it's in
the 48th row in sheet 'Customer 1'
change the sheet_name, row, and/or starting_column to cater to ur
need.

hope this helps.

MinYeh
.

thanks for your help althou i cannot get it to work,

it may be a problem if the table is trying to get data horizontally, in a
format vertically
If you see Customer 1
* * * * * * * * * * * *D * * * * * * * * * * * * * E * * * * * * * * * * * *
F * * * * * * * * * *G
line *ITEMS * Units sold (Jan) * Sales Value (Jan) Units sold (feb) sales
value (Feb)
45 * * A * * * * * * *1 * * * * * * * * * * * * 2500 * * * * * * * * * *0 * *
* * * * * * * * * 0
46 * * B * * * * * * *3 * * * * * * * * * * * * 6000 * * * * * * * * * *3 * *
* * * * * * * * * 2500
47 * *C * * * * * * *5 * * * * * * * * * * * *5000 * * * * * * * * * * 1 * *
* * * * * * * * 1000
48 * *TOTALS * *9 * * * * * * * * * * * *13500 * * * * * * * * * 4 * * * * *
* * * * * *3500

then into summary

Date * * * Customer 1 * * * * Customer 2 etc....
* * * * * * units/value * * * * * * *
JAN * *so this formula needs D48 and and then follow the pattern for
alternative columns
FEB * *this will need F48
MAR

can anyone help with the formula query, i have *other customers with detail sales, so one formula would be very simple than that = that and that = that and so on

Regards
Lloyd- Hide quoted text -

- Show quoted text -

Using similar formula. if ur first cell is D48, and u wanted it to be
dragged vertically rather than horizontally, then
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$D$48)+2*(ROW(A1)-1))
note that now
1. the row is locked: 'Customer 1'!$48:$48
2. the starting cell is locked: 'Customer 1'!$D$48
3. the multiplier is change from 2*(Column(A1)-1) to: 2*(Row(A1)-1) so
that when u drag the formula down, the row number changes, so ur
multiplier changes.

this will work. for another customer, u can change the sheet name.
or if u wanted it to be more automated, u can use an INDIRECT function
(Customer ID is in Row1, Date in column A, so data starts from Column
B)
=INDEX(INDIRECT("'"&B$1&"'!$48:$48"),,COLUMN(INDIR ECT("'"&B$1&"'!$D
$48"))+2*(ROW(A1)-1))
In this case, when u enter the customer ID (or equivalently ur sheet
name) in B1, the formula can change the referencing sheets when u drag
it horizontally to ur right.

--
L Hibbert


"minyeh" wrote:

On Mar 18, 10:55 pm, Mr L Hibbert
wrote:
--
L Hibbert





"minyeh" wrote:
On Mar 17, 8:52 pm, Mr L Hibbert
wrote:
--
L Hibbert

"Mr L Hibbert" wrote:
hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???

--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....

Please help excel wizards !- Hide quoted text -

- Show quoted text -

try this formula:
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$P
$48)+2*(COLUMN(A1)-1))
*u can drag it to ur right, but not to ur left.
assume the data u wanted to extract starts from column P, and it's in
the 48th row in sheet 'Customer 1'
change the sheet_name, row, and/or starting_column to cater to ur
need.

hope this helps.

MinYeh
.

thanks for your help althou i cannot get it to work,

it may be a problem if the table is trying to get data horizontally, in a
format vertically
If you see Customer 1
D E
F G
line ITEMS Units sold (Jan) Sales Value (Jan) Units sold (feb) sales
value (Feb)
45 A 1 2500 0
0
46 B 3 6000 3
2500
47 C 5 5000 1
1000
48 TOTALS 9 13500 4
3500

then into summary

Date Customer 1 Customer 2 etc....
units/value
JAN so this formula needs D48 and and then follow the pattern for
alternative columns
FEB this will need F48
MAR

can anyone help with the formula query, i have other customers with detail sales, so one formula would be very simple than that = that and that = that and so on

Regards
Lloyd- Hide quoted text -

- Show quoted text -

Using similar formula. if ur first cell is D48, and u wanted it to be
dragged vertically rather than horizontally, then
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$D$48)+2*(ROW(A1)-1))
note that now
1. the row is locked: 'Customer 1'!$48:$48
2. the starting cell is locked: 'Customer 1'!$D$48
3. the multiplier is change from 2*(Column(A1)-1) to: 2*(Row(A1)-1) so
that when u drag the formula down, the row number changes, so ur
multiplier changes.

this will work. for another customer, u can change the sheet name.
or if u wanted it to be more automated, u can use an INDIRECT function
(Customer ID is in Row1, Date in column A, so data starts from Column
B)
=INDEX(INDIRECT("'"&B$1&"'!$48:$48"),,COLUMN(INDIR ECT("'"&B$1&"'!$D
$48"))+2*(ROW(A1)-1))
In this case, when u enter the customer ID (or equivalently ur sheet
name) in B1, the formula can change the referencing sheets when u drag
it horizontally to ur right.
.
Thanks its getting there, with reference to your 2nd formula,

i assume customer 1 goes inbetween """"......correct?
i cannot get it to work

On Mar 19, 7:52*pm, Mr L Hibbert
wrote:
--
L Hibbert



"minyeh" wrote:
On Mar 18, 10:55 pm, Mr L Hibbert
wrote:
--
L Hibbert

"minyeh" wrote:
On Mar 17, 8:52 pm, Mr L Hibbert
wrote:
--
L Hibbert

"Mr L Hibbert" wrote:
hello im trying to compile a small folder consisting of different worksheets,

each worksheet is a different customers sales etc.... with totals for JAN,
FEB etc throught to the year,

these totals for each customer month are to be summarised in a summary
spreadsheet,

how do i extract the data from the customer folder to the summary box.

at the moment i can only *a = formula, but copy and paste does not allow the
boxes to use the formula for all the sheets, this is hard to explain

For example

A - summary
B - customer 1, detailed sales for jan, feb, mar, etc.. with totals of each
month
C - customer 2
D - customer 3
E - customer 4

The A -summary spreadsheet get each total of month for each customer to
represent total monthly sales. i have by units and figures.

Can anybody share some wisdom on excel???

--
L Hibbert

Or formula issue......horizontally

my formula ='Customer 1!P$48

but for the next formula i will need ='Customer 1!R$48

my formula needs to account for data, no data then data.

So the next formula will need to be ='Customer 1!T$48

increasing by 2 alphabetically

how do i get excel to recognise the pattern that the data needs to be done
every two cells not one cell after the other. copy and paste does not work it
assumes i want data, data data, every line,

i want data, no data, data....

Please help excel wizards !- Hide quoted text -

- Show quoted text -

try this formula:
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$P
$48)+2*(COLUMN(A1)-1))
*u can drag it to ur right, but not to ur left.
assume the data u wanted to extract starts from column P, and it's in
the 48th row in sheet 'Customer 1'
change the sheet_name, row, and/or starting_column to cater to ur
need.

hope this helps.

MinYeh
.

thanks for your help althou i cannot get it to work,

it may be a problem if the table is trying to get data horizontally, in a
format vertically
If you see Customer 1
* * * * * * * * * * * *D * * * * * * * * * * * * * E * * * * * * * * * * * *
F * * * * * * * * * *G
line *ITEMS * Units sold (Jan) * Sales Value (Jan) Units sold (feb) sales
value (Feb)
45 * * A * * * * * * *1 * * * * * * * * * * * * 2500 * * * * * * * * * *0 * *
* * * * * * * * * 0
46 * * B * * * * * * *3 * * * * * * * * * * * * 6000 * * * * * * * * * *3 * *
* * * * * * * * * 2500
47 * *C * * * * * * *5 * * * * * * * * * * * *5000 * * * * * * * * * * 1 * *
* * * * * * * * 1000
48 * *TOTALS * *9 * * * * * * * * * * * *13500 * * * * * * * * * 4 * * * * *
* * * * * *3500

then into summary

Date * * * Customer 1 * * * * Customer 2 etc....
* * * * * * units/value * * * * * * *
JAN * *so this formula needs D48 and and then follow the pattern for
alternative columns
FEB * *this will need F48
MAR

can anyone help with the formula query, i have *other customers with detail sales, so one formula would be very simple than that = that and that = that and so on

Regards
Lloyd- Hide quoted text -

- Show quoted text -

Using similar formula. if ur first cell is D48, and u wanted it to be
dragged vertically rather than horizontally, then
=INDEX('Customer 1'!$48:$48,,COLUMN('Customer 1'!$D$48)+2*(ROW(A1)-1))
note that now
1. the row is locked: 'Customer 1'!$48:$48
2. the starting cell is locked: 'Customer 1'!$D$48
3. the multiplier is change from 2*(Column(A1)-1) to: 2*(Row(A1)-1) so
that when u drag the formula down, the row number changes, so ur
multiplier changes.

this will work. for another customer, u can change the sheet name.
or if u wanted it to be more automated, u can use an INDIRECT function
(Customer ID is in Row1, Date in column A, so data starts from Column
B)
=INDEX(INDIRECT("'"&B$1&"'!$48:$48"),,COLUMN(INDIR ECT("'"&B$1&"'!$D
$48"))+2*(ROW(A1)-1))
In this case, when u enter the customer ID (or equivalently ur sheet
name) in B1, the formula can change the referencing sheets when u drag
it horizontally to ur right.
.
Thanks its getting there, with reference to your 2nd formula,

i assume customer 1 goes inbetween """"......correct?
i cannot get it to work

hmm... i don't get what u mean by: {i assume customer 1 goes inbetween
""""}
Supposedly, ur data for each customer is stored in each individual
sheet named accordingly by the customer's name or id
so, for example: if the sheet name is named by customer name, "David
Paul"
then ur reference should be your customer name 'David Paul'!$D$48; if
however, it is named after its ID, "DP001"
then ur reference should be your customer ID 'DP001'!$D$48; for
summary, it is then 'Summary'!$D$48

For indirect, the first "'" is actually " + ' + " (if u count, it's 5
's instead of 8 's u typed), the second part B$1 is ur sheet name,
followed by the third part " + !$48:$48 + "
if u still can't get it working, ask me for a sample file.