Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return the column name using if function.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return the column name using if function.
Hi!
How many columns are there, and, how many clients are there? It would be easier to output horizontally rather than vertically. Biff "pblenis" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return the column name using if function.
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "pblenis" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to return # of column with min value in selected rows | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Fill handle formula | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |