Weird vlookups and formulas on large sheets

[Devnull]

Hi all,

Recently a problem came up with one of my Excel worksheets that so far
I´ve been unable to figure out.

We have an excel sheet which we send to our suppliers when we require
quotes. They fill it out and send it back to us, then we gather them
all up and compare prices.

The basic process is this:

1. Supplier sends us supplier1.xls
2. We open it, and copy the invoice sheet over to comparison.xls
3. The process is repeated until comparison.xls contains supplier1,
supplier2, supplier3, etc. as sheets.
4. We define for each sheet range names, for example supplier1_matrix
for supplier 1´s quote, supplier1_codes for the inventory code column,
etc.
5. Then we simply define a series of vlookups and sumifs in order to
transfer the numbers to the comparison sheet.

But, here´s the problem....

Once the comparison sheet is set up and the formulas are all in place,
I get a bunch of N/A´s on my vlookup cells. On those same lines, the
total column, which uses a SUMIF, displays the total correctly.

If I go back to the supplier1 tab on the comparison worksheet, and
manually type over the inventory code with the same value (for example
the code says "1.001" and I type in "1.001" in the same cell), the N/A
for that line disappears and the line displays correctly.

So I have to go typing over each inventory code in order to get the
comparison sheet to work. It doesn´t matter if I close and reopen,
save, reboot, whatever.. until I type over the code the vlookup
doesn´t work.

Also, if there´s a formula (for example if to number the lines I use
"1... =A1+0.1... = A2+0.1... etc") there´s no way vlookup works. I
have to type over the formula to get it to work.

I´ve even tried copying the cells and pasting the values over
themselves to remove the formulas... nothing.

Any ideas? The worksheet´s only moderately big (about 350 lines per
sheet, x 4 sheets), so I don´t think I´m anywhere near Excel´s
maximum size limits for ranges and functions.

Mario

 

[PeterAtherton]

Mario

I'm guessing that the lookup references 1.001 and so on are formatted as
numbers on some sheets and text on the others. You could try this macro it
will format the selection as number or text -- it may save you having to
re-enter the numbers

Sub checkFormat()
Dim ans, c, Msg, Title, Style
Msg = "Is the selection number format?"
Title = "Text or Number format"
Style = vbYesNo + vbCritical
ans = MsgBox(Msg, Style, Title)
Application.DisplayAlerts = False
If ans = vbYes Then
For Each c In Selection
Selection.NumberFormat = "0.0000"
c.Select
c = Trim(c)
c = c * 1
Next
Else
Selection.NumberFormat = "@"
End If

End Sub

Regards
Peter