[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Generic column headings when using advanced filtering in Excel.

Posted on 2011-03-13
5
Medium Priority
?
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 17

Expert Comment

by:Alan
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 17

Expert Comment

by:Alan
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 17

Accepted Solution

by:
Alan earned 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

649 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