Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    varying column headers (XL 2000 on Win2k/XP)

    I want to be able to display a different set of column headers depending on the value I set in a cell.

    Initially I did this by putting a nested IF statement into each cell in the column header range, e.g. cell A1 held:
    IF(IndexCell=“set1”,ColHeaderSheet!A1,IF(IndexCell =“set2”,ColHeaderSheet!F1,IF(IndexCell=“set3”,ColH eaderSheet!K1,“”)))

    then cell A2 referred to ColHeaderSheet!A2, cell A3 returned ColHeaderSheet!A3, etc

    That was OK because the number of different headers I had was less than XL’s limit of 7 nested IF statements inside the initial one so I could switch between 8 different sets of headers.

    You can guess my problem; I want to be able to display more than 8 different sets of column headers. What is a more sensible solution for this requirement?

    I’m stuck

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: varying column headers (XL 2000 on Win2k/XP)

    You can do it with a lookup. John Walkenbach discusses it and give examples in Excel User Tip: Alternatives to Nested IF Functions

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: varying column headers (XL 2000 on Win2k/XP)

    I've come up with a solution...

    The simple example here:
    http://www.excel-vba.com/excel-14-fo...ndex-match.htm
    gave me enough to constuct a formula for each cell of my header block along the lines of:
    =INDEX(col_hdrs!$B$3:$AD$63,MATCH($B$1,col_hdrs!$B $3:$B$63,0)+n,Z)

    In other words B1 is my IndexCell, MATCH finds the row number where that occurs in my lookup range (B3:AD63 on a separate worksheet) and index then returns the value of the cell at that row in the column specifed by 'Z'. Since the IndexCell value is a marker for a block of 5 rows in the lookup range I need the '+n' as an offset (n= 0, 1, 2, 3 or 4) to find the actual cell I want.

    Any comments?

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •