Replace all numbers in formulas with a calculation

[James Williamson]

Hi there,

I have a spreadsheet that has my call centre's master stats
on one worksheet. There are also seperate worksheets for
each employee.

I need to link all the data from the master spreadsheet so
it autopopulates into each employees sheet.

The sheet goes by day across (with different stats listed
under each day). By row there is one lising for each employee.

This is then repeated 12 times. There are 12 groupings of
rows for each employee (one for every month).

Now I can choose one employee and make all the links to the
master worksheet and then copy that to all the other
employee worksheets. The problem is that each and every
worksheet I have to go in 12 times and say replace the row
number for each month from the original employee to what it
should be for each new employee. This is VERY time consuming.

Since each employee's row number for each month is just off
from the employee I chose as a template by a certain number
of rows. I would be much easier to be able to do "replace
all row numbers in the formulas in new employee's worksheet
with what they currently are plus 5 (for example), since
this employee is 5 rows down from the template employee in
each of the months.

Example: The template employees row numbers are (70. 142,
214, 286, 358, 430, 502, 574, 646, 718, 790, 862).

The next employee down all their row numbers are plus 1 of
what I listed above. Currently I'm going and doing replace
70 with 71, replace 142 with 143, etc...when I'd like to do
is say replace exisiting row number with row number + 1.

Is there any way of doing this?

Thanks!

James Williamson

 

[Max]

Assume your structure

In sheet: Master
is as below
---------------------

.....1-Jan..2-Jan..3-Jan..4-Jan
Stat1
Emp1
Emp2
Emp3
Emp4
Stat2
Emp1
Emp2
Emp3
Emp4
(Structure repeats down)

where the statistics data for employees Emp1, Emp2, etc
are in ranges B3:E6, B8:E11, and so on

------------------
In sheet: Emp1
-------------------
[ Row1 assumed to contain labels identical to that in sheet: Master ]

Put in A2:
=INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-7)

Copy A2 across to E2, then down to E3

The above pulls data for Emp1 from A3:E3 and A8:E8 in sheet: Master
and drops it into A2:E3 in sheet: Emp1


Repeat in a similar manner for the other employee sheets
(with just a slight change in the corner cell A2's formula)

For example

In sheet: Emp2
------------------
Put in A2:
=INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-6)

Copy A2 across to E2, then down to E3

(the change is in the last number to be subtracted from ROW()
viz. "6" instead of "7")

Similarly for sheets Emp3 & Emp4

In sheet: Emp3
Put in A2: =INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-5)

In sheet: Emp4
Put in A2: =INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-4)

Experiment the above and adapt to suit
---------------------------------------------------

Note: The above will work for up to 26 cols across (cols A - Z)

If it exceeds, you need to change the formula for the each next series of 26
cols

Example: For the next 26 cols: AA - AZ

In sheet: Emp1

Put in AA2:
=INDIRECT("Master!A"&CHAR(COLUMN()+70)&5*ROW()-7)

Then you can copy across (up to AZ2) and down, as before

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

a. The letter in the phrase e.g.: "Master!A" which has to correspond
to the first letter in the starting cell, i.e. "A" in "AA2" in this case

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

So for AA2, as col AA = col # 27
(i.e. : =COLUMN() in say, AA2 returns 27),
hence 70 needs to be added to .. COLUMN().. to equal 97

 

[James Williamson]

Thanks for the reply.

I've tried playing with this formuls, though I'm not sure I
truely understand how it works.

My data is more like

Master worksheet:
......1-Jan..................2-Jan..................
......Stat1...Stat2...Stat3..Stat1..Stat2...Stat3...
Emp1
Emp2
Emp3
Emp4
......1-Feb..................2-Feb..................
......Stat1...Stat2...Stat3..Stat1..Stat2...Stat3...
Emp1
Emp2
Emp3
Emp4
(and so on for each month)

Employee worksheet:
.........Stat1..Stat2..Stat3
01-Jan
02-Jan
03-Jan
04-Jan
05-Jan
AVERAGE Stat1..Stat2..Stat3
(and so on for each week of the month and the on to the
next month in the same pattern).

