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

[Duffman56]

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!

 

[Duffman56]

Correction - I think I was using VLOOKUP functions to find the right numbers.

 

[Candyman8019]

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]

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]

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]

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.