Return the column name using if function.

[Guest]

I have a client spreadsheet with client names in column A (starting in A2)
and a series of client information in each column B-!!. If the client
information is missing i have "pending" written in the cell. What i want to
do is make a type of report that will return column headers (ie column b is
company name) for each client where the value in the cell is equal to
"pending". For example if Row 3 Mr. X has pending in columns 5-10 and 21, i
want to return a list of column names to look some this like this

Mr. X Missing
column 5 name
column 6 name
column 7 name
column 8 name
etc....
I don't know how to write macros, i though maybe a pivot chart would work,
but i can't think of how to do it....thanks for any feedback.

 

[Biff]

Hi!

How many columns are there, and, how many clients are there?

It would be easier to output horizontally rather than vertically.

Biff

 

[Guest]

One quick n dirty possibility ..

Transpose the source data sheet in another sheet, then use autofilter

Assuming source data is in Sheet1
in Sheet2, put in A1:

=IF(OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)=0,"",OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1))

Copy A1 down by as many rows as there are columns in Sheet1, then fill
across by as many columns as there are clients (up to a max of 255 clients).
This dynamically transposes the source table from Sheet1 so that the client
names are now listed in B1 across with the col headers in Sheet1 listed in A2
down. Then we could apply Data > Filter > Autofilter, autofilter for
"pending" by the particular client name in B1 across, and read off the
desired results in col A.