• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

"List" view of Excel spreadsheet

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    "List" view of Excel spreadsheet

    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

    #2
    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...
    Last edited by jamesbrown; 8 August 2015, 12:35.

    Comment


      #3
      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.
      Last edited by xoggoth; 9 August 2015, 21:55.
      bloggoth

      If everything isn't black and white, I say, 'Why the hell not?'
      John Wayne (My guru, not to be confused with my beloved prophet Jeremy Clarkson)

      Comment

      Working...
      X