Replies

Say that your list is in A1:A5.
Enter the following formula in B1:

=IF(A1=0,””,RANK(A1,$A$1:$A$5,1)-COUNTIF($A$1:$A$5,0))

Fill down to B5.

The IF function returns a blank if the value in column A is zero.
The COUNTIF function is used to subtract the number of zeros from the rank.

I thought I had tried that very thing, but I must not have, because it works.

As always, I thank you kindly…

I dont know about Excel 2007. However, in Excel 2003 and prior, I don’t beleive you can do this. A Pivot Table is desiged to work using data supplied by a User. In your case the Hyperlinks are data just like the other data and numbers. When you generate a Pivot Table it is extracting data, not formatted data.

Once a Pivot Table has extracted data the User must tell the new Pivot Table how to format the extracted data. The Pivot Table Tools do not allow for formatting Hyperlinks.

Perhaps VBA could get around this but I would think not.

Regards,

Tom Duthie

Hi Tom,

Thanks for the update. The source that Hans sent applies to Excel 2007 and does state directly that pivot tables do not support active hyperlinks, so if there is a workaround, it would have to be in VBA.

Regards,

Thanks, Hans. I had thought so, but could not find an authoritative source.

Very much appreciated, as always…

OK, I have created a small sample for you to view.

Note that in column B of the data, each cell contains a hyperlink (at least it does here).

Now note that in the pivot table to the right, that field contains text instead of the hyperlink.

Is there any way to have the pivot table show the hyperlink, or is this something that pivot tables just will not do? Again, this question applies to both Excel 2003 and Excel 2007.

And again, thank you for all your efforts. Very much appreciated.

I have a hard time visualizing what you’re talking about – could you attach a sample workbook (with sensitive information removed or altered)?

My apologies, Hans. In my original message I stated that there was “no pivot table”. What I meant to say was there was “no hyperlink, just text”.

Does that make it any clearer?

Hi Hans,

Thank you, as always, for your able assistance.

I’m a little embarrassed to admit that I actually had created similar code myself, but it didn’t seem to work on an instance I found in my data, so I thought I was missing something. It turns out that the code does work on Alt+Enter returns, but in this one cell there is some other kind of return (it’s downloaded data) that the code doesn’t affect.

So there is nothing wrong with the code. The problem is within the data.

Thanks again. Very much appreciated…

I finally got it! Here is what finally worked for me:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Tickets”).CreatePivotTable TableDestination:= _
“‘[” & wb1.name & “]PivotTables’!R3C1″,TableName:=”IBD Table”, DefaultVersion:= _
xlPivotTableVersion10

Thank you for your efforts and for making me focus…

Hi Hans,

Tried your version, but it did not work either. I get Run-time error 438, “Object doesn’t support this property or method.”

Very vexing…

Thanks, Hans. Very much appreciated.

Just a minor addition to Hans’ response:

If you use Excel models that have intentional circular references (and many do in financial institutions), then you should set your calc mode to manual and enable iterations. Some such models will not even open unless these are the settings.

Regards,

Hi Hans,

Exactly what I wanted. Just could not get my brain to function on this one, much appreciate the help.

Regards,

Hi Steve,

Never thought of that. I’ll try it tomorrow when I’m back in the office. That would make the array UDF a lot simpler. The only downside is that the array UDF would not be able to stand on its own in this form. But I really like the idea.

Thanks much for taking a look and for the suggestion.

It makes sense. I had thought that variable required a Redim, but it doesn’t.

Thanks again.

Well, it looks like your guess was on the money, because you did it.

What did you do that I was missing? I looked at your code, but I couldn’t see the difference at first glance. I thought that I was close, but I don’t know why I couldn’t make it work.

Thanks so much for your efforts. You are, once again, a lifesaver…

Hi Hans,

Yes, I agree with your view, but as with so much else in life, this was thrust upon me. Nothing like a baptism of fire.

To answer your question, qd is the range of quarter dates, and each cell in the array looks at two quarters, qd1 and qd2. What I am doing is selecting all the cells under the dates and defining them as qd. In the array function I refer to the first quarterly date as qd(i) and the second as qd(i + 1). That arrangement worked in the single cell UDF and I thought it would work here as well.

I know the logic looks complicated but it works in the single cell UDF. Somehow, though, I’m not able to get it going in the array UDF.

Does this answer your question?