3-D function help

  • Thread starter Thread starter Neil22
  • Start date Start date
N

Neil22

Paul, thankyou for the help on the count IF function. I understand the
limitations of 3-D so how to I solve this other challange with my 27
work sheets. I want 26 of the sheets to change if I change the main
page e.g. employee name and the employee sales goal. If this was
within the worksheet I would just enter the other cell e.g. =D5.

Thanyou,

Neil
 
Paul, thankyou for the help on the count IF function.

You should have posted the above response in the same,
i.e. your earlier thread, instead of flinging it here.

Always post your replies to responses received in the *same* thread

ok, back to your Q. Try sheet grouping?

Assume the main sheet is named: Sheet1,
and the others are named: Sheet2 - Sheet27

Assume Sheet1 is the leftmost tab
with Sheet2 - Sheet 27 to the right
(Sheet2 & Sheet 27 are also assumed to be
at the extremes of the pack of the tabs,
i.e. "placeholders")

Click on Sheet2
Hold down Shift key, click on Sheet27
(this "groups" the whole lot of Sheet2-27
for action at one go)

Put in say, A1: =Sheet1!A1
Press Enter

The above will enter the link formula into cell A1
of each of Sheet2 - Sheet 27

To ungroup: Right-click > Ungroup Sheets
or just click on Sheet1
 
Just to add a little more..

To take care of insertions of new columns
into the main sheet: Sheet1
*after* the links in Sheets2-27 are done

Instead of putting in A1: =Sheet1!A1

Try in A1:
=INDIRECT("Sheet1!"&CHAR(COLUMN()+96)&ROW())

(You can copy across up to col Z*, then down
as many rows as there is data in Sheet1)

Now when you insert a column in-between
cols A-Z in the main Sheet1,
and type-in the new headers, etc in the inserted column,
the changes will be reflected in Sheet2-27
(no need to re-do the links)

For a cleaner look, suggest that you suppress zeros from showing in Sheet2

Select Sheet2

Click Tools > Options > View tab > Uncheck "Zero values" > OK
---------------------------------------------------------------------------

Here's the steps if the cols to be linked extend beyond col Z
(some changes needed in the formula
for the starting cell at each break of 26 cols)

To link cols AA - AZ
--------------------------
Put in AA1:
=INDIRECT("Sheet1!A"&CHAR(COLUMN()+70)&ROW())

Copy across to AZ1, then down as many rows as there is data in Sheet1

To link cols BA - BZ
--------------------------
Put in BA1: =INDIRECT("Sheet1!B"&CHAR(COLUMN()+44)&ROW())
Copy across to AZ1, then down as many rows as there is data in Sheet1

To link cols CA - CZ
-------------------------
Put in CA1:
=INDIRECT("Sheet1!C"&CHAR(COLUMN()+18)&ROW())

Copy across to CZ1, then down as many rows as there is data in Sheet1

To link cols DA - DZ
--------------------------
Put in DA1:
=INDIRECT("Sheet1!D"&CHAR(COLUMN()-8)&ROW())

Copy across to DZ1, then down as many rows as there is data in Sheet1

To link cols EA - EZ
-------------------------
Put in EA1:
=INDIRECT("Sheet1!E"&CHAR(COLUMN()-34)&ROW())

Copy across to EZ1, then down as many rows as there is data in Sheet1

And so on ..

The key changes to the formula for the
starting cell at each break of 26 cols
are (using the formula in EA1 as an example):

a. The letter in the phrase e.g.: "Sheet1!E" which has to correspond
to the first letter in the starting cell, i.e. "E" in "EA1" in this case

b. The number for the "..COLUMN()-34..." part inside the CHAR(...)
must equal 97 in the starting cell.

So for EA1, as col EA = col # 131
(i.e. : =COLUMN() in say, EA1 returns 131),
hence 34 needs to be subtracted from COLUMN() to equal 97
 
