Skip to Content
0
Former Member
May 29, 2008 at 07:32 PM

1 of 2 Hlookup tables do not work in Xcelsius 4.5 but does in Excel

44 Views

Post Author: ShaneXL

CA Forum: Xcelsius and Live Office

Hello all,

First post on something that is driving me crazy.

I may not be tackling this the best way, but I am using Hlookup to dynamically update a chart based on what a user clicks within the Xcelsius dashboard.

So a user clicks on a product, it sets a flag in excel notifying it to dynamically generate a chart that is used by Xcelsius to build a graph for each subproduct. The raw data in excel is messy something like:

PRODUCT1 May June JulySub product 1 3 7Sub product2 3 6 23PRODUCT2 9 2 7Sub product 1 1 1Subproduct2 2 5 9

Now, I have 2 HLOOKUP statements, 1 builds the x values or labels of each subproduct which works fine in both excel and xcelsius, the other builds the actual data which works in excel but NOT xcelsius.

The difference between the 2 lookups is as follows:

Works in excell and Xcelsius: =IF(HLOOKUP(A$7,F$45:I$62,2)=0,"",HLOOKUP(A$7,F$45:I$62,2))Works in excel but not Xcelsius: =IF(A49="Product1",HLOOKUP(B81,A9:N26,2),IF(A49="Product2",HLOOKUP(B81,A28:N32,2),IF(A49="Product3",HLOOKUP(B81,A34:N36,2),IF(A49="Product4",HLOOKUP(B81,A38:N39,2)))))

I am guessing it has something to do with the messy formula, but again it works fine in excel.

Checking the docs, all the functions I am using are supported. I saw another posting saying that VLookups don't work unless you give all arguements which I tried, and still no luck. I even tried having another set of cells that just point to the ones doing the calculations in hopes that it simplified the work for Xcelsius but no luck either.

Any pointers?

Thanks,Shane