Change a reference in one cell will change the reference in another?

Duffman56

New Member
Joined
Dec 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi guys. First post on here, thanks to anyone who provides input.

I'm trying to maintain a massive spreadsheet that was created by my predecessor. It's amazing what he was able to do (extracts tons of data from another piece of software daily and inputs it into a spreadsheet of usable information). Unfortunately I'm still figuring out the ins-and-outs of the whole thing.

One of the tabs references multiple items on the balance sheet (which is brought in from another program). I haven't figured out why yet, but every so often the balance sheet information has been moved down a few rows. This makes all of the information on the tab referencing the balance sheet incorrect. For example, today I had to subtract 4 rows from all of the cell references BS!D66 became BS!D62, etc.

Originally I was going to use an Index function to find the right Balance Sheet category so it wouldn't matter what row it was in, but the spreadsheet already seems very overloaded and changing several to the Index function really dragged it down.

I'm wondering if it's possible to just change the first cell in each column, and have the formulas in the cells below adjust somehow? For example, changing BS!D66 to BS!D62 would also change the cell below from BS!D67 to BS!D63 and so on? I realize it may not be possible but really hoping it is so I can quit adjusting this spreadsheet that's supposed to be simply run and printed daily. Thanks in advance to anyone who replies!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Duffman56

New Member
Joined
Dec 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Correction - I think I was using VLOOKUP functions to find the right numbers.
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Does the balance sheet always contain the same number of rows? Something must be going on that is causing the formulas to increment. If you want them to not change, then you can try using absolute references, ie D$66 rather than D66. This way, if rows are inserted, the formulas won't change.

Alternatively, as you mentioned, you may want to update the first cell and have subsequent cells update accordingly...depending on your formula, you may be able to incorporate the offset function. For example, I have a spreadsheet that numbers items...whenever I copy or delete a row, all subsequent line numbers are updated accordingly with this formula in cell A6: =+INT(OFFSET(A6,-1,0))+1. This formula essentially takes the value of 1 row above and adds 1 to it.
 

Duffman56

New Member
Joined
Dec 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Does the balance sheet always contain the same number of rows? Something must be going on that is causing the formulas to increment. If you want them to not change, then you can try using absolute references, ie D$66 rather than D66. This way, if rows are inserted, the formulas won't change.

Alternatively, as you mentioned, you may want to update the first cell and have subsequent cells update accordingly...depending on your formula, you may be able to incorporate the offset function. For example, I have a spreadsheet that numbers items...whenever I copy or delete a row, all subsequent line numbers are updated accordingly with this formula in cell A6: =+INT(OFFSET(A6,-1,0))+1. This formula essentially takes the value of 1 row above and adds 1 to it.
Thanks for the reply Candyman. I'm trying to use the offset function you're referencing. I got it to work within a tab, but am having trouble getting it to work while referencing another tab. My formula is =+INT(OFFSET(AvgBS!D66,-1,0))+1. The cell is returning the value of 1.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
If using Vlookups killed your workbook, Offset will probably be even worse as it volatile.
Why not change the 1st formula & then fill down?
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
getting it to work across tabs will be very dependent on the structures of each tab. Are they the same? Perhaps if you could provide examples of each tab so we can see what we're working with and visualize what you're trying to accomplish.

NOTE: It may help to get the XL2BB add-in from this site...it makes sharing spreadsheet ranges super easy.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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
Top