Can you use this formula you gave me to do this or is there
another better method? The problem seems to be that each
day continues across in the master but down in the employee
worksheets and I can't get it to pull the correct data.

Thanks again.

-----Original Message-----
Assume your structure

In sheet: Master
is as below
---------------------

.....1-Jan..2-Jan..3-Jan..4-Jan
Stat1
Emp1
Emp2
Emp3
Emp4
Stat2
Emp1
Emp2
Emp3
Emp4
(Structure repeats down)

where the statistics data for employees Emp1, Emp2, etc
are in ranges B3:E6, B8:E11, and so on

------------------
In sheet: Emp1
-------------------
[ Row1 assumed to contain labels identical to that in sheet: Master ]

Put in A2:
=INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-7)

Copy A2 across to E2, then down to E3

The above pulls data for Emp1 from A3:E3 and A8:E8 in sheet: Master
and drops it into A2:E3 in sheet: Emp1


Repeat in a similar manner for the other employee sheets
(with just a slight change in the corner cell A2's formula)

For example

In sheet: Emp2
------------------
Put in A2:
=INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-6)

Copy A2 across to E2, then down to E3

(the change is in the last number to be subtracted from ROW()
viz. "6" instead of "7")

Similarly for sheets Emp3 & Emp4

In sheet: Emp3
Put in A2: =INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-5)

In sheet: Emp4
Put in A2: =INDIRECT("Master!"&CHAR(COLUMN()+96)&5*ROW()-4)

Experiment the above and adapt to suit
---------------------------------------------------

Note: The above will work for up to 26 cols across (cols A - Z)

If it exceeds, you need to change the formula for the each next series of 26
cols

Example: For the next 26 cols: AA - AZ

In sheet: Emp1

Put in AA2:
=INDIRECT("Master!A"&CHAR(COLUMN()+70)&5*ROW()-7)

Then you can copy across (up to AZ2) and down, as before

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

a. The letter in the phrase e.g.: "Master!A" which has to correspond
to the first letter in the starting cell, i.e. "A" in "AA2" in this case

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

So for AA2, as col AA = col # 27
(i.e. : =COLUMN() in say, AA2 returns 27),
hence 70 needs to be added to .. COLUMN().. to equal 97

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------

Hi there,

I have a spreadsheet that has my call centre's master stats
on one worksheet. There are also seperate worksheets for
each employee.

I need to link all the data from the master spreadsheet so
it autopopulates into each employees sheet.

The sheet goes by day across (with different stats listed
under each day). By row there is one lising for each employee.

This is then repeated 12 times. There are 12 groupings of
rows for each employee (one for every month).

Now I can choose one employee and make all the links to the
master worksheet and then copy that to all the other
employee worksheets. The problem is that each and every
worksheet I have to go in 12 times and say replace the row
number for each month from the original employee to what it
should be for each new employee. This is VERY time consuming.

Since each employee's row number for each month is just off
from the employee I chose as a template by a certain number
of rows. I would be much easier to be able to do "replace
all row numbers in the formulas in new employee's worksheet
with what they currently are plus 5 (for example), since
this employee is 5 rows down from the template employee in
each of the months.

Example: The template employees row numbers are (70. 142,
214, 286, 358, 430, 502, 574, 646, 718, 790, 862).

The next employee down all their row numbers are plus 1 of
what I listed above. Currently I'm going and doing replace
70 with 71, replace 142 with 143, etc...when I'd like to do
is say replace exisiting row number with row number + 1.

Is there any way of doing this?

Thanks!

James Williamson

.

 

[Max]

Hi, here's what I cooked up, hopefully one solution
you can adapt to meet your needs. It's a little klunky,
I admit, and there could possibly be far better alternatives
out there. Anyway, do hope it helps to ease you-in ...

I'll be sending you a sample file via private email
with all the formulae done up for a full year
in a manner quite consistent with your data structure indicated.

