Insert Line; Need formulas to adjust correctly

J

jmcgarbo1

New Member
Joined
Nov 17, 2016
Messages
3
Hi all, I have a spreadsheet(condensed version) that I need help with formulas changing...
Every month I insert a row for the new month between Nov & Cur Mo vs Ls Mo, the formula B9 changes from B7/B8 to B8/B7(same idea with the other formulas) but I need it to now be B9/B8(ie, Dec/Nov)... so I tried instead to insert between Oct & Nov and that solved the formula problem with YOY and CM vs Budget but Cur Mo vs Ls Mo still was incorrect...and changed to B9/B7 ... need it to be =B9/B8...Is there a way to get both sets of formula's to update correctly in one step or quickly??? I have a lot of lines and spreadsheets that require this action frequently. Thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 323"]
<tbody>[TR]
[TD]Cycle
[/TD]
[TD="align: right"]MONTH
[/TD]
[TD="align: center"]Formula in Cells
[/TD]
[/TR]
[TR]
[TD]Last FY
[/TD]
[TD="align: right"]$2,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Budget
[/TD]
[TD="align: right"]$2,601
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul
[/TD]
[TD="align: right"]$1,500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug
[/TD]
[TD="align: right"]$1,800
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sept
[/TD]
[TD="align: right"]$2,501
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD="align: right"]$2,300
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov
[/TD]
[TD="align: right"]$1,500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cur Mo vs Last Mo %
[/TD]
[TD="align: right"]65%
[/TD]
[TD="align: center"]B7/B6(Nov/Oct)
[/TD]
[/TR]
[TR]
[TD]Cur Mo vs Last Mo $
[/TD]
[TD="align: right"]$(800)
[/TD]
[TD="align: center"]B7-B6(Nov-Oct)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YOY% (M)
[/TD]
[TD="align: right"]75%
[/TD]
[TD="align: center"]B7/B3(PY/Nov)
[/TD]
[/TR]
[TR]
[TD]YOY $(M)
[/TD]
[TD="align: right"]$(500)
[/TD]
[TD="align: center"]B7-B3(Nov-LastFY)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CurMo vs Budget
[/TD]
[TD="align: right"]$(1101)
[/TD]
[TD]
B7-B4(Nov-Budget)​
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
jmcgarbo1 said:
Hi all, I have a spreadsheet(condensed version) that I need help with formulas changing...
Every month I insert a row for the new month between Nov & Cur Mo vs Ls Mo, the formula B9 changes from B7/B8 to B8/B7(same idea with the other formulas) but I need it to now be B9/B8(ie, Dec/Nov)... so I tried instead to insert between Oct & Nov and that solved the formula problem with YOY and CM vs Budget but Cur Mo vs Ls Mo still was incorrect...and changed to B9/B7 ... need it to be =B9/B8...Is there a way to get both sets of formula's to update correctly in one step or quickly??? I have a lot of lines and spreadsheets that require this action frequently. Thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 323"]
<tbody>[TR]
[TD]Cycle[/TD]
[TD="align: right"]MONTH[/TD]
[TD="align: center"]Formula in Cells[/TD]
[/TR]
[TR]
[TD]Last FY[/TD]
[TD="align: right"]$2,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD="align: right"]$2,601[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]$1,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]$1,800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sept[/TD]
[TD="align: right"]$2,501[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]$2,300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]$1,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cur Mo vs Last Mo %[/TD]
[TD="align: right"]65%[/TD]
[TD="align: center"]B7/B6(Nov/Oct)[/TD]
[/TR]
[TR]
[TD]Cur Mo vs Last Mo $[/TD]
[TD="align: right"]$(800)[/TD]
[TD="align: center"]B7-B6(Nov-Oct)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YOY% (M)[/TD]
[TD="align: right"]75%[/TD]
[TD="align: center"]B7/B3(PY/Nov)[/TD]
[/TR]
[TR]
[TD]YOY $(M)[/TD]
[TD="align: right"]$(500)[/TD]
[TD="align: center"]B7-B3(Nov-LastFY)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CurMo vs Budget[/TD]
[TD="align: right"]$(1101)[/TD]
[TD]
B7-B4(Nov-Budget)​
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

can you put in the next 24 months now with the correct formulas in place so that when cells go from blank to containing data the formulas spring into action ?
 
Upvote 0
Not really .. the data is tied to charts as well so I would then have to update the data source for the charts as well... that can be even uglier :)
 
Upvote 0
jmcgarbo1 said:
Not really .. the data is tied to charts as well so I would then have to update the data source for the charts as well... that can be even uglier :)
Click to expand...

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Jul[/TD]
[TD="width: 64, align: right"]1500[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]1800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sept[/TD]
[TD="align: right"]2501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]2300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="class: xl22, align: right"]1700.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]curr/last%[/TD]
[TD="class: xl22, align: right"]1.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]curr/last$[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]before adding Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD="align: right"]1800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sept[/TD]
[TD="align: right"]2501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]2300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]curr/last%[/TD]
[TD="class: xl22, align: right"]0.65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]curr/last$[/TD]
[TD="align: right"]-800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I merely copied last 2 rows from 6 and 7 to 7 and 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then overtyped the Dec figure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Formula mod to include AND in Logic
Replies
1
Views
387
SanjayGMusafir
SanjayGMusafir
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
486
kvsrinivasamurthy
K
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
612
Eric W
Eric W
V
  • Solved
Adjust dividing formula when a value is zero
Replies
4
Views
458
vladimiratanasiu
V
V
  • Solved
Formula to calculate automatically progressive average of monthly expenses
Replies
6
Views
651
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top