Forum Discussion

DianeDennis's avatar
DianeDennis
Brass Contributor
Jul 31, 2023

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(...
excel
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor
Aug 01, 2023

DianeDennis 

 

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.

DianeDennis's avatar
DianeDennis
Brass Contributor
Aug 01, 2023

mathetes 

 

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

Resources