(Anyone else interested in a copy, just drop me an email
at my address given in the sign-off below)

The only adjustment I made was to shift your weekly average rows
to be after each month, so as to make it simpler for us to copy the
formulae down from the first row for each month.

Here's some description of the core set-up steps:
-----------------------------------------------------------
(suggested formulae now uses OFFSET, instead of INDIRECT)

The formula set-up is done completely for say, Emp1's sheet first.

Then we can simply duplicate the "template" sheet for the other 3 Emp's.

After that, we just need to enter a number in cell A1 of the sheet:

"1" if sheet is for Emp1
"2" if sheet is for Emp2
"3" if sheet is for Emp3
"4" if sheet is for Emp4

The number in A1 will be used in the OFFSET formula
to extract the correct Emp's rows from sheet: Master

(no need for any tedious amendments!)

Creating the "template" sheet (Emp1)
---------------------------------------------
In sheet: Emp1

Put in A1: 1

In A2:A32 are the labels for each day of the month of Jan, viz.:
1-Jan, 2-Jan . 31-Jan

Put in B2: =OFFSET(Master!$A$1,$A$1+COLUMN()-1,3*ROW()-5)

Copy B2 across to D2, then amend the 2nd & 3rd params
(i.e. the row & column params) in the OFFSET formula
in both C2 and D2 so that it reads as:

In C2: =OFFSET(Master!$A$1,$A$1+COLUMN()-2,3*ROW()-4)
In D2: =OFFSET(Master!$A$1,$A$1+COLUMN()-3,3*ROW()-3)

The amendments are:

a. Subtract "1" from the row param of the copied formula in C2

Row param (2nd param):
"$A$1+COLUMN()-1" amended to "$A$1+COLUMN()-2"

b. Add "1" to the col param of the copied formula in C2

Col param (3rd param):
"3*ROW()-5" amended to "3*ROW()-4"

The same steps are done for the copied formula in D2
except that we subtract "2" and add "2" instead of "1"

The above will be typical of the pattern across the formulas
in cols B to D for each month

Select B22, copy down till the last day of the month in row32

The formula will return the "Jan" data from sheet: Master
in the format required

Repeat similar steps to construct for the other 11 months of the year,
using the following key formulae in the first / starting row for each month:

Feb
----
In B40: =OFFSET(Master!$A$1,$A$1+COLUMN()+5,3*ROW()-119)
In C40: =OFFSET(Master!$A$1,$A$1+COLUMN()+4,3*ROW()-118)
In D40: =OFFSET(Master!$A$1,$A$1+COLUMN()+3,3*ROW()-117)

Mar
----
In B80: =OFFSET(Master!$A$1,$A$1+COLUMN()+11,3*ROW()-239)
In C80: =OFFSET(Master!$A$1,$A$1+COLUMN()+10,3*ROW()-238)
In D80: =OFFSET(Master!$A$1,$A$1+COLUMN()+9,3*ROW()-237)

Apr
----
In B120: =OFFSET(Master!$A$1,$A$1+COLUMN()+17,3*ROW()-359)
In C120: =OFFSET(Master!$A$1,$A$1+COLUMN()+16,3*ROW()-358)
In D120: =OFFSET(Master!$A$1,$A$1+COLUMN()+15,3*ROW()-357)

May
----
In B160: =OFFSET(Master!$A$1,$A$1+COLUMN()+23,3*ROW()-479)
In C160: =OFFSET(Master!$A$1,$A$1+COLUMN()+22,3*ROW()-478)
In D160: =OFFSET(Master!$A$1,$A$1+COLUMN()+21,3*ROW()-477)

Jun
----
In B200: =OFFSET(Master!$A$1,$A$1+COLUMN()+29,3*ROW()-599)
In C200: =OFFSET(Master!$A$1,$A$1+COLUMN()+28,3*ROW()-598)
In D200: =OFFSET(Master!$A$1,$A$1+COLUMN()+27,3*ROW()-597)

