Solved

Generic column headings when using advanced filtering in Excel.

Posted on 2011-03-13
5
186 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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find and Replace Function not working in Excel 13 50
And OR formula 5 23
formula how to get the number incrementor? 3 23
Rather Simple Formatting Question 6 24
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

832 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