|
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 |
summaries on alternative sheets
-- 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 ! |
summaries on alternative sheets
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 |
summaries on alternative sheets
-- 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 |
summaries on alternative sheets
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. |
summaries on alternative sheets
-- 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 |
summaries on alternative sheets
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. |
| All times are GMT +1. The time now is 08:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com