Excel Checklist

[Ada01]

I am trying to make a summary page actually be a summary page (as apposed to a repeat of the details page in a different order). In the attached workbook there is a detail page with named ranges for the tasks and the value associated with it. on the summary page i would like the end result to match the white text in column E

Thanks

[teylyn]

Hi Ada,

that's a bit of a tricky layout you got there. In general, it is better not to have blank rows interspersed with data/summary rows, because it will make it so much harder to come up with a formula that can be copied down.

Your formula always returns Task1 etc, because the first IF condition is always true, no matter in which cell you ask it. So you need a way to tell Excel to test a condition in relation to the current row, or return a value in relation to the current row.

A formula that will return the results that you need could be:

=IF(ISNUMBER(INDEX(Details!$A$2:$H$2,1,(ROW()/2)-2)),INDEX(Details!$A$1:$H$1,1,(ROW()/2)-2)&":"&INDEX(Details!$A$2:$H$2,1,(ROW()/2)-2),"")

Note that this one does not even use your range names, but it can be applied to all the result cells and will deliver what you specified.

With regards to sorting the results, so that the blank results are pushed to the bottom of the list: with this layout it will be difficult.

In general, you may want to have a look at pivot tables to see how you could report on and summarize data without the need for dozens of range names or complicated formulas.

cheers

[scaffdog845]

Ada01

A little more information may be helpful. I can only assume that there need be values entered into the white blocks in column B on the summary sheet that dictate what the possible outcome values are in column D. Is this correct? If it is try putting the following formulae in cell D6 and copying it to the corresponding cells.

=IF(B6="","",IF(B6=A,A_Header&": "&B6,IF(B6=B,B_Header&": "&B6,IF(B6=D,D_Header&": "&B6,IF(B6=E,E_Header&": "&B6,IF(B6=F,F_Header&": "&B6,(IF(B6=G,G_Header&": "&B6,IF(B6=H,H_Header&": "&B6,IF(B6=I,I_Header,""))))))))))

[teylyn]

@scaffdog,

You've missed out column C . Also note that this formula will not work in XL 2003. Too many nested IFs. In general, nested IFs with such a regular pattern can normally be replaced by a lookup or index formula. The "every other row" layout makes it a bit tricky, but doable.

cheers

[scaffdog845]

teylyn

Thanks for the note on there being too many IFs for '03. I've become so used to using the newer version that I oftern forget the older versions limitations. i would not have been able to figure out the formula with the use of INDEX due to the alternate lines.