Solved

Generic column headings when using advanced filtering in Excel.

Posted on 2011-03-13
5
183 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
  • 3
  • 2
5 Comments
 

Author Comment

by:Steve_Brady
ID: 35123993
Sorry, I meant to include a link for the previous thread. Here it is:

previous thread
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 35124037
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
 
LVL 12

Expert Comment

by:Alan3285
ID: 35124047
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
 

Author Comment

by:Steve_Brady
ID: 35125532
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
 
LVL 12

Accepted Solution

by:
Alan3285 earned 500 total points
ID: 35125871
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now