I have a bunch of categories; Bar Foo Ghost Misc Other

All have variable amounts - except for category Ghost, the amount is always 15.00.

I have Data Validation set up (which is how I pick the category). I want to automate that when I pick Ghost as my category, it auto fills in the next cell (in Column B) with the amount 15.00

image example

Is this possible in Google Sheets?

Note, my other categories can and do have variable numbers (including negatives).

Update: I'm hoping it'll ignore other cells which aren't in the Ghost category in row A.

2 Answers 2


workaround 1:

pre-populate B2:B with =IF($A(n)="Ghost", 15, )

  • B2: =IF($A2="Ghost", 15, )
  • B3: =IF($A3="Ghost", 15, )
  • B4: =IF($A4="Ghost", 15, )
  • B5: etc...

workaround 2:

set conditional formatting to range B2:B to signalize the spot

  • custom formula: =COUNTIF($A2, "Ghost")

workaround 3:

have one more column pulling data from B column constructing the whole thing (conditional formatting is just a bonus to know which cells to skip in process of filling up B column)

  • C1: ={"Construct"; ARRAYFORMULA(IF(A2:A="Ghost", 15, B2:B))}


  • I forgot to mention, this sheet is update daily, I'm constantly adding in new values (this only works to "prefill" the values for Ghost if everything else is empty. Can I make it ignore other cells if it isn't under the Ghost category in row A?
    – Welz
    Jan 1, 2019 at 2:04
  • @WELZ answer updated. i also believe this script could be reshaped to solve your issue: webapps.stackexchange.com/questions/99247
    – user0
    Jan 1, 2019 at 5:27

Write the formula in cell B2 and drag it down:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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