software

I am creating a data tracking workbook. Information is entered into cells on
different sheets, then the tallies are pulled to one easier to read sheet.

In creating the tally sheet, I am setting the cell formulas. The cells
referred to are constant, only the sheet referred to changes. For example,
the cell might read

='FP 1'!$I$25

and the next one should read

='FP 2'!$I$25

....and so on. I would like to be able to paste the formula and have the
referred to sheet change so that I do not have to go through and make
hundreds of little changes. Any suggestions?

You can try this:

=INDIRECT(quot;'FP quot;amp;ROW(A1)amp;quot;'!I25quot;)

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Nicole M.quot; lt;Nicole gt; wrote in message
...
gt; I am creating a data tracking workbook. Information is entered into cells
on
gt; different sheets, then the tallies are pulled to one easier to read sheet.
gt;
gt; In creating the tally sheet, I am setting the cell formulas. The cells
gt; referred to are constant, only the sheet referred to changes. For example,
gt; the cell might read
gt;
gt; ='FP 1'!$I$25
gt;
gt; and the next one should read
gt;
gt; ='FP 2'!$I$25
gt;
gt; ...and so on. I would like to be able to paste the formula and have the
gt; referred to sheet change so that I do not have to go through and make
gt; hundreds of little changes. Any suggestions?This appeared to have no effect. It did not alter the sheet referred to in
the formula, just copied exactly as it was.

In my formula, FP 1 (etc) is the sheet referred to, whereas the row and
column are constant. To make it more fun, since I am dealing in fiscal
periods the formula series repeats every fourth cell only, but with a new
sheet referral. I had taken the time to do this the tedious (and highly
inefficient) way about a year ago, then was an idiot and did not protect the
formula cells thinking that the person using the workbook would have sense
enough to not type into those cells. Naive, naive, naive. Live and learn.

quot;Ragdyerquot; wrote:

gt; You can try this:
gt;
gt; =INDIRECT(quot;'FP quot;amp;ROW(A1)amp;quot;'!I25quot;)
gt;
gt; And copy down as needed.
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Nicole M.quot; lt;Nicole gt; wrote in message
gt; ...
gt; gt; I am creating a data tracking workbook. Information is entered into cells
gt; on
gt; gt; different sheets, then the tallies are pulled to one easier to read sheet.
gt; gt;
gt; gt; In creating the tally sheet, I am setting the cell formulas. The cells
gt; gt; referred to are constant, only the sheet referred to changes. For example,
gt; gt; the cell might read
gt; gt;
gt; gt; ='FP 1'!$I$25
gt; gt;
gt; gt; and the next one should read
gt; gt;
gt; gt; ='FP 2'!$I$25
gt; gt;
gt; gt; ...and so on. I would like to be able to paste the formula and have the
gt; gt; referred to sheet change so that I do not have to go through and make
gt; gt; hundreds of little changes. Any suggestions?
gt;
gt;

So what result do you get?
RD's formula will increment the sheet numbers by one for each row it is
copied down but
the cell will stay the same

--
Regards,

Peo Sjoblom

(No private emails please)quot;Nicole M.quot; gt; wrote in message
...
gt; This appeared to have no effect. It did not alter the sheet referred to
gt; in
gt; the formula, just copied exactly as it was.
gt;
gt; In my formula, FP 1 (etc) is the sheet referred to, whereas the row and
gt; column are constant. To make it more fun, since I am dealing in fiscal
gt; periods the formula series repeats every fourth cell only, but with a new
gt; sheet referral. I had taken the time to do this the tedious (and highly
gt; inefficient) way about a year ago, then was an idiot and did not protect
gt; the
gt; formula cells thinking that the person using the workbook would have sense
gt; enough to not type into those cells. Naive, naive, naive. Live and
gt; learn.
gt;
gt; quot;Ragdyerquot; wrote:
gt;
gt;gt; You can try this:
gt;gt;
gt;gt; =INDIRECT(quot;'FP quot;amp;ROW(A1)amp;quot;'!I25quot;)
gt;gt;
gt;gt; And copy down as needed.
gt;gt; --
gt;gt; HTH,
gt;gt;
gt;gt; RD
gt;gt;
gt;gt; ---------------------------------------------------------------------------
gt;gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt;gt; ---------------------------------------------------------------------------
gt;gt; quot;Nicole M.quot; lt;Nicole gt; wrote in message
gt;gt; ...
gt;gt; gt; I am creating a data tracking workbook. Information is entered into
gt;gt; gt; cells
gt;gt; on
gt;gt; gt; different sheets, then the tallies are pulled to one easier to read
gt;gt; gt; sheet.
gt;gt; gt;
gt;gt; gt; In creating the tally sheet, I am setting the cell formulas. The cells
gt;gt; gt; referred to are constant, only the sheet referred to changes. For
gt;gt; gt; example,
gt;gt; gt; the cell might read
gt;gt; gt;
gt;gt; gt; ='FP 1'!$I$25
gt;gt; gt;
gt;gt; gt; and the next one should read
gt;gt; gt;
gt;gt; gt; ='FP 2'!$I$25
gt;gt; gt;
gt;gt; gt; ...and so on. I would like to be able to paste the formula and have
gt;gt; gt; the
gt;gt; gt; referred to sheet change so that I do not have to go through and make
gt;gt; gt; hundreds of little changes. Any suggestions?
gt;gt;
gt;gt;Just to see if I can make it more clea what I am trying to achieve, this is
an example of a group of cells in the series:

