Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell References and External Data
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? |
#2
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create one pivot from mulitple data sources | Excel Discussion (Misc queries) | |||
External References | Excel Discussion (Misc queries) | |||
How do you locate External References in an Excel Workbook | Excel Discussion (Misc queries) | |||
Save External Link Values | Excel Discussion (Misc queries) | |||
External References not working right. | Excel Worksheet Functions |