I have an Excel spreadsheet which connects to a SQL Server 2000 database.
When you type in a work order number in WorkOrder!J1, several queries
retrieve the data to fill in all the fields. This works fine for everything
except the products ordered. I suspect this has something to do with the fact
that the Products worksheet is the only worksheet which retrieves multiple
rows from the database. On the WorkOrder worksheet, I have three columns
(unit price, quantity, description) which reference the Products worksheet.
For example, the unit price column goes like this: Products!E2, Products!E3,
Products!E4, etc. But when a work order number is entered, it somehow is
changing to this: Products!E2, Products!E13, Products!E14, etc. It may be
useful to know that the second row of the Products worksheet contains the
first product ordered, and the 13th row of the Products worksheet contains
the last product ordered. References to the rows between the first and last
products seemingly disappear. I can temporarily fix this by selecting each
column and using CTRL+D to fix the references, but if you delete the work
order number which then empties all the fields, there are several rows of
Products!#REF! between Products!E2 and Products!E3 equal to the number that
had previously gone missing. I experimented with changing my relative
references to absolute ones but that had no effect. Several Google searches
turned up nothing but the basics of cell referencing which are no help at
all. Does anyone know what is causing this and how to fix it permanently?

It figures that less than five minutes after I post, I stumble across the
solution on my own. By default, Excel was inserting and deleting rows on the
Products worksheet. I changed it to overwrite existing cells in data range
properties and the problem is gone. Why anybody would want it to function
that way by default is beyond me.

"Scott" wrote:

I have an Excel spreadsheet which connects to a SQL Server 2000 database.
When you type in a work order number in WorkOrder!J1, several queries
retrieve the data to fill in all the fields. This works fine for everything
except the products ordered. I suspect this has something to do with the fact
that the Products worksheet is the only worksheet which retrieves multiple
rows from the database. On the WorkOrder worksheet, I have three columns
(unit price, quantity, description) which reference the Products worksheet.
For example, the unit price column goes like this: Products!E2, Products!E3,
Products!E4, etc. But when a work order number is entered, it somehow is
changing to this: Products!E2, Products!E13, Products!E14, etc. It may be
useful to know that the second row of the Products worksheet contains the
first product ordered, and the 13th row of the Products worksheet contains
the last product ordered. References to the rows between the first and last
products seemingly disappear. I can temporarily fix this by selecting each
column and using CTRL+D to fix the references, but if you delete the work
order number which then empties all the fields, there are several rows of
Products!#REF! between Products!E2 and Products!E3 equal to the number that
had previously gone missing. I experimented with changing my relative
references to absolute ones but that had no effect. Several Google searches
turned up nothing but the basics of cell referencing which are no help at
all. Does anyone know what is causing this and how to fix it permanently?