Cell values being moved to a new page but still be read by other formulas

Hi everyone,

I have a macro which cuts rows of cells in one worksheet and pastes them into another. In worksheet 1, beside these rows, there are cells which count results. When I activiate the macro it cuts the data rows and moves them to another worksheet, but the cells which count the results automatically adjust to continue to count the cells in the new worksheet

Is there any way I can have these counting cells only count the cells in the worksheet they are originally in? Here's my formula:

=COUNTIF('Worksheet 1'!J3:AB3,"Y")+COUNTIF('Worksheet 1'!J3:AB3,"N")

So when the cells are moved via macro, the formula seems to automatically change to:

=COUNTIF('Worksheet 2'!J3:AB3,"Y")+COUNTIF('Worksheet 2'!J3:AB3,"N")

Interestingly enough, i have other counter cells which count complete columns, i.e. J:J, and they don't adjust as the data changes. Any ideas?

Thanks guys!

Perhaps instead of CUT and Paste to the new location...
Have the macro do COPY and Paste to the new location, then ClearContents of the original.

Jonmo,

You know, I never thought of that. I'll certainly give it a try. In any case, do you have any suggestions as to how I could amend the formulas above?

You could use INDIRECT

=COUNTIF(INDIRECT("'Worksheet 1'!J3:AB3"),"Y")+COUNTIF(INDIRECT("'Worksheet 1'!J3:AB3"),"N")

But I would not recommend it.
Indirect is volatile and will significantly reduce your sheet's performance if used alot.

The best solution is the copy/paste instead of cut/paste.

Jonmo,

I tried the indirect method you suggested. It worked great on the one row, but when I tried to copy down the remaining rows that do the same thing it wouldn't copy correctly. I think i'll need to try the whole copy/paste/clear values method you suggested.

Thanks for your help

yep, another argument against using Indirect.

The range J3:AB3 becomes just a text string within the Indirect function.
And it won't incriment as it's dragged down.

This can be accounted for, but you have to get quite clever, and it really just isn't worth it considering the impact it has on sheet performance.