Data Validation List and Formula

[excelbytes]

Is it possible to have both a formula and a drop down list in the same cell? I have 14 pages and I want the headers to match the first page, so I would just use a formula to pull in the header info from the first page to all the others. However, I still need the flexibility to modify any one of the headers on the succeeding 13 pages manually, which would delete the formula.

Is there a VBA solution to this?

 

[Joe4]

That is correct.
A cell can either contain a hard-coded value, or a formula, but never both at the same time.
Once you update with one thing, you lose the other.

Depending on what you are trying to do, and what you want to allow to happen (and the timing of it), perhaps VBA might be helpful here.
But we would need a bit more detail from you before coming a VBA code solution, specifically what needs to happen and at what point in the process.

 

[excelbytes]

That is correct.
A cell can either contain a hard-coded value, or a formula, but never both at the same time.
Once you update with one thing, you lose the other.

Depending on what you are trying to do, and what you want to allow to happen (and the timing of it), perhaps VBA might be helpful here.
But we would need a bit more detail from you before coming a VBA code solution, specifically what needs to happen and at what point in the process.

I'll try to explain the details. I have 14 worksheets, Sheet1...Sheet14. Each has a header in cells B1 & C1. On both cells in all 14 sheets is a drop down list with A, B, C & D. When I choose a selection in cell B1 & C1 of Sheet1, I want the other 13 sheets to have the same header. That's easy, using ='Sheet1"!B1 or ='Sheet1!C1 in the other 13 worksheets. However, if on Sheet4, for example, someone wants to select a different header than what is on Sheet1, it will override the formula and then it won't be able to go back to matching what's on Sheet1 if that's what they want. I don't see how that's possible, but thought maybe with VBA and a selection in another cell, like a CHOOSE function that if cell A1 =1 then use the drop down list, if 2, then use the formula.

 

[Joe4]

I think I might take a different approach. Maybe allow them to overwrite it, but then create a macro which "resets" everything and sets all the headers back to formula.
Or you could target it so that the macro only updates the active cell, setting it back to a formula.

 

[bebo021999]

Assum sheet1 is original sheet with dropdown list in B1 and C1
Sheet2 to sheet14 with B1 is same validaion with sheet1 B1, but 2 more items added: B1,B2
similar to C1, same validaion with sheet1 C1, but 2 more items added: C1,C2
This how below code work:
1) create NewList = Validation List in sheet1, plus B1,B2 (or C1,C2)
2) defaul value in B1, C1 each sheet: = B1, C1 of sheet1
3) pick other value in B1, C1
4) every time rung the sub, B1, C1 value back again B1,C1 of sheet1

Option Explicit
Sub TEST()
Dim NewListB, NewListC, ws As Worksheet
NewListB = Worksheets("Sheet1").Range("B1").Validation.Formula1 & ",B1,B2"
NewListC = Worksheets("Sheet1").Range("C1").Validation.Formula1 & ",C1,C2"
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            ws.Range("B1").Value = Worksheets("Sheet1").Range("B1").Value
            ws.Range("C1").Value = Worksheets("Sheet1").Range("C1").Value
            With ws.Range("B1").Validation
                .Delete
                .Add xlValidateList, , , NewListB
            End With
            With ws.Range("C1").Validation
                .Delete
                .Add xlValidateList, , , NewListC
            End With
         End If
    Next
End Sub