If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formula patterns with a gap of two
"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 ! 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, which would take forever.... Regards Lloyd -- L Hibbert |
Thread Tools | |
Display Modes | |
|
|