cell numbers change when moved

[mike rand]

Good Day aLL.

I have a spreadsheet ive been working on for some time , Id like to move some things around and add rows above or below certain cells. Problem... when i add a row on say sheet 4, sheet 1 has calculation to take info from sheet 4 but now that ive added a row sheet 1 is looking to take info from say c5 to complete calcu;lation but since row was added the info it needs to access is now in c6 and all calculations from sheets 1,2,3 that require info from sheet 4 to complete calculations have ref# errors

do i have to manually cahange all calculations that are affected by this or is there a way to have excel automatically change the calculation from c5 to c6 in the cell which contains the formula .


thanks
mike

[ChemistB]

Typically if you insert a row before a cell that's referenced on another sheet, the other sheet will automatically modify the cell ID so there should be no problem.

There are exceptions, for eample, INDIRECT functions won't adapt because they are looking at strings, not ranges. What exactly is the formula that is giving you trouble?

[mike rand]

=IF(ISERROR(INDIRECT("'1'!K189")),"",INDIRECT("'1'!K189")) this is the formula on tab called PROPOSAL on another tab is master estimate in k189 is a number created by its own formula

when i add or delete a row on tab master estimate the cell reference k189 changes to k188 or 187 depending if i added or deleted row but on tab proposal its still looking for k189 but since its moved one up or one down on estimate sheet it doesn't find anything in the cell k189 cause its blank. how do i get the proposal tab to change its cell from k189 to k188 as i add, delete or move the cell or rows on the estimate tab.

hope im being clear enough lol i almost cant understand myself except for i know what i want it to do lol

thanks again
mike

[oeldere]

Why not all data on 1 worksheet?

That makes it a lot easier to analyse the data.