='FP 1'!$I$8='FP 1'!$I$9='FP 1'!$I$11='FP 1'!$I$6
='FP 1'!$I$25='FP 1'!$I$26='FP 1'!$I$28='FP 1'!$I$23
='FP 1'!$I$42='FP 1'!$I$43='FP 1'!$I$45='FP 1'!$I$40
='FP 1'!$I$59='FP 1'!$I$60='FP 1'!$I$62='FP 1'!$I$57
='FP 2'!$I$8='FP 2'!$I$9='FP 2'!$I$11='FP 2'!$I$6
='FP 2'!$I$25='FP 2'!$I$26='FP 2'!$I$28='FP 2'!$I$23
='FP 2'!$I$42='FP 2'!$I$43='FP 2'!$I$45='FP 2'!$I$40
='FP 2'!$I$59='FP 2'!$I$60='FP 2'!$I$62='FP 2'!$I$57
='FP 3'!$I$8='FP 3'!$I$9='FP 3'!$I$11='FP 3'!$I$6
='FP 3'!$I$25='FP 3'!$I$26='FP 3'!$I$28='FP 3'!$I$23
='FP 3'!$I$42='FP 3'!$I$43='FP 3'!$I$45='FP 3'!$I$40
='FP 3'!$I$59='FP 3'!$I$60='FP 3'!$I$62='FP 3'!$I$57Did the cell you entered the formula into return the contents of cell I25 on
Sheet quot;FP 1quot;?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Nicole M.quot; gt; wrote in message
...
gt; This appeared to have no effect. It did not alter the sheet referred to
in
gt; the formula, just copied exactly as it was.
gt;
gt; In my formula, FP 1 (etc) is the sheet referred to, whereas the row and
gt; column are constant. To make it more fun, since I am dealing in fiscal
gt; periods the formula series repeats every fourth cell only, but with a new
gt; sheet referral. I had taken the time to do this the tedious (and highly
gt; inefficient) way about a year ago, then was an idiot and did not protect
the
gt; formula cells thinking that the person using the workbook would have sense
gt; enough to not type into those cells. Naive, naive, naive. Live and
learn.
gt;
gt; quot;Ragdyerquot; wrote:
gt;
gt; gt; You can try this:
gt; gt;
gt; gt; =INDIRECT(quot;'FP quot;amp;ROW(A1)amp;quot;'!I25quot;)
gt; gt;
gt; gt; And copy down as needed.
gt; gt; --
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; quot;Nicole M.quot; lt;Nicole gt; wrote in message
gt; gt; ...
gt; gt; gt; I am creating a data tracking workbook. Information is entered into
cells
gt; gt; on
gt; gt; gt; different sheets, then the tallies are pulled to one easier to read
sheet.
gt; gt; gt;
gt; gt; gt; In creating the tally sheet, I am setting the cell formulas. The
cells
gt; gt; gt; referred to are constant, only the sheet referred to changes. For
example,
gt; gt; gt; the cell might read
gt; gt; gt;
gt; gt; gt; ='FP 1'!$I$25
gt; gt; gt;
gt; gt; gt; and the next one should read
gt; gt; gt;
gt; gt; gt; ='FP 2'!$I$25
gt; gt; gt;
gt; gt; gt; ...and so on. I would like to be able to paste the formula and have
the
gt; gt; gt; referred to sheet change so that I do not have to go through and make
gt; gt; gt; hundreds of little changes. Any suggestions?
gt; gt;
gt; gt;Alas, the formula copied and pasted exactly as written. Perhaps the problem
lies in that the sheet only needs to change every 4th cell, as opposed to
each next cell. All I know for sure is that this is a pain in my
hindquarters and I dread doing all of the formula edits, though I have
alerady started in the interest of just getting it done.

