RE: Formula help to ignore specific text during cell reference from pivot data

[New_Bie]

Hi All,


I have a pivot table in my workbook and I am trying to pull data from the pivot data column using cell reference. The source data will change, hence I need to extend the excel formula to a certain range.


I need a formula that should ignore and return an empty cell if it comes across a certain text in cell value from the respective column. I have attached the sample data for your reference.


The pivot range starts from column "A" and 4th Row. The formula which I have currently used is below:

=IF(A4<>"",A4,"")

This formula returns the cell value in column A and returns empty if no value in column A. But if any text like grand total and blank occurs, it is returning the same text. Here also, it should return empty cell

I have attached the sample screenshot for your reference.

 

[pecoflyer]

Try =IF(A4,="(blank)","",A46)

 

[New_Bie]

Try =IF(A4,="(blank)","",A46)

Hi,

This formula =IF(A4="(blank)","", A4) works but it repeats the same value in all the cells. eg: if column A15 have East, it returns central only. Also, the formula should ignore two texts like "blank" and "Grand Total" and return empty cells.

Attached the screenshot for reference.