Refreshing results of custom formulas

[malcmail]

I've been playing around with grabbing information from web pages to help me out with somethign I am doing. Basically the workbook drags in a table from a webpage to give stats for each month over a number of years. I then have custom function that is a lookup. Given two variables provided by the user (or by the sheet itself) it grabs the relevant data. It works by cycling down the years till it finds the right one, then cycling the months and returning what is in the intersection. Works like a charm, except...

If I refresh the external data the cell with the answer remains the same until I go into it to edit it then hit return. Refresh all doesn't work. Is there a way I can force the cell to recalculate? At the moment it is just one cell but once I get that working it is likely to be lots of cells (in no particular location).

[xlnitwit]

It would be helpful to see the code for your custom function, but most likely you can just add

Please Login or Register  to view this content.

to the start of it.

[malcmail]

Found an answer that I'm managed to butcher. Ctrl+Alt+f9 seems to work for anyone landing here in future (including probably me!)

[malcmail]

Thanks for that. I seem to recall there maybe being some issues with that bit of code. Or is that something blown out of all proportion?

[xlnitwit]

Could you be a little more specific? It makes your function volatile, which appears to be necessary. That in itself makes me think the function could be written better.

[malcmail]

Nothing specific springs to mind, just remember seeing that bit of code before and the advice was not to use it. It works for what I need but I'd be interested to see if my code is any use or not. And in the interest of learning:

Please Login or Register  to view this content.

As background DC takes a variable (which in this situation would be the month - but ti has applications beyond that) and a year. The table is on sheet2 and is a simple case of years along the top months down the side. It was the simplest way I could think of doing it to be honest. I'm no coder, just someone who has dabbled with VBA / Excel for far too long!

[xlnitwit]

You aren't passing the lookup table to the range, so your function would have to be volatile. It looks to me as though a simple INDEX and MATCH formula would do what you need, and do it quicker.

[malcmail]

I'll have to use application.worksheetfunction to get those to work, yes? I'd had issues with that before (Again a while back) but quite happy to give it a whirl instead (it was what I intended using until I remember previous problems).

INcidentally when you say aren't passing the lookup table to the function.... do solve this do i name the table and then pass it as a third variable for instance? Then swap out any mention of the worksheet for the table?

[xlnitwit]

No, if you are using worksheet formulas, just use INDEX and MATCH; no need for Application.Worksheetfunction

You don't have to name the table, you just need to pass it as a range to the function.

[malcmail]

AH you meant use INDEX and MATCH on the sheet itself rather than in the formula. For the purposes of future development that won't work for me I don't think. But I think I can work with the passing the table as a solve to the problem. Well, if I can get it to work of course! Many thanks for your help. Much appreciated.