Forum Discussion
Can formulas be made to change dynamically when copying sheets?
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.
Hi Mathetes!!
Thank you again for the formula revisions that you made! 🙂
I've uploaded a new copy here: https://docs.google.com/spreadsheets/d/1CCEb5VbZ-lHeku70xGZJYvz6B6aRZy-K/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true
With notes as to what it should be doing vs. what it is doing. It's close but it's not picking up quite the correct cells, it's like it's skipping forward one cell (you'll see on the uploaded worksheet). 🙂
I also eliminated columns C and D from the 347Helper worksheet (they were just kinda complicating things right now) and removed the reference to them from the formulas on the rest of the worksheets.
Oh, also, I saw the "volatile" that you mentioned. I was adding some rows and watched all the numbers change, so I changed it back. 🙂
I also wanted to show you where I'm at with the actual 347 that I'm working on. I've uploaded a copy here: https://docs.google.com/spreadsheets/d/1B-srlnN4FyNDFupa3JTN_1IrgRsk8O5Z/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true
I'm so excited with how it's coming out and I'm super excited to share it with you. 🙂 It would definitely not be this far along without your incredible help! I'd love to hear your thoughts on it. 🙂
Thank you again so much, Mathetes!! 🙂
