>> Резюме вопроса

Я хочу реализовать сбрасываемое значение ячейки по умолчанию в Excel. Под этим я подразумеваю, что ячейка возвращается к значению «по умолчанию», полученному с помощью формулы поиска, зависящей от второй ячейки, когда эта вторая ячейка обновляется. Пользователь также может записать другое значение в исходную ячейку, которое останется до следующего обновления второй ячейки.

>> Основной корпус и детали

Хорошо, вот ситуация; этот снимок относится к соответствующей области репозитория данных на нескольких листах. Две интересующие вас ячейки выделены зеленым для ясности, а самая высокая видимая строка - это строка 1.

• Ячейка Поиск элемента принимает множество вводимых слов или фраз и имеет проверку данных, чтобы гарантировать, что возможны только допустимые вводы. Проверка данных берется из алфавитного списка возможных входов, а в ячейке есть вариант раскрывающегося списка (отсюда и маленькая стрелка справа).

• Ячейка Стек использует данные из ячейки Поиск элемента в следующей формуле ...

  =IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),"~")
  

  ... где J6 - это ячейка Поиск элемента, а диапазон C3: F315 - соответствующая часть таблицы поиска на том же листе.

  

Вот что я хотел бы сделать в ячейке Стек ...

• Current functionality:
  • When an invalid input is entered into the Item Search cell, a tilde is displayed instead of a number.
  • Когда вводится действительный ввод, в ячейке отображается соответствующий номер из справочной таблицы. Ячейки Купить и Продать также обновляются аналогичным образом.
• Desired additional functionality:
  • In the first instance, the tilde cannot be overwritten.
  • Во втором случае номер «по умолчанию» можно перезаписать, введя другое число в ячейку Стек.
  • Когда в ячейку Поиск элемента вводится новый ввод (или снова тот же ввод), снова отображается номер по умолчанию (или тильда).
• Wish list (non-essential):
  • To have a check-box (or similar; such as a yes/no input in an adjacent cell) that, if ticked, means that the displayed number in the Stack cell will not be changed/affected by any new 'default value' being read in from the lookup table. The number can still be modified by manually entering a new one.
  • В ячейке Поиск элемента в настоящее время есть раскрывающийся алфавитный список всех возможных допустимых вводов данных. Есть ли способ использовать этот же список для добавления функции автозаполнения в ячейку? Возможно, немного похоже на поисковую систему Google, раскрывающийся список появляется по мере ввода, а элементы, заполняющие этот список, постоянно ограничиваются теми, которые содержат (под) строку, которую вы уже набрали.

NB: любое значение, отображаемое в ячейке Стек, должно быть доступно для чтения формулами в других ячейках; а именно ячейки Купить и Продать, значения которых станут соотношением значения поиска ячейки Стек и значения, отображаемого в ячейке в то время. .

Возможно ли это в какой-то степени? Желательно (но не исключительно) без использования макросов. Эта книга предназначена для распространения среди других людей, при этом большая ее часть заблокирована и защищена, чтобы избежать каких-либо изменений в основных данных.

Заранее спасибо.

На данный момент найдена информация:

... но не совсем решаю свой вопрос.

1. Я, вероятно, мог бы использовать более одной ячейки для достижения одинаковых (или аналогичных) эффективных функций (одна ячейка содержит значение по умолчанию, другая - возможное значение, введенное пользователем, а третья - соответствующее выходное значение), но это не будет выглядеть как хорошо и интуитивно понятен для конечного пользователя. Эта книга предназначена для распространения среди других людей, при этом большая ее часть заблокирована и защищена. - Это нежелательный ответ.

2. Прежде чем задать этот вопрос, я обнаружил в своих поисках в Интернете эту небольшую информацию. В нем говорилось, что если я хочу, чтобы возврат к значению по умолчанию был автоматическим, то используйте следующий код в подпрограмме события изменения рабочего листа:

   Private Sub Worksheet_Change(ByVal Target As Range)
       If Not Intersect(Target, Range("C2")) Is Nothing Then
           If Range("C2").Value = "" Then
               Range("C2").Value = 1234
           End If
       End If
   End Sub
   

   Однако я не совсем понимаю, что имеется в виду и как это сделать.
   - C2 - это номинальная ячейка, используемая в примере другого человека.

3. Кто-то задал (возможно) аналогичный вопрос, и ему предоставили этот ответ, связанный с использованием пользовательских числовых форматов. Примет ли произвольный числовой формат формулу, подобную той, которая сейчас используется в ячейке Стек?

Загрузка документа:

Включены текущие и желаемые функции, элементы из списка желаний еще впереди.
Item-inary (общедоступный) .xlsm - (MediaFire)
18 марта 2012 г., 07:40 МСК

Текущая и желаемая функциональность + «Список желаний 1».
Item-inary (общедоступный) .xlsm - (Mediafire)
20 марта 2012 г., 19:50 МСК

>> ИЗМЕНИТЬ # 1:

Это мой код в различных разделах:

In ThisWorkbook

Public temp As Integer 'Used to contain Range("M6").Value once CheckBox5 is ticked
Public warn As Boolean 'True if CheckBox1 is ticked whilst (vVal = "~")

Private Sub Workbook_Open()
    warn = False 'Initialise to False
End Sub

In Sheet1 (Price List)

Private Sub CheckBox1_Click()
    If OLEObjects("CheckBox1").Object.Value = True Then
        If Range("M6").Value = "~" Then
            warn = True
        Else
            temp = Range("M6").Value
            warn = False
        End If
    End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal As Variant

    On Error GoTo Whoa

    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")

    '~~> If J6 has been changed, then continue. Otherwise skip.
    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect ("012370asdf")

        If vVal = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            '~~> Check if CheckBox5 is ticked.
            If OLEObjects("CheckBox5").Object.Value = True Then
                '~~> Checks if CheckBox5 was ticked whilst (vVal = "~")
                If warn = True Then
                    temp = vVal
                    warn = False 'Reset warn status now that special case is resolved
                End If
                Range("M6").Value = temp
            Else
                Range("M6").Value = vVal
            End If
            Range("M6:M7").Locked = False
        End If

        ActiveSheet.Protect ("012370asdf")
        GoTo LetsContinue
    End If

    '~~> If M6 has been changed, then continue. Otherwise skip.
    If Not Intersect(Target, Range("M6")) Is Nothing Then
        Application.EnableEvents = False

        If OLEObjects("CheckBox5").Object.Value = True Then
            temp = Range("M6").Value
        End If

        GoTo LetsContinue
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox err.Description
    Resume LetsContinue
End Sub

Этот код еще не включает никаких функций «Список желаний 2», но в остальном работает нормально.

Большое спасибо тем, кто помогал.