vlookup doesn't always work in this situation



  • 1. How to convert a series of numbers
    I have received an EXCEL 2003 worksheet with a column that contains many zip codes, improperly formatted. The cells contain, for instance: 10751746, but I want 01075 10271234, but I want 01027 10359999, but I want 01035 How can I convert the numbers above to give me the numbers I want as indicated above? I will appreciate advice.
  • 2. Color detection
    Good day. I am using Excel 2002 with Windows ME. I have created a worksheet for ordering products. Each of the cells that needs to be cleared to start a new order is formatted with a green pattern. These are the only cells that the user will be allowed to modify when the project is done. Is there a way to write a macro that will clear only the green cells on the worksheet? Thanks, Danno...
  • 3. In excel pointers should be availible
    many times you could save repeating formulas if you could change the row you want to reference by pointing to a value in another cell to deisnate the row you wish to refernce in a formula. example if your formula cotained the A(B2) and B2 contained a value of 5 the formula would be refernce A5. If you then change the value of B2 to say 10 the formula would now refence A10.
  • 4. looking for help on....
    I am working on a spreadsheet to analyze data during different periods of time. I am having difficulting creating a formula (tried imbeded if, and, then statements unsuccessfully). How can I easily have a formula search for product results easily between a beginning and ending dates so that I can analyze that information only between the selected dates? The spreadsheet is setup something like this: parameter tab: Beginning Quarter: Drop down of dates (i.e. 4th Quarter 1985) Ending Quarter: Drop down of Dates (i.e. 1st Quarter 1999) Input data tab: Quarters Product 1 Product 2 Product.... 1Q 1980 6.90 7.30 15 2Q 1980 1.5 0.520 -.01 . . . 4Q2004 Out Put tab: Date Product 1 Product 2 Product..... 4Q1985 . . . 1Q1999 Results Std Deviation T-Statistic etc... thank you for the help
  • 5. Can Excel represent formula in textural format with values substi.
    In Excel I would like to display the values that go into a formula - in addition to the answer itself. This is to mimic the way hand calculations would be set out on paper. In other words I'd like a function that takes the maths expression within a cell and returns with a string where the cell references have been replaced with actual values.

vlookup doesn't always work in this situation

Postby VGltIEw » Fri, 27 Feb 2004 04:11:12 GMT

I'm using Access vb to copy an Excel spreadsheet to a new filename, then fill in values from a recordset.  The source .xls has formulas that use vlookup() and match() to find values in a third spreadsheet.  (The values to be looked up and matched are blank until my code fills them in.)

When I open the newly created spreadsheet, I get the msg asking me if I want to refresh the lookups.  If I say NO, then all my formulas containing lookup() and match() work as expected.  If I say YES, then all of them return #NA.  If I say NO then try to edit one of the formulas, that formula turns to text.

Any suggestions?  Thanks.

Similar Threads:

1.precision as displayed doesn't always work

I need the number in the formula bar to show the same number as is displayed 
in the spreadsheet.  I used number formatting to set up my data to 2 decimal 
places.  Then I set precision as displayed.  I even tried it by setting 
precision as displayed first then used number formatting to 2 decimal places. 
 Either way, not all the numbers get set to 2 decimal places.  Some do and 
some don't.  For example, I input a value of 0.26 and the formula bar shows 
it as 0.259999999999 but my input value of 0.25 shows as 0.25 in the formula 

2.worksheet change doesn't always work

I have noticed that the worksheet change doesn't always work,
expecially when I have been working on excel for a while and have
opened and closed a lot of workbooks,or if I have used VBA help alot...
if I reboot the computer and open a workbook that has a worksheet
change event it works fine, is there a way around this besides
rebooting the computer,
Here's the code I am using for one of the sheets: but I am sure it is
not the code because I have noticed this behaviour in othe workbooks...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
    ActiveCell.FormulaR1C1 = "=ROUNDUP(RC[-1]/8.0625,0)"
Application.SendKeys "%{down}"
End If
If Target.Address = "$C$5" Then
Application.SendKeys "%{down}"
End If
If Target.Address = "$D$5" Then
Application.SendKeys "%{down}"
End If

If Union(Range("$b10:$b12"), Target).Address =
Range("$b10:$b12").Address Then
    UserForm2.Show False
    myRow = Target.Row
  End If

End Sub

3.Using find- doesn't always seem to work

I have a number of sheets in an excel document. I'm 
searching for a particular name that I know is in there. I 
select all sheets (after right clicking on a sheet tab) 
then press control f. It comes back with the response that 
the word cannot be found.
Now most of the time it will find it but inexplicably 
sometimes it won't. (correct spelling and punctuation is 
being used)

4.ChDir doesn't always work

Is it in quotes
chdir "yours"

5."ComboBox1.ListIndex = 0" Doesn't seem to always work.


I have "ComboBox1" on a UserForm. In the Sub Form Initialize procedure, I  
have "ComboBox1.ListIndex = 0" hoping to set the ComboBox to Index 0 every 
time the Form is loaded. The problem is that maybe 50% of the time it loads 
up with the last selection instead. Am I doing something wrong?

Thank you for your help!

6. Why doesn't Selection.End(xlDown).Select always work?

7. status update during program run -- DoEvents doesn't always work

8. Linking with equals sign doesn't always work

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 43 guest