Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Cell Calculation/Update Problem

Posted on 1998-11-05
13
Medium Priority
?
383 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 280 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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

704 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