Posts by Alka Bajaj

Re: vlookup cell format

Hello Expert,

Any help on the above wil be much appreciated.

Thanks.

Hi Expert,

I have a Master sheet which needs to refer a source sheet having the same criteria name and vlookup the status value with the cell format.

Appreciate if someone can help me to code this. I am unable to color code the cell as per the source file.

Please refer the attached file. cell format.xls

Many thanks.
Regards,

Re: To Apply formula in Pivot column

When I am add
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
ProcessPivot Target
End Sub

I get a compile error- "Ambiguous name detected".
Hence did not included it.

Attached the file with the error. PLease refer the ist row for the error.

Re: To Apply formula in Pivot column

Here it goes. Sample_05012012.xlsm

Re: To Apply formula in Pivot column

I was waiting for you to be online.
Can I send anywhere in your mail box? It is bit big to be attached.

Re: To Apply formula in Pivot column

Hi Rory,

Thanks again.

But my file is still throwing some errors. Attached the file with the errors. Sample_05012010.zip

Thanks for all the effort & support.

On a separate note, great work to support the nobel cause. Have to sponsor that.

Regards,

Re: To Apply formula in Pivot column

Hi Rory,
With refernece to above solution, how do we remove the old grand total on the newly added column once pivot is altered.

Please refer the attachment.

Re: To Apply formula in Pivot column

Thanks Rory so much, the last cloumn" Request No" adds to the list perfectly.

Do you mean, the code will automatically refresh the above added column if I alter pivot table? Right now it is not.

Regards,
Alka

Re: To Apply formula in Pivot column

yeah,... was trying to ask something out of the original post. I was asking how to preserve the below formating of the pivot cell, everytime I refresh it is gone.
1. Cell color should be white
2. Cell format should be " Wrap Text"

But you can choose to ignore this in this particular post.

Just help me in the macro error. Really really appreciate the help done so far.

Re: To Apply formula in Pivot column

Hi Rory,

Also, one more thing..
Everytime I refresh the pivots, the cell formating is gone. I need to maintain this cell formating as below.
1. Cell color should be white
2. Cell format should be " Wrap Text"

How can I code that.

Regards,
Alka

Re: To Apply formula in Pivot column

Please refer the sample file with run time error.

Re: To Apply formula in Pivot column

Hi Rory,

Still gets the error. I have increased the pivot size in the sample workbook and run the macro. Get the below error.
Attached the file for your reference.
Run-time error- 1004
Application defined or object defined error.

Also, wrt # 2. I need to run the code again everytime? What is worksheet's pivottableupdate event ? Is it a normal pivot refresh?
Regards,
Alka

Re: To Apply formula in Pivot column

Just checked this sample file , it works perfectly fine.

Let me see, why is it not happening in the original file.

Will keep u posted tommorrow in this. Thanks a ton again.

Cheers,
Alka

Re: To Apply formula in Pivot column

Code breaks at the line. Cannot execute further.

Re: To Apply formula in Pivot column

Thanks Rory.

But I am getting an error at
.Resize(.Rows.Count - 1).Style = "Hyperlink".

Also, wrt # 2. I need to run the code again everytime? What is worksheet's pivottableupdate event ? Is it a normal pivot refresh?

Re: To Apply formula in Pivot column

Thanks Rory for the VB. It works as expected.

However, appreciate if you let me know how to resolve below

1. Inserted column, requires few formating as highlighted in attached.
2. Also, pivots will be usually filtered based on certain fields. When I run this macro, only the displayed rows fetchs the request no. In an event, when someone unfilter the field, then the request no is not displayed correctly, until n unless the macro is re-run.

Thanks again for the help.

Regards,
ALKA

Re: To Apply formula in Pivot column

Hi All,

Please refer the attached.

Reporting Dashboard_Sample.xlsx

Regards,
Alka

Re: To Apply formula in Pivot column

Hi Rory,

I am unable to attach the file to this forum, Please let me know if I can mail you.

Appreciate your support on this. Really required.

Thanks.

Regards,
Alka

Re: To Apply formula in Pivot column

My bad.It would be different for different pivots. Formula has to be relative depending on pivot & data location.
In this case we need to Lookup_value in reference to the column " SHort Description"

All pivots would have this column as their row data.

Thanks again for all the help!

Re: To Apply formula in Pivot column

F9 is look up value, just a reference to a cell for vlookup.