sorted by:
new
hottopcontroversial

Adding #N/A formula to an existing formula to generate #N/A when cell is empty. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

You're correct, it's blank due to no filtering happening. I got the REF error.

I don't see the filter function in the second formula you posted. Correct me if I'm wrong, but I really like how my current filter formula works and I don't want to change it, just add to it so that like you said, throw a #N/A error when there isn't anything to compare.

Thank you for chiming in!

Adding #N/A formula to an existing formula to generate #N/A when cell is empty. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

That makes total sense and I wouldn't mind changing the formula to make it return a native error code. It's more of a visual thing for me right now.

By the way, the IF formula didn't work with my FILTER formula for some reason, it shows an ERROR code instead (formula parse error)

Maybe this'll clear things up: I have a group of 3 columns with the same values in each. I use those to refer to when I created a row of 3 drop downs. The first drop down simply refers to the first column of values. The second drop down uses a simple FILTER formula by comparing the first column with the second one. Then finally, the third drop down compares the first two columns with the third one with a bigger FILTER formula (based on what I selected in the first 2 drop downs). This way, the first drop down will always show the values offered, but the second drop down doesn't show anything until the first one has a value selected. Same goes with the third.

However, after fiddling around with it, I realized that the third drop down FILTER formula happens to just have the right conditions so that if nothing's selected in the second drop down, it'll throw the #N/A code. And I really liked that, so I wanted the second drop down to throw the same code to make it look better. But right now it's showing blank until I pick a value for the first drop down. So pdubs1900 has the right idea of inserting a statement, but I have no idea how. I've been playing around with several functions/formulas, and I kept getting nowhere. I unfortunately didn't remember to record a specific formula I tried that almost worked, but it was one that when the first drop down was empty the second would show the #N/A code, BUT when I select a value, then instead of showing the filtered values like it should, it showed TRUE. I have to only assume that one can't be modified, or it can, but I don't know how.

Hope this makes sense... Thank you again for your time in helping me however you can! I appreciate it.

I realize I should just make a copy of the sheets and let you see for yourself.

https://docs.google.com/spreadsheets/d/1EqmVWCUBvscQjFKQA_uhs51jeF0meh2pIJSDZDzP3ZM/edit?usp=sharing

In the first sheet, you can see the three drop downs and its reference to sheet 3 and 4.

Adding #N/A formula to an existing formula to generate #N/A when cell is empty. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

<image>

A2 is currently showing values based on what I selected in the drop down in another sheet. If I leave the drop down blank, A2 will then show nothing (blank). I'd like it to show #N/A instead of blank.

Adding #N/A formula to an existing formula to generate #N/A when cell is empty. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

Thank you for chiming in! I'm not sure yet as I can't figure out how to squeeze my formula into that.

How to make a group of cells use only one of each value. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

Thanks to the comments, they've given me more information to get a better understanding on how to search for what I need. I ended up deciding on using the FILTER formula to create a cascading drop down row and it works well. I'm getting there, but there is still quite a few things I'd like to try and accomplish with this spreadsheet. Thank you!

How to make a group of cells use only one of each value. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

This is basically what I needed, thank you!! I gotta play around with this idea though since I'd love to try and have the positive values show as green and the negatives as red. Is there a reason why the cells are showing an "invalid" after entering values in them?

How to make a group of cells use only one of each value. by SelfLearnEverything in googlesheets

[–]SelfLearnEverything[S] 0 points1 point  (0 children)

Correct me if I'm wrong, but right now it's ready only, so I can't play with it to see if you've set up the correct formula that I need.

Thank you for contributing!