quot;Peo Sjoblomquot; wrote:

gt; So what result do you get?
gt; RD's formula will increment the sheet numbers by one for each row it is
gt; copied down but
gt; the cell will stay the same
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;Nicole M.quot; gt; wrote in message
gt; ...
gt; gt; This appeared to have no effect. It did not alter the sheet referred to
gt; gt; in
gt; gt; the formula, just copied exactly as it was.
gt; gt;
gt; gt; In my formula, FP 1 (etc) is the sheet referred to, whereas the row and
gt; gt; column are constant. To make it more fun, since I am dealing in fiscal
gt; gt; periods the formula series repeats every fourth cell only, but with a new
gt; gt; sheet referral. I had taken the time to do this the tedious (and highly
gt; gt; inefficient) way about a year ago, then was an idiot and did not protect
gt; gt; the
gt; gt; formula cells thinking that the person using the workbook would have sense
gt; gt; enough to not type into those cells. Naive, naive, naive. Live and
gt; gt; learn.
gt; gt;
gt; gt; quot;Ragdyerquot; wrote:
gt; gt;
gt; gt;gt; You can try this:
gt; gt;gt;
gt; gt;gt; =INDIRECT(quot;'FP quot;amp;ROW(A1)amp;quot;'!I25quot;)
gt; gt;gt;
gt; gt;gt; And copy down as needed.
gt; gt;gt; --
gt; gt;gt; HTH,
gt; gt;gt;
gt; gt;gt; RD
gt; gt;gt;
gt; gt;gt; ---------------------------------------------------------------------------
gt; gt;gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; gt;gt; ---------------------------------------------------------------------------
gt; gt;gt; quot;Nicole M.quot; lt;Nicole gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; I am creating a data tracking workbook. Information is entered into
gt; gt;gt; gt; cells
gt; gt;gt; on
gt; gt;gt; gt; different sheets, then the tallies are pulled to one easier to read
gt; gt;gt; gt; sheet.
gt; gt;gt; gt;
gt; gt;gt; gt; In creating the tally sheet, I am setting the cell formulas. The cells
gt; gt;gt; gt; referred to are constant, only the sheet referred to changes. For
gt; gt;gt; gt; example,
gt; gt;gt; gt; the cell might read
gt; gt;gt; gt;
gt; gt;gt; gt; ='FP 1'!$I$25
gt; gt;gt; gt;
gt; gt;gt; gt; and the next one should read
gt; gt;gt; gt;
gt; gt;gt; gt; ='FP 2'!$I$25
gt; gt;gt; gt;
gt; gt;gt; gt; ...and so on. I would like to be able to paste the formula and have
gt; gt;gt; gt; the
gt; gt;gt; gt; referred to sheet change so that I do not have to go through and make
gt; gt;gt; gt; hundreds of little changes. Any suggestions?
gt; gt;gt;
gt; gt;gt;
gt;
gt;

Yes, no problem with in the formula itself, just in the implementation, or
rather the complexity of the implementation. It's a nuisance, but I know
that it can be a simpler nuisance. I just haven't figured out exactly how.
I have been a better person though and password protected all formulas in
this workbook, did it as I hit my head against the wall saying 'stupid,
stupid, stupid....' (-:
quot;Ragdyerquot; wrote:

gt; Did the cell you entered the formula into return the contents of cell I25 on
gt; Sheet quot;FP 1quot;?
gt; --
gt; Regards,
gt;
gt; RDTry this:

