MS Query question (1 Viewer)

[jonathanchye]

Hi all,

Pretty new to this side of things in Excel so I hope someone could help me out.

We have an old spread sheet where we populate job information manually. However now that we've moved onto a database based system, there is no need to populate (most) of the field automatically.

The company still wants to utilise the reporting functionality of this old spreadsheet. So I am tasked to "convert" this spreadsheet into a semi automated one.

I have so far been successful in using MS Query to return results to a few worksheets. I've converted those results as "Tables". I've also added a few new columns which contains formulas to convert the data returned to more useful format. Ie =[@stiTotal]*[@stilUnitPrice]/1000

Everything seems to work at the moment but I have 2 main problems:

1) I currently have around 5 worksheets which I fed into one big "main" spreadsheet. I use the option of "Overwrite existing cells with new data, clear unused cells" for those 5 worksheets everytime on refresh. This makes sure I get the latest information from the database query but I run into a problem where the additional columns won't update... I have to manually drag them down each time there are new rows. I find the way around this is to drag all the way down for future expansions but I would like this to be automatically done. Can I use the "Convert to Dynamic Range" in this case? I notice that by creating additional space this slows down the workbook when refreshing as well...

2) In my main worksheet, there are some fields that can't be retrieved from the database via MS Query. I've created additional columns for users to manually key them in. However I find everytime the data is refresh this manual input data either gets misplaced or deleted. I understand this is because I've selected the "Overwrite cells option" on the worksheets containing the MS Query results. Is there a way to make this work?

Hopefully I am clear enough. I am fairly proficient with Excel but haven't really tried something like this before.

Also, I can only use MS Query to return the results from the database as it doesn't support other connections...

 

[Kiwiman]

Howzit

1) I currently have around 5 worksheets which I fed into one big "main" spreadsheet. I use the option of "Overwrite existing cells with new data, clear unused cells" for those 5 worksheets everytime on refresh. This makes sure I get the latest information from the database query but I run into a problem where the additional columns won't update... I have to manually drag them down each time there are new rows. I find the way around this is to drag all the way down for future expansions but I would like this to be automatically done. Can I use the "Convert to Dynamic Range" in this case? I notice that by creating additional space this slows down the workbook when refreshing as well...

You can do this by Rt Clicking on the data >> Data Range Properties >> Fill Down formulas in columns adjacent to data

2) In my main worksheet, there are some fields that can't be retrieved from the database via MS Query. I've created additional columns for users to manually key them in. However I find everytime the data is refresh this manual input data either gets misplaced or deleted. I understand this is because I've selected the "Overwrite cells option" on the worksheets containing the MS Query results. Is there a way to make this work?

Not sure what you mean by this, but if the additioanl columns are not foumulated (is this even a word??) then there is no guarantee that what a user enters would match the returning data set. Could you give examples

 

[jonathanchye]

Thanks for the quick reply

Howzit
You can do this by Rt Clicking on the data >> Data Range Properties >> Fill Down formulas in columns adjacent to data

Hmm, my tables are not converted to Data Ranges at the moment so I can't see this option when I right click. Should I convert them?

Not sure what you mean by this, but if the additioanl columns are not foumulated (is this even a word??) then there is no guarantee that what a user enters would match the returning data set. Could you give examples

I have 5 worksheets which basically are results returned from 5 different MS Queries... In some of the worksheets I have addition columns which converts the data (example shown in my first post above)

My main worksheet combines information from these 5 worksheets. It is mostly based on worksheet # 1 but also contain information from other worksheets. So I use a Vlookup of a field in the main worksheet to get information for various sub worksheets. Not sure if it is useful for me to attach excel file as you wont be able to open them and display results... hold on I will try a few screenshots

 

[Kiwiman]

Howzit

You need to be on section of the imported data to use the right click >> Data Range Properties... It will not show if you right click on a section of the sheet that does not contain the actual data.

You don't have to do anything special if using MS Query to import the data. I assume you used the Data >> Import External Data >> New Database Query to get this data in.

 

[jonathanchye]

I have attached a screenshot of the main spreadsheet to roughly illustrate what I am trying to achieve. Hopefully this will make things a bit clearer to you...

 

[jonathanchye]

Howzit

You need to be on section of the imported data to use the right click >> Data Range Properties... It will not show if you right click on a section of the sheet that does not contain the actual data.

You don't have to do anything special if using MS Query to import the data. I assume you used the Data >> Import External Data >> New Database Query to get this data in.

This is what I get when I right click on one of the worksheet containing Query data

 

[jonathanchye]

I am using Excel 2010 and it seems the "Automatically fill down formula" option is not available?

http://www.mrexcel.com/forum/showthread.php?t=312541

 

[Kiwiman]

Howzit

It looks like you are using Excel 2007 \ 2010, not 2003 where my post would have made sense. I have only just purchased 2010 so not that familiar with the changes as yet.

I just tried puytting in a formula in my Excel 2010 MS Query file and the formulas copied down automatically.

If this file is a converted 2003 file you may get some joy by looking at Data Tab >> Connections Group >> Click Properties >> In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.

Excel will only copy those formulas that are immediately adjacent to (that is no empty column between the data and your formulas) or within the external data set.

 

[jonathanchye]

Howzit

It looks like you are using Excel 2007 \ 2010, not 2003 where my post would have made sense. I have only just purchased 2010 so not that familiar with the changes as yet.

I just tried puytting in a formula in my Excel 2010 MS Query file and the formulas copied down automatically.

If this file is a converted 2003 file you may get some joy by looking at Data Tab >> Connections Group >> Click Properties >> In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.

Excel will only copy those formulas that are immediately adjacent to (that is no empty column between the data and your formulas) or within the external data set.

Thanks again for your reply! I think I've got the formula bit sorted mate. I must have unticked the "Preserve column sort and layout" sometime in the past. What I did it go to the very first row and made sure the formula is copied down till the end and saved the table. I think this should work in the future as this is equivalent to the property you were referring to.

Now just the second question remains...I personally think what I am thinking of doing is not possible as I am combining results fetched from MS Query and user manual input without actually having a "unique key"...

 

[Kiwiman]

Howzit

Glad you got the formulas fill working. For your 2nd problem is it possible to upload a small file to show the problem. I will not need to refresh the data just look at what is happening in the formula - especially where the are no values returned.

 

[jonathanchye]

Howzit

Glad you got the formulas fill working. For your 2nd problem is it possible to upload a small file to show the problem. I will not need to refresh the data just look at what is happening in the formula - especially where the are no values returned.

edit: never mind found a way to upload file now Can you please have a look when you can mate? Really appreciate it!

 

[Kiwiman]

Howzit

Short of finding out why the missing information is not in the table in the first place, the only thing I can come up with is to get the users to populate a static list (in another sheet instead of manually overwriting the formula) that will hold the missing information by job id and then replace the 0 in your iferror function with a lookup to this static list. This way when you refresh the data, your static list will still be available.