trigger MS Query Refresh on population of partic. cell

ajm · Sep 25, 2006

Hi all (again),

I need a hand with a small hiccup. How do I get MS Query to refresh data in one workbook when a particular cell is populated in another workbook? It might make things more interesting to know that the "particular" cell will be different each new month.

The work in process revolves around a monthly report we receive that details and average fuel price for the month. We take that figure and put it into a workbook according to month. Movement in this price is then used to calculate increments or decrements to our clients fuel subsidies.

Any ideas?

Legacy 98055 · Sep 25, 2006

Hi Ajm.

Can you? Probably yes. Can I or anybody else really tell you how? Not really without some more information. Logic will have to be designed to determine "which" cell has changed in the other workbook. If this query needs to be refreshed in real time, that adds further complication. I would imagine that simply checking this "other" cell upon opening the workbook that contains the query would suffice. However, I don't know because you have not told me.

ajm · Sep 25, 2006

hey there right_click,

neither workbook is in real time. the cell to be populated will be on a monthly basis around the tenth of each month.

the process is thus: we receive the fuel report from an external agency. the figure for last month is copied and pasted into a spreadsheet next to the previous months. After this is done, the movement between the two months is calculated and applied to the previous month's fuel price for that operator. The MS Query simply brings in the number of litres used this month by that operator so that we can multiply the price by the volume.

its simple enough to do manually, except I am more than a little bored with others not checking they have the correct month's data before we make our payments.

would more specific details help? the trigger cell(s) are in a workbook called "Key Factors" on the "Indicies" tab. they occupy row 8 across the page, entitled "Fueltrac Index value".

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td valign="top">Month and Year - (white text)</td>
<td valign="top">July 2004</td>
<td valign="top">August 2004</td>
<td valign="top">September 2004</td>
<td valign="top">October 2004</td>
<td valign="top">November 2004</td>
<td valign="top">December 2004</td>
<td valign="top">January 2005</td>
<td valign="top">February 2005</td>
<td valign="top">March 2005</td>
<td valign="top">April 2005</td>
<td valign="top">May 2005</td>
<td valign="top">June 2005</td>
<td valign="top">July 2005</td>
<td valign="top">August 2005</td>
<td valign="top">September 2005</td>
<td valign="top">October 2005</td>
<td valign="top">November 2005</td>
<td valign="top">December 2005</td>
<td valign="top">January 2006</td>
<td valign="top">February 2006</td>
<td valign="top">March 2006</td>
<td valign="top">April 2006</td>
<td valign="top">May 2006</td>
<td valign="top">June 2006</td>
<td valign="top">July 2006</td>
</tr>
<tr>
<td bgcolor="#CCFFCC" valign="top">Current Year</td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
</tr>
<tr>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
<td align="right" valign="top"></td>
</tr>
<tr>
<td valign="top">Fuel change- from previous month</td>
<td align="right" valign="top"></td>
<td align="right" valign="top">0.0301%</td>
<td align="right" valign="top">6.7528%</td>
<td align="right" valign="top">3.0314%</td>
<td align="right" valign="top">2.7601%</td>
<td align="right" valign="top">0.0000%</td>
<td align="right" valign="top">-6.9409%</td>
<td align="right" valign="top">-1.0002%</td>
<td align="right" valign="top">2.4247%</td>
<td align="right" valign="top">7.9380%</td>
<td align="right" valign="top">3.8381%</td>
<td align="right" valign="top">-4.5512%</td>
<td align="right" valign="top">5.0667%</td>
<td align="right" valign="top">2.6243%</td>
<td align="right" valign="top">0.7085%</td>
<td align="right" valign="top">5.9114%</td>
<td align="right" valign="top">0.3130%</td>
<td align="right" valign="top">-7.0863%</td>
<td align="right" valign="top">-0.8847%</td>
<td align="right" valign="top">6.4799%</td>
<td align="right" valign="top">2.2588%</td>
<td align="right" valign="top">1.6624%</td>
<td align="right" valign="top">4.7114%</td>
<td align="right" valign="top">1.6115%</td>
<td align="right" valign="top">-1.0105%</td>
</tr>
<tr>
<td valign="top">Month number</td>
<td align="right" valign="top">13</td>
<td align="right" valign="top">14</td>
<td align="right" valign="top">15</td>
<td align="right" valign="top">16</td>
<td align="right" valign="top">17</td>
<td align="right" valign="top">18</td>
<td align="right" valign="top">19</td>
<td align="right" valign="top">20</td>
<td align="right" valign="top">21</td>
<td align="right" valign="top">22</td>
<td align="right" valign="top">23</td>
<td align="right" valign="top">24</td>
<td align="right" valign="top">25</td>
<td align="right" valign="top">26</td>
<td align="right" valign="top">27</td>
<td align="right" valign="top">28</td>
<td align="right" valign="top">29</td>
<td align="right" valign="top">30</td>
<td align="right" valign="top">31</td>
<td align="right" valign="top">32</td>
<td align="right" valign="top">33</td>
<td align="right" valign="top">34</td>
<td align="right" valign="top">35</td>
<td align="right" valign="top">36</td>
<td align="right" valign="top">37</td>
</tr>
<tr>
<td bgcolor="#800080" valign="bottom">MONTH</td>
<td bgcolor="#800080" align="center" valign="bottom">July</td>
<td bgcolor="#800080" align="center" valign="bottom">August</td>
<td bgcolor="#800080" align="center" valign="bottom">September</td>
<td bgcolor="#800080" align="center" valign="bottom">October</td>
<td bgcolor="#800080" align="center" valign="bottom">November</td>
<td bgcolor="#800080" align="center" valign="bottom">December</td>
<td bgcolor="#800080" align="center" valign="bottom">January</td>
<td bgcolor="#800080" align="center" valign="bottom">February</td>
<td bgcolor="#800080" align="center" valign="bottom">March</td>
<td bgcolor="#800080" align="center" valign="bottom">April</td>
<td bgcolor="#800080" align="center" valign="bottom">May</td>
<td bgcolor="#800080" align="center" valign="bottom">June</td>
<td bgcolor="#800080" align="center" valign="bottom">July</td>
<td bgcolor="#800080" align="center" valign="bottom">August</td>
<td bgcolor="#800080" align="center" valign="bottom">September</td>
<td bgcolor="#800080" align="center" valign="bottom">October</td>
<td bgcolor="#800080" align="center" valign="bottom">November</td>
<td bgcolor="#800080" align="center" valign="bottom">December</td>
<td bgcolor="#800080" align="center" valign="bottom">January</td>
<td bgcolor="#800080" align="center" valign="bottom">February</td>
<td bgcolor="#800080" align="center" valign="bottom">March</td>
<td bgcolor="#800080" align="center" valign="bottom">April</td>
<td bgcolor="#800080" align="center" valign="bottom">May</td>
<td bgcolor="#800080" align="center" valign="bottom">June</td>
<td bgcolor="#800080" align="center" valign="bottom">July</td>
</tr>
<tr>
<td bgcolor="#C0C0C0" valign="bottom">Year</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2004</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2005</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
<td bgcolor="#C0C0C0" align="center" valign="bottom">2006</td>
</tr>
<tr>
<td bgcolor="#FFFF99" valign="top">FUELtrac index value</td>
<td align="right" valign="top">99.81</td>
<td align="right" valign="top">106.55</td>
<td align="right" valign="top">109.78</td>
<td align="right" valign="top">112.81</td>
<td align="right" valign="top">112.81</td>
<td align="right" valign="top">104.98</td>
<td align="right" valign="top">103.93</td>
<td align="right" valign="top">106.45</td>
<td align="right" valign="top">114.90</td>
<td align="right" valign="top">119.31</td>
<td align="right" valign="top">113.88</td>
<td align="right" valign="top">119.65</td>
<td align="right" valign="top">122.79</td>
<td align="right" valign="top">123.66</td>
<td align="right" valign="top">130.97</td>
<td align="right" valign="top">131.38</td>
<td align="right" valign="top">122.07</td>
<td align="right" valign="top">120.99</td>
<td align="right" valign="top">128.83</td>
<td align="right" valign="top">131.74</td>
<td align="right" valign="top">133.93</td>
<td align="right" valign="top">140.24</td>
<td align="right" valign="top">142.50</td>
<td align="right" valign="top">141.06</td>
<td align="right" valign="top">138.55</td>
</tr>
<tr>
<td bgcolor="#FFFF99" valign="top">FUELtrac - % change from July 2004</td>
<td align="right" valign="top">0.00%</td>
<td align="right" valign="top">0.03%</td>
<td align="right" valign="top">6.78%</td>
<td align="right" valign="top">10.02%</td>
<td align="right" valign="top">13.06%</td>
<td align="right" valign="top">13.06%</td>
<td align="right" valign="top">5.21%</td>
<td align="right" valign="top">4.16%</td>
<td align="right" valign="top">6.68%</td>
<td align="right" valign="top">15.15%</td>
<td align="right" valign="top">19.57%</td>
<td align="right" valign="top">14.13%</td>
<td align="right" valign="top">19.91%</td>
<td align="right" valign="top">23.06%</td>
<td align="right" valign="top">23.93%</td>
<td align="right" valign="top">31.26%</td>
<td align="right" valign="top">31.67%</td>
<td align="right" valign="top">22.34%</td>
<td align="right" valign="top">21.26%</td>
<td align="right" valign="top">29.11%</td>
<td align="right" valign="top">32.03%</td>
<td align="right" valign="top">34.23%</td>
<td align="right" valign="top">40.55%</td>
<td align="right" valign="top">42.81%</td>
<td align="right" valign="top">41.37%</td>
</tr>
</table>

The query to be refreshed in the other workbook is called "Fuel Adjustment Total Litres - west"

Legacy 98055 · Sep 26, 2006

Hi AJM. You are going to need a VBA code example to perform this. In order to get an example, you need to provide all of the details. I appreciate the time you have taken to describe what you need so far but I still have a couple of questions.

When is this automatic refresh going to take place? Every time the workbook performing the query is opened? When the user clicks on a button or runs a macro?

Do any of your queries parameters vary or are they always the same? If they change, please provide details.

Would it be ok to email me the two workbooks and/or post them here for others' input?

ajm · Sep 28, 2006

have sent one workbook to your email address. Its a cut down version and actually incorporates the relevant sheets from my two main workbooks. Hope its clearer when you see it.

trigger MS Query Refresh on population of partic. cell

ajm

Well-known Member

Excel Facts

Legacy 98055

Guest

ajm

Well-known Member

Legacy 98055

Guest

ajm

Well-known Member

Similar threads

Forum statistics

Share this page

trigger MS Query Refresh on population of partic. cell

ajm

Well-known Member

Excel Facts

Legacy 98055

Guest

ajm

Well-known Member

Legacy 98055

Guest

ajm

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock