The first sheet in my workbook is a rollup of all the sheets in the workbook.
I have a conditional format that if cell A1 in sheet1 is greater than 44 to
fill it red. The formula in cell A1 is a sum of all E7 cells in all
worksheets:
=SUM('Sheet2:Sheet7'!E7)

What I've been asked to do is if the value of cell A1 in sheet1 is greater
than 44, then the user can click on cell A1 and a drop down list will appear
that shows value of column E of sheets 2 through 7. For example:

SheetX ColumnE
Sheet2 15.0

Where SheetX is the actual tab name, and ColumnE is the hours. Basically
what I need is when the cell is clicked, the actual values of the formula
show up in a list.

I know in Access you can create a subreport, but my manager wants to keep
this in Excel and also does not want to use grouping

Where
--
Thanks in advance!

This will create an equivalent "visual" in a range adjacent to A1

Put in B1:
=IF(AND(ISNUMBER($A$1),$A$144),INDIRECT("'Sheet"& ROW(A1)+1&"'!E7"),"")
Copy B1 down to B6

If A1 exceeds 44, B1:B6 will display the contents of cell E7 in Sheet2 to
Sheet7, otherwise B1:B6 will appear "blank"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Robin" wrote:
The first sheet in my workbook is a rollup of all the sheets in the workbook.
I have a conditional format that if cell A1 in sheet1 is greater than 44 to
fill it red. The formula in cell A1 is a sum of all E7 cells in all
worksheets:
=SUM('Sheet2:Sheet7'!E7)

What I've been asked to do is if the value of cell A1 in sheet1 is greater
than 44, then the user can click on cell A1 and a drop down list will appear
that shows value of column E of sheets 2 through 7. For example:

SheetX ColumnE
Sheet2 15.0

Where SheetX is the actual tab name, and ColumnE is the hours. Basically
what I need is when the cell is clicked, the actual values of the formula
show up in a list.

I know in Access you can create a subreport, but my manager wants to keep
this in Excel and also does not want to use grouping

Where
--
Thanks in advance!