Max, thanks for the help. I hope this is staying within the same thread
I have another issue.

My 27 worksheets are the maximum number of working days that we wil
work in any month. Days worked will depend on our schedule.
currently use the formula =SUM('Day 1:Day 27'!D5) to total the 27 day
on the month to date sheet.

What I want to do is enter the days worked in a cell on the month t
date sheet and total only those days e.g. if we only worked 22 days
want only the SUM of sheet1 to sheet 22.

Please assist.

Thanks

Nei
 
Try the 2 formulas below, adapted from a post in 2001
by the late MVP George Simms

Assuming the sheets are named Day1, Day2 etc
(i.e. *without* spaces between "Day" and the numbers, e.g. "1")

and you have say, in A5: 1, in B5: 22

Try the array formula
: =SUM(N(INDIRECT("Day"&ROW(INDIRECT(A5&":"&B5))&"!D5")))

To enter the array formula:
Hold down Ctrl + Shift, press Enter, instead of pressing Enter alone

The above will give the sum of cell D5 in sheets "Day1" to "Day22",
irrespective of the sheets' position / order

Or, try Sumproduct instead of Sum (formula need not be array entered)
: =SUMPRODUCT(N(INDIRECT("Day"&ROW(INDIRECT(A5&":"&B5))&"!D5")))

which gives the same result
 
Below is the post by the late MVP George Simm
where he provided a detailed explanation and several formulas ..
(Link: http://tinyurl.com/3yfgt )

Rgds,
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
From: George Simms ([email protected])
Subject: Re: Array formula by George Simms
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.excel.worksheet.functions
Date: 2001-04-13 09:20:06 PST

Hi Hans,

Ok, a big push.

To use this formula the sheet names have to be numbered 1,2,3,......N .

ROW(INDIRECT(B2&":"&B3))

Returns an array of numbers specified in B2 and B3 , say B2 contains 2 and
B3 contains 7, this is ROW(2:7) and returns {2;3;4;5;6;7} this is
the sheet numbers you want to sum .
=SUM(N(INDIRECT({2;3;4;5;6;7}&"!A1")))

The second INDIRECT takes this array and turns it into references to each
sheet.
='2'!A1
='3'!A1
etc...... So changing the numbers in B2 or B3 allows you to dynamically
change the sheets to SUM.
Using N changes the array of references into an array of values, that can
be summed by the Sum function, although in the formula bar, using (F9) will
show the values.

If the Sheets are named Sheet1...Sheet2 etc......the formula can be changed
to:

=SUM(N(INDIRECT("Sheet"&ROW(INDIRECT(B2&":"&B3))&"!A1")))

Use Sumproduct instead of Sum, and formula now need not be array entered:

=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(B2&":"&B3))&"!A1")))

How about :

=SUM(SUMIF(OFFSET(INDIRECT(ROW(INDIRECT(B2&":"&B3))&"!A:A"),,),"<>"))

This will sum the contents of column A bounded by sheets specified in B2
and B3 .
The A:A could also be a range, say A1:A100.

Hope this explains.

All the Best

George Simms
Microsoft MVP - Excel

Newcastle upon Tyne
England.

in message ...
Hoping that George or someone else will give me a little push in fully
understanding his most valuable formula in David Hager's 10th issue of EEE.

This array formula returns the sum of cells in the 3D range bounded by the
sheets named in cells B2 and B3 (my comment: sheet names: 1, 2, 3, ...., n)

=SUM(N(INDIRECT(ROW(INDIRECT(B2&":"&B3))&"!A1")))

I note that the following part of the formula:
ROW(INDIRECT(B2&":"&B3))&"!A1"
returns {"1!A1"\"2!A1"}
I understand it so far, but I do not fully understand why it is necessary
to put these arguments into first another INDIRECT function and then the
N-function. I think George has explained this already somewhere but I do
not know what to search.

Hans Knudsen
-------------------------------------
 
Back
Top