Free Microsoft Excel 2013 Quick Reference

shared workbook does not automatically update

i have a shared workbook that is set to automatically update every 5 mins. however after 5 minutes guess what? nothing happens! what's up with this crap.


I work with big excel files (almost 7MB), with most of the worksheets linking
back and forth. I have discovered that sometimes when I change a cell on a
worksheet, it does not automatically update the cells on the linked
worksheets. The only way to do it is to go to each dependent cell
individually to update, this is very time consuming. I have double checked
that the automatical update is checked in the options. Has anyone
experienced this and does anyone have a solution? Thanks.

I have a sheet with a cell that is an HLOOKUP, however this does not automatically update whenever you open the file, and instead you need to highlight over the cell and hit the ENTER key and then it will update.

Any help, thx

=HLOOKUP(D$2,'Network_Address[Last_Name.xls]Sheet1'!$E$3:$BD$23,21,FALSE)

I have 3 or more users working in a shared Excel 2003 workbook, but a one of
the users does not see any data displayed on the worksheet. However, data is
displayed in the formula bar as the cell pointer moves down or across
rows/columns.

I've perused the KB, but haven't come up with anything yet.

Has anyone had this experience, and if so, were you able to resolve it?
I’ve not had the experience myself and my attempts to replicate the issue
have failed.

--
Kevin Backmann

In writing VBA code for an Excel Application, the following problem has
occurred:
The command, "Application.Calculate", does not always update or recalculate
the formulas in the cells of the workbook.

To compound this problem the users of the Excel Application are using
several different versions of Excel and VBA. VBA code will need to check
the version of Excel and VBA before executing a command (i.e. using
Application.Version and Application.VBE.Version)

Present documentation shows the following:
* F9 - recalculates all of the data in the open workbooks
(Application.Calculate)
* Shift+F9 - only calculates data in the specified worksheet
(ActiveSheet.Calculate)
* Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open
workbooks (Application.CalculateFull)
* Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the
open workbooks after checking the dependencies between formulas
(Application.CalculateFullRebuild)

The following information is needed:
* What is the best way to handle this issue over the various versions of
Excel (backward compatibility)?
* What version of Excel and VBA correspond to the previous commands (i.e.
Application.CalculateFullRebuild was introduced in Excel 2003 or Excel
Version 11, VBA Version ??).

Hello.

I did a search for this question, and came up with a post, however the response did not make sense to me, so I am posting my question new (rather than replying to the other, since it was from 1+ years ago).

I know next to nothing about VBA (yet). I found the code I am using from this website (and altered it a bit), and it's purpose is to sum the cells that have a particular font color format. It is:


	VB:
	
 Range) 
     
     ''''''''''''''''''''''''''''''''''''''
     'Written by Ozgrid Business Applications
     'www.ozgrid.com
     
     'Sums cells based on a specified fill color.
     '''''''''''''''''''''''''''''''''''''''
    Dim rCell As Range 
    Dim iCol As Integer 
    Dim vResult 
     
    iCol = rColor.Interior.ColorIndex 
     
    For Each rCell In rSumRange 
        If rCell.Font.ColorIndex = iCol Then 
            vResult = WorksheetFunction.Sum(rCell) + vResult 
        End If 
    Next rCell 
     
    SumColor = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When cells within the range are given a particular color, the function does not automatically update; it requires me to double click on the cell w/ the function. The only time it does auto update is when I subtract a cell with a particular color.

How can I automate this function, just like all the others that are built into Excel act?

Here is the forum post from last year with the answer I do not quiet understand.

http://www.ozgrid.com/forum/showthre...pdate+function

...how do I incorporate 'Application.Volatile' in the above code?

Thank you for your time & help...

Whe I double click the abc.xls file, excel opens and I can see the file name
on the title bar but the workbook does not open.

Can some one help?

Situation: We have an Excel file (master file) on a network that needs to be updated by three different users; in three different locations; at three different PCs. The maste file updates automatically every 10 seconds based on some code in the master file. However, the shared workbook function in Excel (tools/share workbook) limits the shared automatic updates to every five minutes (at the least). (This is seen by going to tools/share workbook/advanced and then "update changes" and then "automatically every...".)

Problem: The master files needs to be updated with any changes as soon as they happen (or within, say 30 seconds). The master file does this, but the shared function does not seem to allow it (meaning the updates from the "updating" computers would not refresh but every five min).

Let me know if this makes sense or you will require additional information to lend a hand. As always, thanks for your Excel brilliance. I am coming a long thanks to you!

D

Hi

I have a workbook that is not automatically updating/refreshing calculations when I change some data, even links from one tab to another is not working.

In options, calculation = the calculation is set to automatic and F9 does not do anything either.

Any ideas as to why this is happening and any solutions would be great.
Thanks
J

Hi there,

I have created a workbook to record the number of cases we received for each issue we deal with to make it easier to make my departments weekly reports. I sent out reporting workbooks to my colleagues who fill them in and send them back daily, these are all referenced to and the values are automatically updated into the master sheet.

At the end of the week I thought that I should be able to delete that weeks values and just add the new weeks sheet and it would be the same.

The problem I have is that if a colleagues workbook is deleted and there is not another one there to replace it, it seems to retain the information and does not clear the value.

The formula I am using is:

=IF(ISERROR(SUM('dunoffice01DATAshareCommercialClient ManagementNew Business-Business PartnersCurrent ClientsNew MediaKana Reportsworking weekFriday[Kana Caroleann.xls]Errors'!C2)), 0,SUM('dunoffice01DATAshareCommercialClient ManagementNew Business-Business PartnersCurrent ClientsNew MediaKana Reportsworking weekFriday[Kana Caroleann.xls]Errors'!C2))

The purpose of this formula is to stop me getting errors when the workbook is not present and I thought to substitute a zero if the workbook was no longer present but it doesn't seem to work that way. It stopped the error I was initially getting but does not put in the zero value if the workbook was there and then was deleted.

Any ideas?

Excel Share Workbook feature does not booted users

Hi All,

There is a very irritating problem with Excel 2003's shared workbook feature. There is this excel file which has this feature on for allowing multiple users to edit the file. The problem is Workbook does not kick the users off its lists when users exit the program and this result in duplicate users on it. When i click on "Share Workbook" button, it shows a long list of entries with the same users name appearing over and over again.

Is there anyway to solve this problem as I believe this is the main cause of frequent corruption that is happening on this file.   

Please refer to this link for example
http://img8.imageshack.us/img8/8713/usersinworkbook.jpg

Hello.

I have a sheet that collects data from pre-defined cells in workbooks that are on a network drive.
When I open the sheet that collects the data the update-or-not question pops up and I choose update.
Still, the values are not updated.

I press F9 to calculate manualy but that does not update the values.

When I select one of the not updated cells and press F2 and then enter the cells updates. Very quick so it seems like the actual value is in RAM memory.

Is there a setting in preferences I must change or do you know any other solution?

/Anders

Hi all,

I have a spreadsheet which contains links to other workbooks. Obviously when I open it it asks me if I wish to update these links. However, the spreadsheet is part of a report which I want to run automatically every night so I would like a way to write into the VBA code that when the workbook opens to automatically update these links and not ask me. Does anybody know of a way to do this?

Thanks,

Ben

Hi,

I am using a shared Excel 2003 file and needed a way to automatically refresh a pivot table. The file is shared to allow multiple entries from different agents and as such, I noticed that the pivot table in the workbook does not refresh. Any help would be appreciated! Thanks - Reayn

I am using MYOB to export a stock list to Excel. This appears as a list, which is not the format I want - I want it to be in a table format. So the solution I have come up with is to create a table in a new worksheet within the same workbook, which links to the corresponding cells in the first sheet. However, when I paste an updated stock list into the first sheet, the 2nd sheet does not automatically update (I have to press Enter within every single cell to update the value). Is there a way to automatically do this within Excel, or even some kind of VBA script I could run to do it?

Thanks!

I have a very basic understanding of vba but by effective use of sites such as this have managed to paste together some code to create a spreadsheet and reporting functions.

The spreadsheet has records added or updated using a userform either by using the scroll bar or using a find record search.

My problem is that I need to have upto 10 people updating records at the same time(different records) so shared workspace and shared workbook does not suit.

What I would like to do is have the main spreadsheet on a closed workbook.

Use the find process to locate the record in the closed workbook, return it to the userform and then save the updated or new record to the closed workbook.

I have the code to do all of this from the worksheet within the same workbook as the userform but don't know how to link the userform to a closed workbook.

Hi,

For some reason excel has stopped automatically updating formulas when I change the input cells. Changing input cells does not change the output cells, even though the formulas are perfectly fine. Does anyone know what the reason for this may be?

Hi,
I sent out the same file to a large number of users, each had specific
lines on the file that needed to be updated so no changes would be
duplicates. I did NOT send the file as a shared workbook so now I have a
large number of files that do not automatically update the original. Is there
anyway I can update my master file with all of the changes automatically?
--
Thank You,
Steve

Dear All,

I was asked to create a complaints and compliments database in excel with the ability to produce some reporting.

So from the database tab I created various pivot tables. Which, as per the code below refresh upon activation

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("_RespondedInTimeByDept").PivotCache.Refresh
Application.AlertBeforeOverwriting = False
End Sub
Private Sub Workbook_Open()
Worksheets("Home Page").Activate
End Sub

The workbook also contains various other macros.

The problem is that the workbook must be Shared so as that people can access it and update it at the same time.

To my annoyance, and even more so to that of my boss. I have discovered that these macros do not work on a shared workbook.

Does anyone know how i can overcome this.?

Thanks alot

Private Sub CommandButton1_Click()
Call Print_Sheet
End Sub

Private Sub CommandButton2_Click()
ActiveWorkbook.Close SaveChanges:=True
End Sub

Sub QuickHideUnhide()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = Not ws.Visible
    Next ws
End Sub


Hello,
I have a large workbook (50 sheets) that contains a sheet for each employee. I have VBA code that recognizes the user's login and only displays the sheet that belongs to that user. However, to allow all users access simultaneously, I have made it a shared workbook. I receive a Run-Time Error 1004 when the workbook is placed into shared mode and it will not function correctly. However, if I remove the shared feature it runs fine (I just can't have everyone in it making updates).

Any suggesions?

Thanks!

I just discovered my problem lies in protecting the workbooks. I guess you can't have protected sheets in a shared workbook. Does anyone know how to still protect sheets in shared workbooks?

I have a large worksheet that for some reason I can’t share.

- It does not have any lists, that I know of, do not know how to see if there are any
- It does have validation, but I have other workbooks with listed validation that are shareable
- It is not protected
- Does not have any named ranges
- Is not read only
- I have saved it as another file name and no luck there
- I have copied the one sheet I really need into a separate file to try that it did not work,
Any other Ideas?

I have a shared workbook on a network drive. This workbook no longer has the minimize, restore, and close buttons. The workbook does not fill the entire screen and there are no button to restore/maximize it. I don't see any protection on this workbook.

Any ideas?

Thanks,
Julie

Hello all,

I have some data with leading zeros that for that very reason i have to import as text format to Excel. This is part numbers and inventory on hand qty for a retail business.

My issue is - after i import the data I have a VLOOKUP formula that references that same data which will not work because of the text formatting. For some reason the text formatting does not allow the formula to read the data.

I can highlight that column after the import and change the format to general but it does not automatically update the formatting of the column from being text. And if it did, it would delete the leading zeros!

My formula errors until i highlight a cell, edit it manually (f2) and enter. This somehow updates / applies the general formatting and allows the formula to start working (but again, deletes the leading zeros in the process which causes me issues)

Why will my VLOOKUP formula not work with text formatting?? Is there a way around this?

My formula is as follows: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),0)

I am entering the formula into the first sheet of a workbook, with the leading zero / text data in the Sheet2. (it is set up to return a 0 if there is an error) Format of the sheet is: A column = Part Number, B Column = Qty Available

Hope that explains it well enough. I though i knew Excel well, but this one stumped me good.

Anybody think they know the way around this one?

Thanks, Jay

I have an excel 2007 file with worksheets for each month. The Feb-Dec sheets
each have cells that contain the formula "=B25+PrevSheet(F25)" to accumulate
the sum of corresponding cells of earlier month worksheets using a function
"PrevSheet(x)" that I wrote to pick up the specified cell value from the
previous month worksheet. Each worksheet has a fairly large number of cells
that use this formula.

When I make changes in earlier month sheets the following month sheets do
not automatically update their cumulative values. Clicking Recalculate does
not perform and update either. The only way I seem to be able to update
these cum cells is to select each one, click up in the formula bar and then
press enter. This is a very tedious process.

How can I get the cells to update automatically?

Thank you for your help, John

Once you make your Excel workbook "Shared" by selecting "Tools > Share
Workbook", some tools or functions are not available, such as Drawing and
Merge cells. Also, you can delete the workbook while it is open.

I do not think these changes are reasonable or necessary.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc