[Solved] VLookups won't sum

[cephyn]

I have sheets with fields filled with values from VLOOKUP. However, when I try to SUM those cells, it keeps returning 0. It used to work on OO 3.2 - what happened? I'm about to downgrade, because it's completely breaking my sheets! Help!

[FJCC]

A sum of VLOOKUP results works for me on version 3.3. Can you give more details or, better, upload an example? There is an Upload Attachment tab just below where you type in a response.

[cephyn]

A sum of VLOOKUP results works for me on version 3.3. Can you give more details or, better, upload an example? There is an Upload Attachment tab just below where you type in a response.

Each page with a person's name pulls info for the rosters using VLOOKUP. at the bottom of the rosters, there are rows in green, blue and red. This is where sums are failing. They previously worked.

[FJCC]

All the data on the sheets Master and Draflist are actually text strings and not numbers. I can tell because they appear with a leading single quote in the Input Bar at the top of the sheet. The single quote isn't part of the cell content, it is just a sign that the cell contains text. I replaced all of the text with numbers by checking that the cell formats were set to numbers, which they were, and then doing this:
Selecting all of the cells that need to be fixed.
Selecting Edit -> Find & Replace
In the Search For box entering .* ( that is period asterisk)
In the Replace with box entering &
Selecting More Options and checking Current Selection Only and Regular Expressions
Clicking Replace All

This uses Regular Expressions, which are like wild cards but more flexible, to effectively re-enter all of the data. It looks to me like the calculations are now working except for Column P on the DrafList sheet. I can't tell what was intended there. I can't post my version because it is now too big and I have to go. Try to follow the procedure I described and see if you can get it to work.

[cephyn]

Thanks for your help! I think I understand the problem... I will investigate.

Really, thank you!

[cephyn]

Thanks FJCC!

Can you, when you have a chance, explain to me why that regular expression code worked? Or point me to a site that explains regular expressions that work in Calc? I really would like to understand how that worked.

Thanks!

[FJCC]

The help section within OOo has a useful table of regular expressions. You can look under "regular expressions; list of". In the simple one I used, the . represents any character and the * means "zero or more of what immediately precedes the *". The combination .* in the search box results in a search for zero or more of any character, which is the same as saying search for anything.
The & represents in the Replace With box whatever is in the Search For box. Combining & with the .* in the Replace With box, we have a search for any characters and they are replaced with the same characters. This just means that all the cells in the search have their contents re-entered.
The reason this is useful is that once contents are entered in a cell, changing the format of the cell does not change the meaning of the contents. If a cell contains text, changing the format to Numeric does not convert the text to numbers. But, if you re-enter the data in the cell, either with manual typing or with Find & Replace, the new contents will be interpreted using the format.
Somewhere along the line all your numbers were converted to text, though your cells were formatted as numbers. The Find & Replace just allowed re-entering all of the data without a painful amount of typing.
You can do a web search for regular expressions and learn a lot more. I actually hardly use them except for this one trick to re-enter data in many cells.