=INDEX(INDIRECT(quot;'FP
quot;amp;INT(ROWS($1:4)/4)amp;quot;'!I:Iquot;),17*(MOD(ROWS($1:1)-1,4) 1)-9)

And copy down.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Nicole M.quot; gt; wrote in message
...
gt; Just to see if I can make it more clea what I am trying to achieve, this
is
gt; an example of a group of cells in the series:
gt;
gt; ='FP 1'!$I$8 ='FP 1'!$I$9 ='FP 1'!$I$11 ='FP 1'!$I$6
gt; ='FP 1'!$I$25 ='FP 1'!$I$26 ='FP 1'!$I$28 ='FP 1'!$I$23
gt; ='FP 1'!$I$42 ='FP 1'!$I$43 ='FP 1'!$I$45 ='FP 1'!$I$40
gt; ='FP 1'!$I$59 ='FP 1'!$I$60 ='FP 1'!$I$62 ='FP 1'!$I$57
gt; ='FP 2'!$I$8 ='FP 2'!$I$9 ='FP 2'!$I$11 ='FP 2'!$I$6
gt; ='FP 2'!$I$25 ='FP 2'!$I$26 ='FP 2'!$I$28 ='FP 2'!$I$23
gt; ='FP 2'!$I$42 ='FP 2'!$I$43 ='FP 2'!$I$45 ='FP 2'!$I$40
gt; ='FP 2'!$I$59 ='FP 2'!$I$60 ='FP 2'!$I$62 ='FP 2'!$I$57
gt; ='FP 3'!$I$8 ='FP 3'!$I$9 ='FP 3'!$I$11 ='FP 3'!$I$6
gt; ='FP 3'!$I$25 ='FP 3'!$I$26 ='FP 3'!$I$28 ='FP 3'!$I$23
gt; ='FP 3'!$I$42 ='FP 3'!$I$43 ='FP 3'!$I$45 ='FP 3'!$I$40
gt; ='FP 3'!$I$59 ='FP 3'!$I$60 ='FP 3'!$I$62 ='FP 3'!$I$57
gt;Just in case it might look a little confusing, the first row number to be
returned is:

17 minus the last number.

So, your first column starts with 8,
17-9 = 8

For your second column to start with 9,
Change the -9 to -8
3rd column -6
4th column -11
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Ragdyerquot; gt; wrote in message
...
gt; Try this:
gt;
gt; =INDEX(INDIRECT(quot;'FP
gt; quot;amp;INT(ROWS($1:4)/4)amp;quot;'!I:Iquot;),17*(MOD(ROWS($1:1)-1,4) 1)-9)
gt;
gt; And copy down.
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; --------------------------------------------------------------------------
-
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; --------------------------------------------------------------------------
-
gt; quot;Nicole M.quot; gt; wrote in message
gt; ...
gt; gt; Just to see if I can make it more clea what I am trying to achieve, this
gt; is
gt; gt; an example of a group of cells in the series:
gt; gt;
gt; gt; ='FP 1'!$I$8 ='FP 1'!$I$9 ='FP 1'!$I$11 ='FP 1'!$I$6
gt; gt; ='FP 1'!$I$25 ='FP 1'!$I$26 ='FP 1'!$I$28 ='FP 1'!$I$23
gt; gt; ='FP 1'!$I$42 ='FP 1'!$I$43 ='FP 1'!$I$45 ='FP 1'!$I$40
gt; gt; ='FP 1'!$I$59 ='FP 1'!$I$60 ='FP 1'!$I$62 ='FP 1'!$I$57
gt; gt; ='FP 2'!$I$8 ='FP 2'!$I$9 ='FP 2'!$I$11 ='FP 2'!$I$6
gt; gt; ='FP 2'!$I$25 ='FP 2'!$I$26 ='FP 2'!$I$28 ='FP 2'!$I$23
gt; gt; ='FP 2'!$I$42 ='FP 2'!$I$43 ='FP 2'!$I$45 ='FP 2'!$I$40
gt; gt; ='FP 2'!$I$59 ='FP 2'!$I$60 ='FP 2'!$I$62 ='FP 2'!$I$57
gt; gt; ='FP 3'!$I$8 ='FP 3'!$I$9 ='FP 3'!$I$11 ='FP 3'!$I$6
gt; gt; ='FP 3'!$I$25 ='FP 3'!$I$26 ='FP 3'!$I$28 ='FP 3'!$I$23
gt; gt; ='FP 3'!$I$42 ='FP 3'!$I$43 ='FP 3'!$I$45 ='FP 3'!$I$40
gt; gt; ='FP 3'!$I$59 ='FP 3'!$I$60 ='FP 3'!$I$62 ='FP 3'!$I$57
gt; gt;
gt;