Calculate or Refresh without scrolling

[biggsy3]

Hello All,

I am having a bit of a problem getting excel to calculate or refresh without scrolling.
In my worksheet, I have a cell that contains a drop down list. The list contains the weeks of the year not including the holiday months, which is referenced from a parameter worksheet.

When I choose one of the values (week numbers) in the drop down list, the three cells below format to the corresponding week numbers.

However, If I choose Week 1 in my drop down list, I want the cells below to refresh automatically and instantly.

At the moment im having to scroll down and back up on the worksheet or refresh the sheet manually, which isnt very user friendly. My calculation options are set to automatic.

Can somebody tell me what I need to do so that my number values will ALWAYS update quickly, every single time I switch to another week on my list?


Just incase it is a problem with my formula I have pasted it here:

This is in the first cell below the dropdown list.

=CHOOSE(MATCH(C10,Params!K:K,0),Params!K2,Params!K3,Params!K4,Params!K5,Params!K6,Params!K7,Params!K8,Params!K9,Params!K10,Params!K11,Params!K12,Params!K13,Params!K14,Params!K15,Params!K16,Params!K17,Params!K18,Params!K19,Params!K20,Params!K21,Params!K22,Params!K23,Params!K24,Params!K25,Params!K26,Params!K27,Params!K28,Params!K29,Params!K30,Params!K31,Params!K32,Params!K33,Params!K34,Params!K35,Params!K36,Params!K37,Params!K38,Params!K39,Params!K40,Params!K41,Params!K42,Params!K43,Params!K44,Params!K45,Params!K46,Params!K47,Params!K48,Params!K49,Params!K50,Params!K51)

Perhaps I need to simplify, any help or advise would be much appreciated.

Regards

Richard.

 

[helpexce]

May be it is taking some time to calculate and update and not because of the scrolling if the calculation setting is set to automatic.

 

[biggsy3]

Yes, I see now.... the calculation takes about 30 seconds to update the cells automatically without scrolling, Its just it seems to update the cells instantly if I scroll down. So I was wandering, perhaps I could reduce the time of the calculation by using a diffferent formula?

 

[ukmikeb]

Hi

Re the formula, perhaps :-

=INDEX(Params!$K$2:$K$51,MATCH(C10,Params!K:K,0),)

hth

 

[biggsy3]

Mike thankyou for your reply,

This most defiantly seems to simplify the formula, however im still having the same issue where it seems to take a long time to calculate. But you seemed to have ruled out the possiblity of it being the formula slowing the calculation down.

Richard