Excel writing&deleting data to/from wrong wksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a spreadsheet that is basically a list of clients.

I have written functions to alphabetize this list, as well as sorting by a
custom list; I also have a dynamic named range to calculate the first
available blank row.
Also included in this is a custom form to input data.

On the second worksheet of the workbook is a totals sheet which totals using
COUNTIF and COUNTA, using named ranges as arguments.

The spreadsheet will work fine for a period of time; whether it is a file
save, or program close that causes this issue, I have not identified yet. I
will say for right now that it happens randomly.

This is what happens:
Clients can be entered by using the form, there are probably 10 cells of
data across the row. These include dates (mm/dd), names, yes/no, and text
entires limited to 100 characters.
When the client list is edited by clicking on cells and changing the data
entry, duplicate entries of the data entered appear in the same cell on the
second (totals) worksheet. Deleting the data on the first worksheet deletes
it from the second sheet, and vice versa.

Also, the COUNTIF and COUNTA functions are corrupting, values are being
returned as ## instead of numbers.

Is there something I may have done wrong in the construction of this
spreadsheet, or is this a bug of sorts? I should note that the spredasheet
was written in Excel 2002, and was put into use on an Excel 2003 machine.

Thank you for your help, I can supply the spreadsheet if needed for
examination.
 
Ian said:
When the client list is edited by clicking on cells and changing the data
entry, duplicate entries of the data entered appear in the same cell on the
second (totals) worksheet. Deleting the data on the first worksheet deletes
it from the second sheet, and vice versa.
Click to expand...

Sounds to me like you have grouped the worksheets.
When multiple worksheets are selected (e.g. by ctrl+Click on the sheet tabs)
changes made to one of the selected sheets also affect the others.
In this state you will see [Group] in the title bar of Excel.

To ungroup, select a sheet outside the group, or right-click the tab of a
sheet which has been grouped and select Ungroup

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Back
Top