Forum Discussion
Can formulas be made to change dynamically when copying sheets?
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!! 🙂
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). 🙂
Fixed in the attached. Note: what is called sheet ....-5 is actually in sequence where it should be...-4, and the formula works on where it actually IS. i.e., the SHEET() function in that formula works sequentially from left to right regardless of what label is attached to the tab.
=INDIRECT("347Helper!"&CHOOSE(COLUMN(),"A","B")&TEXT(ROW()+((SHEET()-2)*5),"0"))So in that INDIRECT formula we're creating or constructing a cell reference by using the COLUMN() where the formula resides, the ROW() where the formula resides, and the SHEET().
- INDIRECT begins this construction project with "347Helper!" and then
- adds an "A" or "B" depending on which column the cell (i.e., the one containing the formula) is in. So, so far, we've constructed "347Helper!A"
- The TEXT(ROW()+((SHEET()-2)*5) computes the correct row and converts it to TEXT
- The computation of the row takes the form of
- ROW() --i.e. current row of the cell so in row 1, a value of 1
- plus
- SHEET()-2 (which, in the third sheet from the left, 347Form-2,
- 3-2 would give us the value of 1
- 1 multiplied by 5 = 5
- added to the 1 of current row, yields 6,
- The computation of the row takes the form of
- and the cell reference we've constructed ends as "347Helper!A6"
That construction project, dependent as it is on the cell--the column and the row--in which the formula resides, creates, in effect, a distinctive formula for each cell, and varies with each sheet as well.
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. 🙂
This probably won't adversely affect the performance on your workbook, but the presence of "volatile" functions--which I only learned fairly recently, is considered by many experts as something to be avoided if possible, minimized at the very least. This is because "volatile functions" use a lot of the computer's resources.
I also wanted to show you where I'm at with the actual 347 that I'm working on. I've uploaded a copy
Looks nice! Well on the way. I'm glad to have been a small part.
- Hi! I'm glad you're enjoying this! I am too! 🙂
I opened what you posted and I'm having a hard time making heads or tails of it. I've been up since about 12:30 am and I think my brain space is gone for the day, lol.
I'm going to check it out tomorrow and get back to you. Thank you SO MUCH for this, Mathetes!!
Have a great night! 🙂