Excel query problem

Jed Shields · 24 May 2011 at 15:03

Hi guys,

I've got two spreadsheets: Provider Master and Provider Template. The Master contains all the data of multiple providers, the Template queries the Master and pulls in the data for a single provider through a drop down list.

Problem - most of the data is pulled across but the last 3 columns won't pull in the text. However, if I change the original text to numbers then it works. I can't see any differences in the formatting etc. All the blank cells in the returned query show a 0. However, in the cells where data should be pulled across (not every cell has a value in the master list) show as blank.

Any ideas?

dynamis_dk · 24 May 2011 at 17:24

Are the cells on the destination template set to specific data types (I.e set as text as that's what you wanting to bring across from the master)

Jed Shields · 24 May 2011 at 17:49

Just checked and the formatting is the same. I've even copy paste special format from a column that works to make sure ... still pulling in blanks. Very odd.

dynamis_dk · 25 May 2011 at 12:41

Can you paste the forumula your using on the forum...

Someone might see something obvious in it

dazzerd · 25 May 2011 at 22:49

Without seeing the formula I can only guess but is sounds to me that the lookup is failing as you are mixing text and number values. Try adding -- before the value in the lookup this will convert the cell value to a number.

Participant · 26 May 2011 at 10:20

Jed Shields said:
Just checked and the formatting is the same. I've even copy paste special format from a column that works to make sure ... still pulling in blanks. Very odd.

F2 enter on a couple of the cells that aren't pulling through and see if it changes anything. If so, it's time to get busy with Ctrl-H (find and replace).

I have this problem (if that's what it is) on many of my spreadsheets. Numbers formatted as text don't get found by VLOOKUPS until I F2, Enter on each blummin cell if there aren't too many, or Find and Replace from 0-9.

Jed Shields · 27 May 2011 at 09:43

Sorry for the delay, I hadn't check the post for a while.

F2 doesn't do anything.

There are no numbers in the cells it's pulling over - it's either an R,A or G as a code for Red, Amber or Green. The other info it pulls over is a description of the status ie why it's a Red, Amber or Green.

There's no formula to post as it's a query? Or do you want to see the query formula?

Connection String:

DSN=Excel Files;DBQ=Z:\Test 20110519\Provider Dashboard Master.xlsm;DefaultDir=Z:\Test 20110519;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Command Text:

SELECT DataInputAll.Provider, DataInputAll.Cat, DataInputAll.ProviderCat, DataInputAll.`2010 Q1`, DataInputAll.`2010 Q2`, DataInputAll.`2010 Q3`, DataInputAll.`2010 Q4`, DataInputAll.`2011 Q1`, DataInputAll.`2011 Q2`, DataInputAll.`2011 Q3`, DataInputAll.`2011 Q4`
FROM DataInputAll DataInputAll
WHERE (DataInputAll.Provider=?)

It works fine up to DataInputAll.`2011 Q1 and then it just shows a blank rather than the value.