Jul
----
In B240: =OFFSET(Master!$A$1,$A$1+COLUMN()+35,3*ROW()-719)
In C240: =OFFSET(Master!$A$1,$A$1+COLUMN()+34,3*ROW()-718)
In D240: =OFFSET(Master!$A$1,$A$1+COLUMN()+33,3*ROW()-717)

Aug
----
In B280: =OFFSET(Master!$A$1,$A$1+COLUMN()+41,3*ROW()-839)
In C280: =OFFSET(Master!$A$1,$A$1+COLUMN()+40,3*ROW()-838)
In D280: =OFFSET(Master!$A$1,$A$1+COLUMN()+39,3*ROW()-837)

Sep
----
In B320: =OFFSET(Master!$A$1,$A$1+COLUMN()+47,3*ROW()-959)
In C320: =OFFSET(Master!$A$1,$A$1+COLUMN()+46,3*ROW()-958)
In D320: =OFFSET(Master!$A$1,$A$1+COLUMN()+45,3*ROW()-957)

Oct
----
In B360: =OFFSET(Master!$A$1,$A$1+COLUMN()+53,3*ROW()-1079)
In C360: =OFFSET(Master!$A$1,$A$1+COLUMN()+52,3*ROW()-1078)
In D360: =OFFSET(Master!$A$1,$A$1+COLUMN()+51,3*ROW()-1077)

Nov
----
In B400: =OFFSET(Master!$A$1,$A$1+COLUMN()+59,3*ROW()-1199)
In C400: =OFFSET(Master!$A$1,$A$1+COLUMN()+58,3*ROW()-1198)
In D400: =OFFSET(Master!$A$1,$A$1+COLUMN()+57,3*ROW()-1197)

Dec
----
In B440: =OFFSET(Master!$A$1,$A$1+COLUMN()+65,3*ROW()-1319)
In C440: =OFFSET(Master!$A$1,$A$1+COLUMN()+64,3*ROW()-1318)
In D440: =OFFSET(Master!$A$1,$A$1+COLUMN()+63,3*ROW()-1317)

------------------------------------------------------------
Just some explanations on the formula set-up:

Row param in OFFSET()
-------------------------------
Since each Emp's data is at intervals of 6 rows per successive month
we need to increment the row param in OFFSET (the 2nd param)
by "6" for each successive month

For example: Emp1 data is in row3, row9, row15, etc

This explains why we have the row params, for e.g.:

In col B
-----------
Jan: $A$1+COLUMN()-1
Feb: $A$1+COLUMN()+5
Mar: $A$1+COLUMN()+11
Apr: $A$1+COLUMN()+17
May: $A$1+COLUMN()+23
Jun: $A$1+COLUMN()+29
Jul: $A$1+COLUMN()+35
Aug: $A$1+COLUMN()+41
Sep: $A$1+COLUMN()+47
Oct: $A$1+COLUMN()+53
Nov: $A$1+COLUMN()+59
Dec: $A$1+COLUMN()+65

Col param in OFFSET()
-----------------------------
The col param (3rd param) in OFFSET for each successive month
depends on which row we begin the month with.

As the data is in sets of 3 per day [viz. Stat1 - Stat3],
"3" is hence used as the multiplier for ROW(), i.e. "3*ROW()"
then we have to subtract a number from "3*ROW()"
to make it resolve to "1" in col B, "2" in col C and "3" in col D

To make it easier to mentally calculate this number to be subtracted
for the col param, that's why I chose the next round row# to use as
the start row for each successive month (e.g.: row40, row 80, row120 etc)

(You can use the empty rows in between months for your average formulas !)

For e.g.: the col params in OFFSET in col B

For Jan: 3*ROW()-5 in cell B2 >> resolves to "1"
(the "5" is what we must subtract from [3 x 2] to give "1")

For Feb: 3*ROW()-119 in cell B40 >> resolves to "1"
(the "119" is what we must subtract from [3 x 40] to give "1")

For Mar: 3*ROW()-239 in cell B80 >> resolves to "1"
(the "239" is what we must subtract from [3 x 80] to give "1")

 

[Max]

File sent, James !