how to auto refresh excel worksheet?

Question

I am having a real rough time getting excel to do what I want it to do....
In my worksheet. I have a cell that contains a drop down list. The drop down list has 4 values: "x" "x.x" "x.xx" "x.xxx" and "select one..."

When i choose one of the three x values, i want another cell (that always has numbers in it) to format to the corresponding number of decimal places.

So. If i chose "x.xx" in my drop down list, I want my number cell (lets say it contains the number 3) to show "3.00". I want to be able to change the drop down list and have my number update automatically.

Here's my problem...

I have setup all the conditional formatting, and i have gotten this to work mostly the way i want it to. But sometimes when i switch between "x", "x.x", etc. My numbers do not update until i either A. scroll down on the sheet and then scroll back up, or B. I push F2+Enter a few times until the worksheet refreshes..

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

Also, another user mentioned i should use the following macro code:

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

I used this, and i still have the same problem.

Answer 1

Please make sure your Calculation is not set to Manual.



Click on "Formulas" Tab -> Calculation Options and make sure it's set to Automatic.

I could recreate what you were describing by changing my Calculation to Manual.



If this not the case then most probably this is a bug and you will need to force the sheet to recalculate.

In the VBA Editor (alt+F11) click on the Sheet that contains above described formatting and paste this code.



Private Sub Worksheet_Change(ByVal Target As Range)

Calculate

End Sub



This will force the sheet to recalculate.

Answer 2

Refresh Excel Spreadsheet

Answer 3

I don't know if this is what you are looking for, but this is the only explanation I could find without using Pivot tables. Refreshing data: Automatically refresh data when a workbook is opened You can refresh an external data range automatically when you open the workbook, and optionally save the workbook without saving the external data, so that the workbook file size is reduced. Click a cell in the external data range you want to refresh, and then click Data Range Properties on the External Data toolbar. Select the Refresh data on file open check box. If you want to save the workbook with the query definition but without the external data, select the Remove external data from worksheet before saving check box. Refresh multiple external data ranges: If your worksheet contains more than one external data range that you want to refresh, on the View menu, point to Toolbars, and click External Data. On the External Data toolbar, click Refresh All to refresh all of the external data ranges in the workbook. If you have more than one workbook open, you must click Refresh All in each workbook to refresh external data. Refresh data in an imported text file: Select the worksheet with your imported text file. Click Refresh Data on the External Data toolbar. In the Import Text File dialog box, select your text file, and then click Import. Tip: If you want to import only part of the data in a text file as a refreshable data range, you can create a query to retrieve the data. For more information, see Microsoft Query Help. Automatically refresh data at intervals: Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar. Select the Refresh every box, and then enter the number of minutes between refreshes. Control the order of refreshes Click a cell in the external data range you want to refresh first, and then click Refresh Data on the External Data toolbar. Repeat for each external data range until they are all refreshed in the order you want. Setting refresh options: Require a password to refresh an external data range If your data source requires a password to connect to it, you can require that the password is entered before the external data range can be refreshed. This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy). Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar. Under Query definition, clear the Save password check box. Notes: Stored passwords are not encrypted. Microsoft Excel prompts for the password only the first time that the external data range is refreshed in each Excel session. The next time you start Excel, you will be prompted for the password again if you open the workbook that contains the query, and then click Refresh Data . Preserve cell formatting when you refresh an external data range Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar. Do one or both of the following If you want to preserve cell formatting that you apply, select the Preserve cell formatting check box under Data formatting and layout. If you want to preserve column widths that you set, clear the Adjust column width check box under Data formatting and layout. Click OK. To refresh the external data range, click Refresh Data on the External Data toolbar. Run a query in the background or while you wait Running a query in the background allows you to use Microsoft Excel while the query runs. Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar. Select the Enable background refresh check box to run the query in the background or clear it to run the query while you wait. Notes Stop a query To stop a query from running when Enable background refresh is turned off, press ESC. Stop a background refresh To stop a query that is running in the background, double-click the Background Refresh icon on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh. Recording a macro While you are recording a macro that includes a query, Excel will not run the query in the background. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True". For information about editing macros, see Visual Basic Help. Note You can check the status of a refresh by clicking Background Refresh in the status bar. To cancel the query, click Cancel Refresh on the External Data toolbar.