Can I have formulas entered that can be written over, but if user deletes what they write, formula remains?

[danielrussell2]

Not sure that I worded the title very well, but here goes:

I'm creating a spreadsheet where users will input information different types of doors - all the details about the door itself. Columns A, B, and C have dropdown lists that the user will select from (based on information from other sheets). Columns D - S is where the user will input the details, as I have input example info below.

What I'd like to do for user efficiency is input formulas in the blue cells that would copy information given the same type door is input in column C (say, C2 is also selected as Door No. "e1", but for a Unit Type A1 in column A)(For a bit of backstory, the reason these would need to be different rows is that the LH and RH (columns D and E) are counts that give that information to other sheets totaling how many are needed for the project and where they go). Inputting this formula to copy information from columns F-S based on column C is not what I'm looking for, as that is easy enough.

Ok, let's say A103 is A1, B103 is Exterior, and C103 is e1. So, the door is the same as row 102 but for a different unit type. I will insert formulas in F-S to copy all of the details down - this will save time on user input and also can help cut down on user error. BUT what if one of the details is slightly different? Say S103 should be a "y" instead of being the "n" in S102 (based on some obscure building code, or really any reason). The user would just select the cell and type "y" - my problem is that this would delete the formula, which I want to be the default for the cell.

So I want to be able to put in a formula, and in that cell allow a user to "write over" the formula if need be, but if the user then deletes their input, the cell would default back to the formula I had entered previous to their "write over".

Is this possible?

 

[Yard]

Hi,

I would have three sections of columns - Section 1 which contains the default values (your formulas), Section 2 which is a corresponding section of columns which are available for user entry. and a final Section 3 which simply returns your preferred value between the first two sections.

So, the user sees the default value in Section 1 and, if they like to make a change, they enter an alternative in the corresponding column in Section 2. Section 3 simply has a logic which says that if Section 2 contains a value, use it, otherwise take Section 1's value.

What you specifically ask for is possible, but to me is an overly complicated solution to a simple challenge.

HTH
Yard

 

[danielrussell2]

Hi,

I would have three sections of columns - Section 1 which contains the default values (your formulas), Section 2 which is a corresponding section of columns which are available for user entry. and a final Section 3 which simply returns your preferred value between the first two sections.

So, the user sees the default value in Section 1 and, if they like to make a change, they enter an alternative in the corresponding column in Section 2. Section 3 simply has a logic which says that if Section 2 contains a value, use it, otherwise take Section 1's value.

What you specifically ask for is possible, but to me is an overly complicated solution to a simple challenge.

HTH
Yard

Yard,

Thank you for your suggestion. The problem with that approach is that this is a large spreadsheet with many columns of details - my image only shows door details (after that, there are sets of columns for details on hardware, trim, mirrors, bath accessories, shelving, blinds, windows, etc.) which is only a fraction of the width of this sheet. It wouldn't be economical to triple the width of this sheet to add in those columns.

You said what I specifically asked for is possible - can you explain how?

 

[AkaTrouble]

are you not just asking for feature that already exists they have the undo button

have read here for putting button on page http://www.mrexcel.com/forum/excel-questions/3623-undo-button.html

if you mean can they delete the contents of a cell at anytime in future and it then restores the formula to cell then.

only way i can think of is to create some VBA that checks cells on change and if empty inserts formula, this would result in a line of code for each formula in the sheet module

 

[Joe4]

What you are asking for would require VBA and possibly the use of more columns.
It all depends on these formulas that you might need to bring back. Are they some sort of "set" (unchanging) formulas that can easily be re-entered (by VBA) based upon some logic?
Or, might these formulas be so different in different scenarios that you will need to record what they were, and store that somewhere (i.e. in some other columns) so you can refer back to them?

For example, let's say that in a certain cell C2, the formula is the following:
=A2 + B2
Will the formulas in this cell (or column) ALWAYS look like this (always adding column A to column B), so that if you ever needed to rebuild it, you know that it will always add column A to column B?
If so, we can simply have VBA recreate the formula.

So, if for any given range, you can determine what the formula in that range should be (without requiring any previous knowledge as to what it was previously), then your can just program VBA to rebuild the formula for you without having to use some "helper" cell/column to look up what it was before.

 

[danielrussell2]

What you are asking for would require VBA and possibly the use of more columns.
It all depends on these formulas that you might need to bring back. Are they some sort of "set" (unchanging) formulas that can easily be re-entered (by VBA) based upon some logic?
Or, might these formulas be so different in different scenarios that you will need to record what they were, and store that somewhere (i.e. in some other columns) so you can refer back to them?

For example, let's say that in a certain cell C2, the formula is the following:
=A2 + B2
Will the formulas in this cell (or column) ALWAYS look like this (always adding column A to column B), so that if you ever needed to rebuild it, you know that it will always add column A to column B?
If so, we can simply have VBA recreate the formula.

