Merged cells and filter/jump to

[Dan]

Hi all

I have a table which contains 2 columns. colum A is a list
of tasks and colum B is a list of sub tasks. As the sheet
will need to be printed for asthetic reasons i have merged
the cells of column A for all the sub tasks.

I need to create some form of drop down menu or filter
which will enable me to select 1 task from the list and it
will display all the sub tasks. All the sub-tasks in the
table are grouped and so if the 1st sub-task for "clean
the car" starts in cell A34 the page would just need to
jump to this cell and display the subsequent cells as
normal.

I have tried to use the autofilter but when I select a
task from the list it will only show the first sub task.
Having read some of the other posts on this group I note
that this is a problem of merged cells but I cannot find
another way arround this.

It may be necessary in the future for more sub tasks to be
added, but they would be inserted in with the other sub
tasks for the task.

I would appreciate any assistance you can give

Thanks

 

[Dave Peterson]

You might not like this.

Get rid of the merged cells.

Put the task on each row for that group. (You can use Format|conditional
formatting to hide the duplicates.)

Your data|Filter|autofilter will still see all the tasks.

Debra Dalgleish has a way to fill in the blanks with the cell above at:
http://www.contextures.com/xlDataEntry02.html

first select your range (A2:Axxxx, say).
then use a condition formatting condition of:
Cell value is
=A1

(with A2 the activecell.)

Give it a font color to match the background color (white on white???).

 

[Dan]

Thanks for the reply

I am willing to go with the deleting the merged cells. The
only problem that would be that the file is to be passed
to other people with even less experience of excel than
myself (yes such people do exist) to populate the sub
tasks. I have put a default 10 spaces per task at present
but am sure that in the future more rows will be inserted
under at least some of the tasks.

If I use your suggestion of not using merged cells but
instead using a conditional format to hide the
repetitions, how can i ensure that the newly inserted row
automatically carries the task in column A from the cell
above?

Secondly, what would I need to put in the conditional
formating to hide if the cell = the cell above?

Thanks again

-----Original Message-----
You might not like this.

Get rid of the merged cells.

Put the task on each row for that group. (You can use Format|conditional
formatting to hide the duplicates.)

Your data|Filter|autofilter will still see all the tasks.

Debra Dalgleish has a way to fill in the blanks with the cell above at:
http://www.contextures.com/xlDataEntry02.html

first select your range (A2:Axxxx, say).
then use a condition formatting condition of:
Cell value is
=A1

(with A2 the activecell.)

Give it a font color to match the background color (white

Hi all

I have a table which contains 2 columns. colum A is a list
of tasks and colum B is a list of sub tasks. As the sheet
will need to be printed for asthetic reasons i have merged
the cells of column A for all the sub tasks.

I need to create some form of drop down menu or filter
which will enable me to select 1 task from the list and it
will display all the sub tasks. All the sub-tasks in the
table are grouped and so if the 1st sub-task for "clean
the car" starts in cell A34 the page would just need to
jump to this cell and display the subsequent cells as
normal.

I have tried to use the autofilter but when I select a
task from the list it will only show the first sub task.
Having read some of the other posts on this group I note
that this is a problem of merged cells but I cannot find
another way arround this.

It may be necessary in the future for more sub tasks to be
added, but they would be inserted in with the other sub
tasks for the task.

I would appreciate any assistance you can give

Thanks

--

Dave Peterson
(e-mail address removed)
.
[/QUOTE]

 

[Dave Peterson]

About the only thing you could do to insure that the data is filled in is to
copy the row and Insert the copied cells. Then clean up columns B:IV.

You could have a macro that did it for you:

Option Explicit
Sub testme()

Dim myRow As Long
Dim HowManyRows As Long

HowManyRows = Application.InputBox("Insert How Many Rows?", Type:=1)

With ActiveSheet
If HowManyRows > 0 Then
myRow = ActiveCell.Row
.Rows(myRow + 1).Resize(HowManyRows).Insert
.Cells(myRow + 1, 2) _
.Resize(HowManyRows, .Columns.Count - 1).ClearContents
.Cells(myRow + 1, 1).Resize(HowManyRows, 1).Value _
= .Cells(myRow, 1).Value
End If
End With

End Sub

The conditional formatting stuff:

Thanks for the reply

I am willing to go with the deleting the merged cells. The
only problem that would be that the file is to be passed
to other people with even less experience of excel than
myself (yes such people do exist) to populate the sub
tasks. I have put a default 10 spaces per task at present
but am sure that in the future more rows will be inserted
under at least some of the tasks.

If I use your suggestion of not using merged cells but
instead using a conditional format to hide the
repetitions, how can i ensure that the newly inserted row
automatically carries the task in column A from the cell
above?

Secondly, what would I need to put in the conditional
formating to hide if the cell = the cell above?

Thanks again



--

Dave Peterson
(e-mail address removed)
.

[/QUOTE]