1

in my original workbook i want to display some values from another sheet for multiple actions.. so i put drop down list for each actions. but when the actions increased i couldn't recognize each formulas. (all of them are import range) so that if i can name each formulas in the drop down then i can recognize fast which action to be performed. here a sample sheet is attached for a solution.. pls take a look. in the dropdown list i included (=a2+b2) instead of that if it displayed as addition in drop down list would be help ful. please take a look.

sorry for my english

Any type of help would be appreciated.

https://docs.google.com/spreadsheets/d/1mpIWyQASMlxRVdlTkv9K1e4oihsrckjT6sD1mLDxvEc/edit#gid=0

Arya Murali
  • 47
  • 6
  • @calculuswhiz.. am really sorry.. i forgot to make it public – Arya Murali Sep 26 '20 at 03:58
  • 1
    Questions should be self contained. While external links are welcome, consider adding a ascii table like ([this](https://stackoverflow.com/q/63837444) or [this](https://stackoverflow.com/q/64059360)) or screenshots or csv text to show your data structure. – TheMaster Sep 26 '20 at 09:53

1 Answers1

1

If I understand correctly, you want to have a dropdown list menu (from Data Validation) that displays the operation name, but when you click it, it displays just the result.

This is very hacky, but here's a way to create some "labels" in your criteria box:

=IF(;"ADDITION";A2+B2),
=IF(;"SUBTRACTION";A2-B2),
=IF(;"DIVISION";A2/B2),
=IF(;"MULTIPLICATION";A2*B2),

How?(!)

After kicking around some no-op ideas, I finally settled on this as the cleanest and most flexible approach. (By some freak coincidence, it also makes some semantic sense too.) It works because when the first argument to IF is omitted, it defaults to 0 -> FALSE. This effectively makes the second argument to IF a comment/no-op, and always just selects the formula.

Yes, the semicolons are intentional or the parser will think of the args as list items.

Productivity Tip/Footnotes

  • Sheets will remove any line breaks in your validation criteria, so the formula will be hard to read when you have to edit it. If you anticipate that you'll be adding a bunch of functions later, save the above block in a text file and edit that. Then you can copy+paste it into the validation field.
  • It will also always show up as "INVALID" because the value will of course never match the formula text.
General Grievance
  • 4,555
  • 31
  • 31
  • 45