So, if for any given range, you can determine what the formula in that range should be (without requiring any previous knowledge as to what it was previously), then your can just program VBA to rebuild the formula for you without having to use some "helper" cell/column to look up what it was before.

See example image below

Columns A:C are dropdown selections the user will choose from, then D:S columns will be typed in by the user. At minimum row 102 will always have to be typed in all the way across. I'm going to have some index/match functions in cells F103:S109 where if a user selects the same Door No (column C) as one that has previously been filled out, all of the columns from F:S will match those specifications input the first time.

So say a user has this example sheet up and goes to row 104, selects Door No. e1 from the dropdown list, row 104 columns F:S will auto-populate with the specifications listed in row 102 because it is the same door. The reason they can be on different rows is that 104 can be in a different Unit Type (column A). So instead of them having to type everything out for the same type door just for a different unit type, and to avoid potential user input error, I want the specifications to auto-populate.

The issue comes where the same type door may have a few differences - maybe the e1 door the user is selecting for row 104 is in a unit that is an ADA-compliant unit, so it has to have 2 peepholes (column X) instead of none (as listed in row 102), or maybe the jamb width has to be different (column K) because this unit has thicker walls, etc.

What I want is for the specifications to auto-populate if the same door no. is selected - I can handle that formula. But if a user needs to change one column in the specifications, they can type over what's in that cell, which erases the formula I'll have entered. A lot of the users for this spreadsheet are not very familiar with Excel, so if they change something in a cell, then decide that was wrong and deletes their input, I want the cell to automatically re-populate what it had originally auto-populated.

I hope that was clearer than mud for everyone. Thanks for your help!

 

[Joe4]

That appears to be the same as what I got from your previous posts - but you really didn't answer the questions I asked.

Let's try coming at it from a different angle. You said the following:

I'm going to have some index/match functions in cells F103:S109

Let's zero in on one cell - F103. There is some formula in that cell.
Will the formula in that cell ALWAYS be the same, and is it the same INDEX/MATCH formula for every cell in the column (from below row 102)?
If so, there is no need to try to "remember" what the formula is before it was changed to a hard-coded value. We can just build it again based on what we know it should be.

Here is an example. Let's say that all the rows in column F have a formula adding columns A and B together in that row. We can using the following Event Procedure code (which runs automatically upon changes) to populate formulas in column F whenever a value in column F is deleted below row 102:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim myCell As CellFormat
    
'   Check to see if updated cells are in column F below row 101
    Set myRange = Intersect(Target, Range("F102:F" & Rows.Count))
    If Not myRange Is Nothing Then
        Application.EnableEvents = False
        For Each cell In myRange
'           If cell is now blank, populate with formula
            If cell = "" Then cell.FormulaR1C1 = "=RC[-5]+RC[-4]"
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub

Note: In case you are not familiar with Event Procedure code, it needs to be placed in the appropriate Sheet module in order to run automatically.

 

[danielrussell2]

That appears to be the same as what I got from your previous posts - but you really didn't answer the questions I asked.

Let's try coming at it from a different angle. You said the following:

Let's zero in on one cell - F103. There is some formula in that cell.
Will the formula in that cell ALWAYS be the same, and is it the same INDEX/MATCH formula for every cell in the column (from below row 102)?
If so, there is no need to try to "remember" what the formula is before it was changed to a hard-coded value. We can just build it again based on what we know it should be.

Here is an example. Let's say that all the rows in column F have a formula adding columns A and B together in that row. We can using the following Event Procedure code (which runs automatically upon changes) to populate formulas in column F whenever a value in column F is deleted below row 102:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim myCell As CellFormat
    
'   Check to see if updated cells are in column F below row 101
    Set myRange = Intersect(Target, Range("F102:F" & Rows.Count))
    If Not myRange Is Nothing Then
        Application.EnableEvents = False
        For Each cell In myRange
'           If cell is now blank, populate with formula
            If cell = "" Then cell.FormulaR1C1 = "=RC[-5]+RC[-4]"
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub

Note: In case you are not familiar with Event Procedure code, it needs to be placed in the appropriate Sheet module in order to run automatically.

My apologies for not answering your question - yes, that formula would always be the same in that cell. Every cell in that column, actually, would always use the same index/match formula.

I will have to say that the event procedure code and, I assume this is VBA, is completely unfamiliar to me - I was hoping there'd be a much simpler solution. I'll have to educate myself on these things before I could get it set up.

Thank you so much for the help! When I get time to learn about how to do these I will try it and reply back to this thread.

 

[Joe4]

In order to do what you want - the automatic population of cells, it is absolutely going to require VBA. I am pretty certain there is no way around that.
If you want to avoid using VBA, I think you will need to adopt a solution like the one that Yard suggested. But pretty much any automation that is changing the value of cells would require some sort of VBA.

Chip Pearson wrote up a good article on Event Procedures here: Events In Excel VBA