Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Generic column headings when using advanced filtering in Excel.

Hello,

This is a follow-up question from another thread.

In Excel (2007), I have recently been using advanced filtering more and more as I have learned more about its benefits and advantages.  However, one thing that has messed me up on occasion is the requirement to have column headings in row 1 followed by a criteria area in some number of rows (say rows 2-4) followed by a duplicate of row 1 which in this case would be in row 5.

Because I almost always use two rows for headings and frequently insert, delete, and change columns -- sometimes without assigning or changing headings, I decided to just keep "my headings" (i.e. those I would normally have in rows 1 & 2 and which pertain to my spreadsheet) in row 6 & 7 and use generic headings in row 1 & 5.  For the generic headings, I arbitrarily chose to simply use the actual letter(s)-heading of that column.  Thus, my previous question related to getting the actual letters since =COLUMN() returns numbers.

It was suggested by an expert that I explain the purpose behind my previous question on the chance that a better solution may exist.

Thanks
0
Steve_Brady
Asked:
Steve_Brady
  • 3
  • 2
1 Solution
 
Steve_BradyAuthor Commented:
Sorry, I meant to include a link for the previous thread. Here it is:

previous thread
0
 
AlanConsultantCommented:
Hi Steve,

Would it be possible to attach a sample workbook?

If the data is confidential, and it is not too much trouble, you could delete the actual data and replace with dummy stuff.

If not, we can just ask questions, but seeing it would likely be quicker for you.

Thanks,

Alan.
0
 
AlanConsultantCommented:
Hi Steve,

I am guessing you are having difficulties in that some of the items in row 6 and / or row 7 are duplicated within their own rows, and only become unique when combined together?

If so, please can you give an example of the mult row headings (now in 6 & 7) and why they cannot be combined into a single cell?

Thanks,

Alan.

Thanks,

Alan.
0
 
Steve_BradyAuthor Commented:
Hi Alan,

Thanks for the response.

>>I am guessing you are having difficulties in that some of the items in row 6 and / or row 7 are duplicated within their own rows, and only become unique when combined together?

Duplicated cell contents in the same row is certainly an issue at times (and I'd be interest in what you do with those).  However, the main issue is simply related to real estate.  Obviously, I'm sure most users try to minimize the length of their heading entries but even then, don't you commonly end up with certain columns in which the width needed for your data entries is quite minimal -- maybe only a character or two -- whereas your heading for that column, even in it's most abbreviated form, is several time wider?  

In that case, I would always rather split the heading between a couple or even three rows than have a skinny little line of data in a column that's 10 times wider than needed.  I try to minimize the blank white areas on my screen so I can see more columns (data) at-a-glance.

>>If so, please can you give an example of the mult row headings (now in 6 & 7) and why they cannot be combined into a single cell?

The information is confidential but there is nothing complicated.  All it has is the formula from the previous thread in A1 followed by these other entries:

    A1        =SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)
    A2        [empty]
    A3        [empty]
    A4        [empty]
    A5        =A1
    A6        1st data headings row
    A7        2nd data headings row
    A8        Data
    A9        ...
 1
This way the filter and data headings have nothing to do with each other.  When columns change or are added, I can just copy A1:A5 and paste it to the same rows in the new column.  I usually include a freeze pane below row 7 and an outline grouping as shown so I can see the headings and also stow the filter rows until needed:
 2
Thanks for any thoughts. EE-2011.0313.xlsx
0
 
AlanConsultantCommented:
Hi Steve,

In answer to your first query:

>
> I try to minimize the blank white areas on my screen
> so I can see more columns (data) at-a-glance.
>


I have attached a file showing one way to get around this, including a new column Q with a really long field name.

There are limitations using this - 'FieldName' cannot be split unless you don't mind the word itself wrapping, but I personally don't like that.

Another option is in column R, but personally I tend to go for the 'Q' option.

Does that solve that part of the issue?

Alan.

EE-2011.0313---Version-001.xlsx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now