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

**Flight777** · 05-24-2012, 03:13 AM

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!

**Fotis1991** · 05-24-2012, 03:16 AM

Hi and welcome to the forum.

Would you upload a small sample workbook for us?

**Flight777** · 05-24-2012, 03:42 AM

Sure, here you go:

Book2.xls

**zbor** · 05-24-2012, 03:46 AM

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

**Fotis1991** · 05-24-2012, 03:47 AM

...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?

**Flight777** · 05-24-2012, 03:48 AM

Data in cells. So tasks and workorders.

**Flight777** · 05-24-2012, 03:49 AM

Originally Posted by Fotis1991

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

Where exactly, is the problem?

Yes that is the weird thing, sometimes it doesn't give #REF errors (in the formula) and sometimes they do appear. Especially when I select a larger area and delete that (the data that is).

**Flight777** · 05-24-2012, 03:51 AM

I found this thread which looks very similar to my problem, I do not delete columns or rows, however:

http://www.excelforum.com/excel-gene...-a-column.html

**Fotis1991** · 05-24-2012, 03:54 AM

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?

**zbor** · 05-24-2012, 03:57 AM

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?

**Flight777** · 05-24-2012, 03:57 AM

No what I mean with #REF error is that the formula returns 1 and reads:

=IF(AND(ISBLANK($A11);ISBLANK(Planning!#REF!));"";IF(ISBLANK(Planning!#REF!);0;1))

This returns 1 while the cell in planning is empty and thus it should return 0

**zbor** · 05-24-2012, 03:59 AM

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

**Flight777** · 05-24-2012, 04:02 AM

I still get it and I don't delete any columns or rows. I already corrected in the way you said everytime, but I want to automate the process and then when it happens the system can't correct it and thinks it is a 1

I don't change anything to the planning sheet other that deleting data in cells.

**JosephP** · 05-24-2012, 04:07 AM

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.

**Flight777** · 05-24-2012, 04:13 AM

Yes it "moves" the tasks back a day when it detects it is on the next day. Can I do something to prevent those errors?

**Russell Dawson** · 05-24-2012, 04:13 AM

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?

**Flight777** · 05-24-2012, 04:18 AM

No, the code should run when Excel is started and looks when the planning sheet was last updated. If it sees that it is yesterday, it will select all the data (so from row 9 and on) of the other 14 days (in total there are 15 days), cut the data, and move it to the left with a -4 offset (4 quarters = 1 day). So I don't delete anything. :P

**JosephP** · 05-24-2012, 04:18 AM

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

**vlady** · 05-24-2012, 04:24 AM

And make the cell reference absolute before copy and paste.

**Flight777** · 05-24-2012, 04:27 AM

Originally Posted by JosephP

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

Ok I will try that

@vlady, is there a easy/lazy way to do that instead of manually go to each cell? Because I did not make them absolute so that I could use autofill.

**JosephP** · 05-24-2012, 04:42 AM

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.

**Flight777** · 05-24-2012, 04:46 AM

I will edit the code to reflect your proposed solution and run it during the next test and let you know the outcome. Thanks for all the help, didn't know it would go that quick!

**Marcol** · 05-24-2012, 05:41 AM

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?

**Flight777** · 05-24-2012, 09:23 AM

The copy pasting seems to work until now, thanks again for all the help!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions