Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel Cell Calculation/Update Problem

Posted on 1998-11-05
13
Medium Priority
?
386 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

595 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