Inserting/deleting rows and cutting cells causing corrupted formulas.

[alxes]

Hey all,

Whenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.

Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.

I gave you an idea by deleting a row range in Mon tab which causes #REF error.

Thanks,
Al

[RobertMika]

What I see I removed Wed-Friday tabs and no sign of REF in Monday part of the table

[XOR LX]

Hi,

It's hard to say as we can't see what your references were before you deleted some rows.

Regards

[martindwilson]

its further over chaps! look at aa
however some of it seems oddly constructed but to stop it happening
change cell referenced to index
so
=IF(Mon!$V7="50/50",Mon!S7,IF(Mon!$V7="D","",IF(Mon!$V7="W",Mon!S7,""))) becomes

=IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="50/50","50/50",IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="W","W",IF(INDEX(Mon!$V:$V,ROWS($A$1:A7))="D","D","")))

INDEX(Mon!$V:$V,ROWS($A$1:A7)) is the same as Mon!$V7

[6StringJazzer]

Here is an implemented solution that takes a similar approach to martindwilson but is a little different. The solution has to deal with all five columns (or is it six, I don't remember now).

The problem, alxes, is that you have big, bold #REF! errors right in our face but the little tiny #REF! errors off to the right are the ones you want us to look at. It would help to be more specific.

[alxes]

Sorry guys for not being specific enough. The original spreadsheet was 2 MB and I had to throw away a lot of stuff to get it below 1 MB to post it on the forum and that created some problems.

Thanks for the proposals, I will look into them when I got few minutes.

[alxes]

Here is an implemented solution that takes a similar approach to martindwilson but is a little different. The solution has to deal with all five columns (or is it six, I don't remember now).

I like that solution, the only problem is that it is pulling the initials to the root cause table even if the root cause column (Column V, Mon tab) is left blank. I think it's connected with this part of the formula:

Please Login or Register  to view this content.

Thanks,
Al

[6StringJazzer]

I did another review of your spreadsheet, understand a little more about what you're actually doing, and now wonder why the original formulas were so complicated. For example, the original formula in column AA is

=IF(Mon!$V6="50/50","50/50",IF(Mon!$V6="W","W",IF(Mon!$V6="D","D","")))

But according to header in Mon!V5, the only possible values are 50/50, W, D, or null. Why do you check each possible value here? Basically this says if the user enters an illegal value, show a null. If you want to restrict what the user can enter, then I would recommend Data Validation on Mon!V5. I have done that and this simplifies the other formulas quite a bit. This also solves the problem that you noted.

[alxes]

I did another review of your spreadsheet, understand a little more about what you're actually doing, and now wonder why the original formulas were so complicated. For example, the original formula in column AA is

=IF(Mon!$V6="50/50","50/50",IF(Mon!$V6="W","W",IF(Mon!$V6="D","D","")))

But according to header in Mon!V5, the only possible values are 50/50, W, D, or null. Why do you check each possible value here? Basically this says if the user enters an illegal value, show a null. If you want to restrict what the user can enter, then I would recommend Data Validation on Mon!V5. I have done that and this simplifies the other formulas quite a bit. This also solves the problem that you noted.

Thank you 6StringJazzer, it looks like my main problem was not knowing the INDEX function. I amended the formula proposed by you and it works like a charm.

Please Login or Register  to view this content.

martindwilson also gets the credit for help, he came up with INDEX function as well.

Problem solved.

[6StringJazzer]

Glad to hear it's fixed, thanks for marking Solved, and thanks for the rep!