Reference Formula and cell reference

[ImAhNoBoDy]

I have a spreadsheet that is based on benching (weight lifting). The cells currently increase by 5lbs everyday. By day 4 the weight increases by 2.5lb everyday. In Day 5, I never copied the new formula from day 4 to the rest of the days ahead, so day 5 is increasing by 5lb and that's not what I want. It should be increased by 2.5lb everyday.

Is there a formula I could use that would reference the previous day and increase the weight appropriately without copying the formula everytime the weight increase is changed?



I wasn't sure if FORMULATEXT was appropriate and tried searching for a solution to this and couldn't come up with something. Any help would be appreciated. Thanks.

[p45cal]

in the attached,

• Adjust the green cells' values in row 4; this is the daily increase in weight (can be negative)
• Adjust how long that increase should apply for in the green cells in row 2
• Hide row 3
• Change starting weight in cell C7
• The formula in cell D7 is the one that can be copied across as far as you need.
• Row 13 is only there to demonstrate how the weight increments have been applied, can be deleted.

There's probably a slicker way.

[ImAhNoBoDy]

When it comes to weight training, I never know how long I will be using the same weight increase. Using my excel sheet as my example, my thinking was, I would have to manually change the cell reference when the weight increase changes.

C5 - 100
D5 - =SUM(C5+E$2$)
E5 - =FORMULATEXT(D5), get cell that references the weight increase (E$2$)
F5 - =SUM(E5+F$2$), weights are getting heavier, so I have to increase weight slower
G5 - =FORMULATEXT(F5), get cell that references the weight increase (F$2$)
H5 - =FORMULATEXT(F5), get cell that references the weight increase (F$2$)
I5 - =FORMULATEXT(F5), get cell that references the weight increase (F$2$)
J5 - =SUM(I5+F$2$), weights are getting heavier, so I have to increase weight slower

Obvious, I would need something a function to turn the text into an expression of some sort.

[p45cal]

Originally Posted by ImAhNoBoDy

I would have to manually change the cell reference when the weight increase changes.

In the files I attached, adjust the green cells in row 2, you will immediately see the effect in row 7. If you want no change in weight for a period, put a zero in the appropriate green cell in row 4 and the number of days you want to stay at that weight in the corresponding green cell above it in row 2.


2024-10-14_184802.jpg

[ImAhNoBoDy]

I needed up using something similar to this:

https://www.extendoffice.com/documen...al-values.html

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

The above is just a copy and paste from the site and not what I put in my spreadsheet. I'm putting it here in case I need reference it again.

I got the cell address, I just haven't put everything together. But I think I got what I needed. Thanks.

[ImAhNoBoDy]

So I've been at this for the last few days.

I've attached the same spreadsheet, but made some adjustments to the formula. I'm trying to make the formula as dynamic as possible.

So in cell E5, I was able to create an address array, but it's not searchable. In cell E6, I manually created the address array to see if the concept would work where a text matches one of the address arrays. It works, but I can't get a value from the searchable address when using the INDIRECT function. I think it's because the values spilled to the next cells.

Can anyone help with cell E5 and get the dynamic address array to be a valid array. I'm assuming the formula is evaluating it as a text and not as an array. Then in cell E6, can we get 1 result in the same cell and then use an INDIRECT function on it, so it can evaulate text to a value? Thanks.

In the end I'm trying to do a =SUM((previous workout weight)+(long formula that evaluates to a value dynamically))

Edit: Updated spreadsheet. I was able to figure out how to extract an address in E5:

=MID(FORMULATEXT(D5),SEARCH(TEXTSPLIT(TEXTJOIN(",",TRUE,ADDRESS(ROW(E2:I2),COLUMN(E2:I2))),","),FORMULATEXT(D5)),4)

[ImAhNoBoDy]

So the way I was thinking about things was incorrect. I think I was trying for something like this:

=SUM(D5+INDIRECT(ARRAYTOTEXT(FILTER(MID(FORMULATEXT(D5),SEARCH(TEXTSPLIT(TEXTJOIN(",",TRUE,ADDRESS(ROW(E2:I2),COLUMN(E2:I2))),","),FORMULATEXT(D5)),4),NOT(ISERROR(MID(FORMULATEXT(D5),SEARCH(TEXTSPLIT(TEXTJOIN(",",TRUE,ADDRESS(ROW(E2:I2),COLUMN(E2:I2))),","),FORMULATEXT(D5)),4)))))))

But the next column wouldn't be able to determine what the previous value was gonna be. I really didn't think this through logically. It would need to be script something in vba to get the results I really want.