Prev: LookUP function with multiple variables and worksheets
Next: Conditions Not Accurate when subtracting time
From: Mr L Hibbert on 18 Mar 2010 13:29

"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
 | 
Pages: 1
Prev: LookUP function with multiple variables and worksheets
Next: Conditions Not Accurate when subtracting time