Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a spreadsheet with cells that I want to be user-editable, but that I also want to have "default" or "placeholder" values in, whenever there is no user-entered data. There's a couple good use cases for this:

  • Prevent formula errors, while providing reasonable assumptions when a user has not entered (or has deleted) their own value.
    • I could use conditional formatting to alert the user to default values, so as to prevent their ignorance of them - they can then make an informed choice as to whether that value is still appropriate or not for the intended calculations.
  • Give a short description of what is intended to be entered in the cell, without having to have a separate "instructions" segment or document. This would also eliminate the need for a nearby "Label" cell, in some cases where it's really not appropriate.

To accomplish what I want, I need some formula, script, or other advanced spreadsheet option that will do the following:

  • Show the default value in the cell before user enters data.
  • Allow the default value to be found by any formulas referencing the cell, when there is no user-entered data in that cell.
  • Allow the user to freely (naturally, exactly as they would do with any "normal" cell) overwrite the displayed value with their own value or formula, and have the user-entered data found by any formulas referencing the cell.
  • When cell is blanked by deletion of user input, revert to default value.

Is there a way to do this in Excel, or am I asking too much of a spreadsheet program here?

share|improve this question
    
Think you could use data validation's input message Select the cell(s), click Data > Validation > Input Message tab Type the message in the box eg: Enter name. As for generating placeholders sequentially based off cells around it, that's much harder to do. Click OK The message will display when the cell is selected –  AlanTuring Sep 28 '12 at 21:35
add comment

1 Answer 1

There is no built in way to do this, you will need to create your own.

One possibility is this:

  1. For each cell you want a default for, create a worksheet scoped named range referencing that cell, and in the comment field enter the required default value
  2. Add the Workbook module Workbook_SheetChange event

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim nm As Name
        Dim cl As Range
    
        On Error Resume Next
        For Each cl In Target.Cells
            If IsEmpty(cl) Then
                For Each nm In Sh.Names
                    If nm.RefersToRange.Address = cl.Address Then
                        Application.EnableEvents = False
                        If IsNumeric(nm.Comment) Then
                            nm.RefersToRange.Value = Val(nm.Comment)
                        Else
                            nm.RefersToRange.Value = nm.Comment
                        End If
                        Application.EnableEvents = True
                        Exit For
                    End If
                Next
            End If
        Next
    End Sub
    

What this does is:

  • Any time a cell is changed the event code is run
  • If a cell that changed is empty
    • Look for a Worksheet scoped Named Range that references the cell
    • If found copy the Name comment into the cell value, converting to numeric is appropriate
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.