1

My formula in Sheet 1, A3 is

=SUMPRODUCT(('count May'!$D$2:$D$2000=Categories!$A$5)*('count May'!$E$2:$E$2000=Categories!$B$3)*(ISNUMBER(SEARCH('count May'!$F$2:$F$2000,Categories!$C$4))))

Count may is the sheet for the month of may(I'm doing this for a full year), categories is the sheet that has my helper table. When I paste the data from the export with about 1600 rows one column with different companies, another column with different employment status,another column with various locations i.e company $A$5, fulltime $B$3, Location $C$4 into any month sheet the cell that has the above formula changes all the cell ranges from $2:$2000 to $1609:$2000.

=SUMPRODUCT(('count May'!$D$1609:$D$2000=Categories!$A$5)*('count May'!$E$1609:$E$2000=Categories!$B$3)*(ISNUMBER(SEARCH('count May'!$F$1609:$F$2000,Categories!$C$4))))

I then get #Value! or #N/A in A3. Essentially the pasted data makes the formula range in A3 start from a few rows after the last pasted data cell. I have tried to change $D$1609:$D$2000 to $D:$D, $E:$E, $F:$F, but I get a excel cannot complete this task error, I tried changing $D$2000 to $D$1600 but that didn't work and is not feasible because every month will have a different number of rows. I tried cut instead of copy but still happens.

If it is any help the data is put together in one sheet, column D,E,F and then I copy or cut and paste in the month sheet and then the formulas are in a different sheet but reference each month.

Any help is much appreciated.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Ben
  • 27
  • 8
  • Have you tried copying the ASCII text from the formula line and pasting that? Because if you do it that way, it will not try to "interpret" the formula for you. – durbnpoisn Jun 30 '16 at 17:07
  • If I understand you correctly, does that mean copying the data cell by cell? Because one column has ~1000 rows. But correct me if I'm wrong. – Ben Jun 30 '16 at 17:36
  • No. I mean, if you are currently copying by highlighting the cell and pressing CTRL-C, and then pasting, that is probably your problem. If you highlight the cell, then highlight the TEXT in the formula line above and copy THAT, you won't have your references messed up. – durbnpoisn Jun 30 '16 at 17:37
  • Gotcha! but there are too many cells to repeat that. And I can't highlight the entire range A1:F1600 and copy the text right? Because each cell has text in it. – Ben Jun 30 '16 at 18:03
  • Can you use Structured Tables? (Select table, control-T to convert into Structured table) Then you could start referencing columns (like [Categories]) rather than specific cells (like $D$2:$D:$2000). For me, it makes reading and debugging formulas much easier and might help assist in solving this pasting issue you're running into. – Namkce Jun 30 '16 at 19:51
  • I cold be wrong here and please correct me if I am. You have the formula in your sheet first, then afterwards you copy and paste in your data. To me it initially sounds like the reference row 2 is being pushed down by whatever you are pasting. Its like you are inserting rows instead of overwriting rows. As a result the cell reference gets moved down to the end of your inserted data. That would explain why your first reference number is changing. However, if this were true, then your second reference number would also be changing by an equal amount. – Forward Ed Jul 01 '16 at 07:20
  • how are you getting your data on to the sheet your are using the formula in? are you using paste, insert copied cell, ctrl+V, shift+INS, paste special? – Forward Ed Jul 01 '16 at 07:25
  • I'm losing it here because I expect absolute referencing SHOULD take care of this @Forward Ed you are right in that it is pushing it down and changing the first reference but surprisingly the second is not changing. Also I am pasting using CTRL-V, and copying or cutting (I've tried both) using CTRL-C or CTRL-X – Ben Jul 01 '16 at 20:39
  • What about right click and using the paste in there? also check your settings to see if there is something that affects the way paste or insert works. Also try it on a blank worksheet or better yet in a brand spanking new workbook with no tables. Dump in your formula first, and then do a paste of your data and see if you get the same results. I am just wondering if this may be related to a table or something residual in the background. – Forward Ed Jul 01 '16 at 20:41

1 Answers1

0

I'm joining in late here so may be you may have already fixed your bug (if it's the case lets know how!). If I understand correctly, you have sheet 'Count May' with a formula in A3 and you're a copying data (range A1:F1600) into 'Count May' (presumably in A2) in order to use your formula. Doing so messes up the reference in the formula in A3. The only way I was able to recreate this was by copying/pasting entire rows, (i.e. rows 1:1600), instead of the range of cells needed, (i.e. A1:F1600). That would insert 1600 rows in 'Count May' and effectively push your reference down. Your second reference wouldn't get moved because it references to another sheet, 'Categories' untouched by the copy/paste. Your problem should be fixed by copying the range of cell needed (instead of entire rows) and selecting A2 (not row 2) to paste.

Pomul
  • 392
  • 3
  • 11