Excel Function Output not updating

Hi All!

Apologies to make my first post a question but I am more than planning to make up for that....

I have a problem in a small VBA function, the purpose of which is as follows:

Say you have an Excel wookbook with 4 sheets, named UK-1, GR-2, UK-3, and CONSOLIDATE. The function will only be used in the CONSOLIDATE sheet. Basically the aim is have the value of a cell in the CONSOLIDATE sheet to be the conditional sum of the cells in the same location on the other sheets. Best illustrated by example:

Say the cell $A$3 in UK-1 is 4, $A$3 in GR-2 is 100, and cell $A$3 in worksheet UK-3 is 2000. If on the CONSOLIDATE sheet in cell A3 you use the function =CONSOL("UK"), then the value of A3 on the CONSOLIDATE sheet will sum up all the values of $A$3 on the other sheets, as long as the first two letters in the name of the worksheet are "UK". So if you used =CONSOL("UK") in this example the value of $A$3 on the CONSOLIDATE sheet would be 4 + 2000 = 2004. If you changed the function to =CONSOL("GR"), the value returned in cell $A$3 in the CONSOLIDATE sheet should be 100 (note that you avoid circular references by not naming the sheet where the function is written starting with "UK" or "GR").

Code is as follows:

Please Login or Register  to view this content.

This formula appears to work except for two apparant issues. Firstly (following on from example above), if you change the value of cell $A$3 on one of the "feeder" sheets, and click back to the CONSOLIDATE sheet, then the CONSOLIDATE sheet does not reflect the change made unless you press F2, then RETURN/ENTER on the cell. Is there any way to get the function to automatically update if you change one of the feeder cells?

Secondly, you should be able to use this exact same formula in a number of cells on the CONSOLIDATE sheet, all off which should return different values (as long as the conditional sums are not equivalent). For example if you write =CONSOL("UK") in cell $A$3 and drag that across to cell $A$4 (so that cell $A$4 is also =CONSOL("UK") ), the output in cell $A$4 should be different to the output in $A$3 as the function references different cells on the "feeder" sheets. However if you make this drag, then it just fills in the same figures on all cells. Now again, if you press F2, then ENTER on a particular cell, it updates to what you would expect would be the correct output based on the cell location, but it doesn't make this update automatically. Is there any way to get around this?

I am not sure if the general format of my formula is just generally unstable and poorly coded hence I am having difficulties or there is a reason the automatic update is not occurring. But any feedback would be appreciated .

Many Thanks,
Aj.

Hi , Try adding the following at the start of your code.

Please Login or Register  to view this content.

Regards Mick

application.volatile doesn't work