Solved

Excel Cell Calculation/Update Problem

Posted on 1998-11-05
13
381 Views
Last Modified: 2006-11-17
I'm using a spread sheet that has numerous records that using "Vlookup" refers back to a main page to get common data and change the cell value accordingly. A problem occured recently where I have to now double-click the cell to make it update. I've tried clicking F-9 with no avail. Is there a way to correct the problem, or a function that I'm missing?
0
Comment
Question by:sanctified
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 1

Expert Comment

by:ajmendes
ID: 1614332
Hi,
try to be more specific, because i did't understand your problem.

Bye
0
 

Author Comment

by:sanctified
ID: 1614333
When I update the master sheet that the "Vlookup" commands are looking to, upon the change the cell registers a "#N/A" error. But, once I double click the cell it will update. Is there a refresh option that I'm missing?
0
 
LVL 1

Expert Comment

by:ajmendes
ID: 1614334
Hi,
i want to know what kind of data are you updating. Are you update the Key field?
Did you already see what kind of Calculation is set (TOOLS|Options, Calculation tab).

Bye

0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:sanctified
ID: 1614335
The calculation is set for "Automatic"

Here is what the spreadsheet looks like:

PART          DESCRIPTION          PRICE

9813456-0001  WIDGET 5.0           (VLOOKUP to find common price)

Since the Widget 5.0 is used on other assemblies, I use the vlookup to find it. I'm exploring other options such as the "GoGet" command. But this is all I have for now.
0
 
LVL 1

Expert Comment

by:ajmendes
ID: 1614336
Hi,
the VLOOKUP command have several parameters:
- Lookup_value: value of PART field in destination table
- Table_array:  the "Price's" source table
- Col_index_num: 2 ("Price" order column order)
- Range_lookup: TRUE

I imagine that your "Price's" source table have this layout
  PART      PRICE

I've tried several situations with this scenario, with several settings on Calculation tab, but I couldn't repeat your problem, it worked every time.

Bye.
0
 
LVL 1

Expert Comment

by:batdan
ID: 1614337
I have had this problem on several occasions before, but never actually managed to overcome it without using a VBA workaround.  (If you are interested, it's not pretty but it's not much code).
Are you getting any of your data from elsewhere? i.e. Copied from a Lotus spreadsheet.
0
 
LVL 1

Expert Comment

by:knutt
ID: 1614338
Try turning "Automatic" calculation off. Go out of the dialog box and back to the Excel sheet. Then turn it back on. Can you create a new sheet with a simple vlookup that works? If so, try copying data from your problem sheet into a new one and recreate a new sheet. (There is, sadly, some situations where Excel sheets refuse to do what you want it to do. They crash, so to speak.)
0
 
LVL 1

Expert Comment

by:ajmendes
ID: 1614339
Hi,
i'm getting data allways from Excel worksheets.
One other thing that i thought is if your PART field has the same data in both places, because in your comment you show to me this field formatted as i said to, but your data don't have really the "-" character, so you must garantee that in your source table this field does't have also this character.

Bye
0
 

Author Comment

by:sanctified
ID: 1614340
I'm importing data from ASCII (almost like .dbf format) files. These files are Btrieve generated tables. Once I convert the text to columns using the Excel wizard, the new Book1 file generates a type of text that you can implement changes, but to actually have the changes take place I have to double click each individual cell to reflect the new format. I tried a VB code made to a workbook macro to refresh, still no change. Changing the font, and copying and repasting as "value" changes nothing. There's a way to do anything.
0
 
LVL 1

Expert Comment

by:batdan
ID: 1614341
OK, this may sound strange but give this VBA code a try anyway:

To be able to have the formats without you having to edit each cell manually, you have to set the range of cells to be equal to itself.  i.e.

Worksheets("Sheet1").Range("A1:D6").Value = Worksheets("Sheet1").Range("A1:D6").Value

This should sort out your formatting problems.
Let me know if this helps........
0
 
LVL 5

Accepted Solution

by:
vboukhar earned 70 total points
ID: 1614342
Try this - I met such problems too:
Sub Numrefresh()
  For Each c In ActiveSheet.UsedRange
    c.Formula = c.Value
  Next c
End Sub

0
 

Author Comment

by:sanctified
ID: 1614343
Thanks.

Nice try batdan, but simply transferring the values did not affect the outcome nor the effects.
0
 
LVL 1

Expert Comment

by:batdan
ID: 1614344
Bugger!  Hats off to vboukhar....catch you later.

:-)
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question