Solved

Excel Cell Calculation/Update Problem

Posted on 1998-11-05
13
375 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now