Forum Discussion
Can formulas be made to change dynamically when copying sheets?
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.
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.
I've got a workaround, Mathetes! 🙂
I found out how to allow a helper column to show without printing and it'll work great for this.
I'm adding a helper column to the left of the first column on the 347Form-1 (it's the employee name column) and the user will select a coded name from a dropdown list in that column and the not-coded name will appear in the name field.
This will resolve a couple issues PLUS the user can copy the sheet as many times as he needs and the formulas will copy over as they should. 🙂
I'd still love for my hope above to work because it'll allow me to create an entire helper worksheet (instead of just a column) and I can set up that helper worksheet to work like a spreadsheet should. 🙂 But if I can't make the formulas change dynamically when a sheet is copied within the 347Form-1 worksheet, I'll have to figure out a different way to make it work.
Thank you, Mathetes!! 🙂
This is kind of fun. I need to warn you that INDIRECT is what's called a volatile function. But this does look like it works with multiple columns and added sheets.
