A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formula patterns with a gap of two



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 06:29 PM posted to microsoft.public.excel.worksheet.functions
Mr L Hibbert
external usenet poster
 
Posts: 5
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.