Applying the INDIRECT or INDEX function on formula to solve #REF issue

Hello all from The Netherlands,

I've finally decided to register after I already used alot of archived posts to help me further, thanks for the great resource this forum provides! . This time, however, I really require some problem specific help. I have a excel workbook where I have one sheet monitoring the other. Both have the same lay-out with in the A column names of employees and then each following column represents a day. In one sheet each column contains the task for that day, the other sheet converts this in 1 (busy), or 0 (nothing to do) so it makes it easy for me to do some more advanced calculations and programming to it. (it also includes other numbers for other states).
When I delete something I keep getting #REF errors in my formula that converts it to either 1 or 0. I found out that this can be solved by using the INDIRECT or INDEX function. However, I failed to apply this properly to my function so could anybody help me with that?

Please Login or Register  to view this content.

Thanks!

Hi and welcome to the forum.

Would you upload a small sample workbook for us?

What do you delete: entire row, netire column, data in cell...?

...When I delete something I keep getting #REF errors in my formula that converts it ....

I am not able to see #REf errors, if i delete something..

Where exactly, is the problem?

In Availability Sheet, F16, for example, exist this formula and gives as result 1

=IF(AND(ISBLANK($A15);ISBLANK(Planning!F16));"";IF(ISBLANK(Planning!F16);0;1))

If i delete the word in (Planning!F16, gives me 0. Not error...

This result is Corect, regarding the formula. Isn't it?

He delete column in that question. If you delete value in a cell you shoudln't get REF error.

REF error you get when there is a reference problem.

For example, write formula: =ROW(C3)-1 and you get result 2.
But if you put this in ROW 10 and pull formula up you will get reference error when you go below C1 (because there is no row C0)...
Now, the question is where you lose your reference?

Just pull your formula over that cell and they should get corrected.
This can happend if you delete B column or 12th row but not if <ou delete data in B12

you have code that cuts cells so you would get #REF errors in formulas that referred to the cells that were in the location that you pasted the cut cells.

Forgive the intrusion.

Am I correct in thinking that you would be deleting columns in Planning! (Col B) when that day is past. Or perhaps all that week. If so you would also be deleting the column B in Availability!. You'd need to change the way the day is calculated in row 6.

Or am I mistaken?

you should copy and paste and then clear the old data cells instead of cutting them.

And make the cell reference absolute before copy and paste.

the cell references shouldn't need to be changed-it's only the fact that you are effectively deleting cells that is causing the error.

Hmm?
I'm late in joining this thread, but if you put this formula in Sheet"Availability" B10

Please Login or Register  to view this content.

Drag Across to Column BI, then down as required.

Does this help with your #REF errors, or am I on the wrong track?