Solved

Excel Cell Calculation/Update Problem

Posted on 1998-11-05
13
376 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

776 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