HPVAL not working in Excel XP

[MF in Asia]

HI!

Is there any way to get the function HPVAL to work in Excel XP.

We have spreadsheets with this function which works fine in EXCEL 97. Now that we are on EXCEL XP, it shows NAME? errors.

Is this a known bug, or did we miss something in the installation. All Add-Ins have already been loaded!

Any ideas? Thanks

 

[Andrew Poulsom]

Is this a Hyperion function? If so you need to install the Hyperion Add-In, although I don't know if it works in Excel XP.

 

[MF in Asia]

I think it is. But the files are sent to us with the formulas already done. We can see the formulas, it goes like this:

=HPVAL($C16&$B$7,E$1,E$3,$B$3,E$2,$B$1)/$B$6*0+2170.328

The same spreadsheet in Excel 97 opens up without any problems and shows the numeric value. Both the Excel97 machine and Excel XP does not have the Hyperion add-in loaded. THey also both do not have access to the Hyperion system, they are standalone.

Ii have tried this in Excel2000, it shows the same problem as Excel XP.

Any ideas?
This message was edited by MF in Asia on 2002-10-08 06:34

 

[Andrew Poulsom]

Could it be that you need to disable links? Tools, Options, Calculation tab, uncheck "Update remote references". Or Tools, Options, Edit tab, check "Ask to update automatic links". Choose no when prompted.

 

[MF in Asia]

Thanks. I was thinking the same and did try setting it on the document itself but it did not seem to take any effect.

Using this track, i tried the other settings and got it to work!!

It has to do with Manual Calculation. But if you try it on the sheet with the data it does nothing. No change although Manual Calculation is set.

Whenever the sheet is loaded it seemed to take the default settings on my Excel which was Automatic or probably the setting which the sheet itself was saved.

Finally in order to get it to work, I had to load a spreadsheet, any spreadsheet ..turn calculation to Manual, save it. Keep the sheet up in a Window, Load the original one that had all the formulas, then this worked and the numeric data appeared. When looking at the settings for Calculation, this then showed as Manual.

Weird, that I need to load another sheet first with the correct setting. Do you think this is an Excel XP /2000 quirk?

 

[Juan Pablo González]

I think that the Calculation mode isn't saved with the workbook, but "taken" from the "current" Application state, that is, if you're on automatic calculation, the workbooks that you open will be in automatic too. That's why you need to first set it to manual.

 

[MF in Asia]

You are right there. For some reason, the few installs that I have seen for Excel XP and 2000 are always automatic.

But in order to make the spreadsheet show its numeric value, I always have to have something else loaded even a blank sheet which has Manual Calculation set before this one will work.

It still does not explain why the sheet can be viewed in Excel 97 (with the automatic recalc setting.

We were also told that by Microsoft that we needed to have the Hyperion HPVAL add-in to make the sheet viewable but I have tried it in a few other Excel 97 systems which does not have any Add-in and it still views OK.

I think it might have something to do with the way formulas are calculated in Excel XP / 2000.

If you wish, I can send along a sanitised version of the sheet.

Thanks.

regards.