"List" view of Excel spreadsheet

[Boo]

Hi,

Can anyone tell me whether there is a way of viewing an Excel spreadsheet as a single list of all the formulas applying to the cells in use ? Ideally, formulas applying to an entire column or row or area would be conglomerated into a single line in the list.

Thanks,

Boo

[jamesbrown]

I don't think this answers your question but, FWIW, you can switch between viewing formulas and outputs with CNTRL + `. Then, when selecting a cell with a formula, it will show the range of cells used in that formula. Another possibility (again, probably not adequate) is to display the precedents/dependents for formulas. If you go to the Formulas tab, you'll see some options under Formula Auditing for tracing (via directional arrows) which cells/functions reference a selected cell (dependents) and vice versa (precedents). Anything more automated than that is probably a VB problem...

[xoggoth]

You could also use VBA to copy contents as formula to another sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For r = 1 To 10
For c = 1 To 10
Sheet2.Range("A1").Cells(r, c).Value = Range("A1").Cells(r, c).Formula
Next c
Next r
End Sub

For this to work you must format Sheet2 as text, otherwise you will see values and not the formula.