HPVAL not working in Excel XP

MF in Asia

New Member
Joined
Oct 7, 2002
Messages
4
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 7, 2002
Messages
4
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 7, 2002
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

New Member
Joined
Oct 7, 2002
Messages
4
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.
 

Forum statistics

Threads
1,143,640
Messages
5,719,984
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top