Forum Discussion
Can formulas be made to change dynamically when copying sheets?
Hi!
I have a workbook with two worksheets, Sheet1 and Sheet2.
Sheet2 has 7 columns and 15 rows with a number in each cell.
Sheet1 A1 references A1 on Sheet2 with the following:
=SUM(Sheet2!A1)
Sheet1 A2 references A2 on Sheet2 with the following:
=SUM(Sheet2!A2)
And so on across and down A1 through G5.
The worksheets are protected, no password.
I'd like the user to be able to copy Sheet1 to create Sheet3 in the same workbook and have those references automatically change to reference the next 5 rows on Sheet2, which are A6 through G10.
For example, I'd like the formula in A1 on Sheet3 to automatically change, while the user is copying Sheet1 to become Sheet3, to be:
=SUM(Sheet2!A6)
and so on, much like when I Ctrl + D or Ctrl + R and the formulas change...
Is that possible?
Thank you so much!!
11 Replies
Unless I misunderstood your request somehow, Diane....I just did what you were wondering if you could do and Sheet 3 displays (in cell A6) exactly as you wanted it to.
With one exception: You are using the formula =SUM(Sheet2!A1) and its variants. The SUM in those cases is utterly unnecessary. You're not adding any two or more numbers, which is when you'd want to use SUM. You're just referring to a single cell's contents. So =Sheet2!A1 works.
See the attached.
Hi Mathetes! 🙂
Thank you for the correction on my formula. 🙂 I got side-tracked yesterday with thinking I needed $ in there and I knew that wasn't right, or I was pretty sure, but then I lost track of having used the SUM.
I didn't explain well enough what I need, I think.
I've uploaded a simple sample with notes that I'm hoping will help.
https://docs.google.com/spreadsheets/d/1mKd00BvTXm2dqApKi1VdCYU3RpqSwolY/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true
This is for that same 347 workbook that I've been working on and I have a new wrinkle. The uploaded sample isn't the 347 because I haven't created the next step - I need to find out if this is doable before I do that.
Anyhow, it's going to be often that the user has to create additional "347Form" worksheets to accommodate all of his employees (each sheet allows for only 7 employees).
My plan...
I'm looking at creating a "347Helper" worksheet (along the lines of the revision you created) that the "347Form-1" worksheet will reference (this is to help with the "Name -Classification" issue we chatted about before).
There are 7 employee name fields/cells (one per row) on the "347Form-1" and an unlimited number of rows for employee names on the "347Helper".
The cells A1, A2, A3, A4, A5, A6, and A7 on the "347Form-1" have the formulas =347Helper!A1 =347Helper!A2 =347Helper!A3 =347Helper!A4 =347Helper!A5 =347Helper!A6 =347Helper!A7 respectively.
When the user copies "347Form-1" to create "347Form-2" I'd like those formulas to automatically change on the new "347Form-2" to be =347Helper!A8 =347Helper!A9 =347Helper!A10 =347Helper!A11 =347Helper!A12 =347Helper!A13 =347Helper!A14 (and remain in A1 through A7).
Then when the user copies "347Form-1" (or "347Form-2") to create "347Form-3" I'd like those formulas to change on the new "347Form-3" to be =347Helper!A15 =347Helper!A16 =347Helper!A17 =347Helper!A18 =347Helper!A19 =347Helper!A20 =347Helper!A21 (and remain in A1 through A7).
And so on...
I hope that make sense. 🙂
Thank you so much, Mathetes!!
Diane
I saw almost immediately that I had misunderstood your request regarding Sheet3. But I don't see a way to to that with simple copy and paste.
Instead I came up with a formula that you could use (assuming you're wanting to do this for a series of sheets) that does require a separate cell to denote how many rows to "add" in sheets 3, 4, etc.
With cell $K$1 in sheet 3 containing the value 5, this formula, copied into cells A1:G5 of Sheet3 will retrieve the values in cells A6:G10 of Sheet2.
=INDIRECT("Sheet2!"&CHOOSE(COLUMN(),"A","B","C","D","E","F","G")&TEXT((ROW()+$K$1),"0"))See the attached
For my sake, as well as the sake of others who may be wondering: What is the bigger picture here? Why are you wanting to accomplish this somewhat "odd" kind of copying and pasting? What purpose is being served.
I ask that because there may be a more direct, more robust and reliable way to accomplish the same purposes.
