Pasting constant formula where referral sheet changes
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? |
Pasting constant formula where referral sheet changes
You can try this:
=INDIRECT("'FP "&ROW(A1)&"'!I25") And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nicole M." <Nicole wrote in message ... 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? |
Pasting constant formula where referral sheet changes
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. "Ragdyer" wrote: You can try this: =INDIRECT("'FP "&ROW(A1)&"'!I25") And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nicole M." <Nicole wrote in message ... 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? |
Pasting constant formula where referral sheet changes
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) "Nicole M." wrote in message ... 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. "Ragdyer" wrote: You can try this: =INDIRECT("'FP "&ROW(A1)&"'!I25") And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nicole M." <Nicole wrote in message ... 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? |
Pasting constant formula where referral sheet changes
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$57 |
Pasting constant formula where referral sheet changes
Did the cell you entered the formula into return the contents of cell I25 on
Sheet "FP 1"? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nicole M." wrote in message ... 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. "Ragdyer" wrote: You can try this: =INDIRECT("'FP "&ROW(A1)&"'!I25") And copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Nicole M." <Nicole wrote in message ... 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? |
Pasting constant formula where referral sheet changes
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. "Peo Sjoblom" wrote: 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) "Nicole M." wrote in message ... 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. "Ragdyer" wrote: You can try this: =INDIRECT("'FP "&ROW(A1)&"'!I25") And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Nicole M." <Nicole wrote in message ... 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? |
Pasting constant formula where referral sheet changes
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....' (-: "Ragdyer" wrote: Did the cell you entered the formula into return the contents of cell I25 on Sheet "FP 1"? -- Regards, RD |
Pasting constant formula where referral sheet changes
Try this:
=INDEX(INDIRECT("'FP "&INT(ROWS($1:4)/4)&"'!I:I"),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 ! --------------------------------------------------------------------------- "Nicole M." wrote in message ... 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$57 |
Pasting constant formula where referral sheet changes
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 ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Try this: =INDEX(INDIRECT("'FP "&INT(ROWS($1:4)/4)&"'!I:I"),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 ! -------------------------------------------------------------------------- - "Nicole M." wrote in message ... 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$57 |
Pasting constant formula where referral sheet changes
Fab! I will try this after I finish up with fixing our traditional New
Year's Day meal here in Texas of Black Eyed Peas and Corn Bread! Just to be a pain, I would like to know not just the formula that will work but why it will work so I can replicate the efforts again another time without referring to the original workbook that got me started in that direction to begin with. Would you mind tellng me in more or less plain english (presume I am computer savvy, just not techie schooled) what "sentence" the formula is composing? Thanks so much for the assist. I get so frustrated with myself when I can't figure things out on my own that I just know will seem like a kead knocker once I see the light (one of those things you hit yourself up side the head for and say 'duh' about). Nicole Happy New Year, by the way! "Ragdyer" wrote: Try this: =INDEX(INDIRECT("'FP "&INT(ROWS($1:4)/4)&"'!I:I"),17*(MOD(ROWS($1:1)-1,4)+1)-9) And copy down. -- HTH, RD |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com