Variable external data through a Microsoft Query
 

I am trying to create a Price list, using Excel 2003, after importing data
from Sage Line 50. I have created a query in excel which works fine to start
with. The data imported is stock code, description, last cost price into
sheet 1. There is a filter that stops all discontinued lines (the Stock
Location is defined as "Discontinued" in Sage), so after refreshing the data,
I have only current stock lines. I then use a separate sheet in the same
workbook to group relevant products. So on the grouped list (sheet 2), I
refer to the stock code using the simple = formula. Sheet 2 cell a1 = sheet 1
cell a6, and sheet 2 cell A2 = sheet 1 a25, for example. The problem arises
when a product is discontinued in Sage. Refreshing the data removes the line
from the query, but then all data on sheet 2 becomes corrupted - sheet 1 cell
a25 becomes a completely different product. Is there a way I can do the
references so that sheet 2 cell a2 will always refer to the original cell on
sheet 1? I hope I've made myself clear!!!!
Thanks in advance

Variable external data through a Microsoft Query
 

Have a look in the help index for INDIRECT

--
Don Guillett
SalesAid Software

"Jocular Jock" wrote in message
...
I am trying to create a Price list, using Excel 2003, after importing data
from Sage Line 50. I have created a query in excel which works fine to
start
with. The data imported is stock code, description, last cost price into
sheet 1. There is a filter that stops all discontinued lines (the Stock
Location is defined as "Discontinued" in Sage), so after refreshing the
data,
I have only current stock lines. I then use a separate sheet in the same
workbook to group relevant products. So on the grouped list (sheet 2), I
refer to the stock code using the simple = formula. Sheet 2 cell a1 =
sheet 1
cell a6, and sheet 2 cell A2 = sheet 1 a25, for example. The problem
arises
when a product is discontinued in Sage. Refreshing the data removes the
line
from the query, but then all data on sheet 2 becomes corrupted - sheet 1
cell
a25 becomes a completely different product. Is there a way I can do the
references so that sheet 2 cell a2 will always refer to the original cell
on
sheet 1? I hope I've made myself clear!!!!
Thanks in advance

Variable external data through a Microsoft Query
 

Perhaps absolute references will help:
http://www.cpearson.com/excel/relative.htm
--
Brevity is the soul of wit.


"Jocular Jock" wrote:

I am trying to create a Price list, using Excel 2003, after importing data
from Sage Line 50. I have created a query in excel which works fine to start
with. The data imported is stock code, description, last cost price into
sheet 1. There is a filter that stops all discontinued lines (the Stock
Location is defined as "Discontinued" in Sage), so after refreshing the data,
I have only current stock lines. I then use a separate sheet in the same
workbook to group relevant products. So on the grouped list (sheet 2), I
refer to the stock code using the simple = formula. Sheet 2 cell a1 = sheet 1
cell a6, and sheet 2 cell A2 = sheet 1 a25, for example. The problem arises
when a product is discontinued in Sage. Refreshing the data removes the line
from the query, but then all data on sheet 2 becomes corrupted - sheet 1 cell
a25 becomes a completely different product. Is there a way I can do the
references so that sheet 2 cell a2 will always refer to the original cell on
sheet 1? I hope I've made myself clear!!!!
Thanks in advance