Query Help

[DavSam]

Greetings,
I have an Excel query that accesses an Access table to maintain a spreadsheet. In addition, the aforementioned spreadsheet is supposed to help populate another spreadsheet. Here's the problem: for some reason the link betweeen the two spreadsheets doesn't play well with the query active. If I remove the query, the link works fine; however, if any additions or removals are made in the Access table, the spreadsheet with the table query won't get updated. Is there a way to make them all play nice together? Thanks for the help.

 

[NBVC]

The query needs to be refreshed by force... right-click|Refresh query. Did you do that?

 

[DavSam]

NBVC,
Yes, I tried that, but when I try to use the other spreadsheet I still get the #NA error in the cells where the info pulled from the first spreadsheet is supposed to go. I have it to where the query gets refreshed when the spreadsheet with the query gets refreshed when the sheet is opened. Any other ideas? This is the last step I need to fix before I launch.

 

[NBVC]

So, it's not the actual initial spreadsheet with the query that is the problem, it's another sheet that uses the queried info that is the problem? Is that right?

Is the other sheet a separate workbook?

Can you give details on what formulas are being used to get the info into the second sheet?

 

[DavSam]

NBVC,
You are correct. The initial sheet,let's call it query sheet, works fine. The other sheet, in another workbook, is the problem. I'm using INDEX and MATCH to pull in the data from query sheet. For example: IF($B12 = "","ZZ",(INDEX([QS]Sheet1!$D$2:$D$3000,MATCH($B12,[QS]Sheet1!$A$2:$A$3000)))) Also, the INDEX I'm keying in on (Item ID #) was stored as text in the database and was imported in that form so I changed it to a long integer in the database; this did not help. I'm wondering, do I even need the query sheet? Any additions or removals of data are done in Access.

 

[NBVC]

A couple of things...

If you are referring to another workbook, you should have the extension in the workbook name (i.e. .xls or .xlsx), also, the MATCH() function usually takes on another optional argument to get the exact match... esp. if your data is not in sorted alphabetic order....

eg.

IF($B12 = "","ZZ",(INDEX([QS.xls]Sheet1!$D$2:$D$3000,MATCH($B12,[QS.xls]Sheet1!$A$2:$A$3000,0))))

Try that.