Menu
I can't post a link due to sensitive information, but I can provide the structure:
|Id |PT/DOT |Primary Employee |From |To |Per x Days |Frequency Data Complete? |
------ --------- ----------------- ------- ----- ------------ -------------------------
Those are the Column Headers. In Column A, B, and C there are text values. In D, E, & F there are either number values or blank values, dependent on whether the min frequency (From), max frequency (To), and Period (Per x Days) has been completed by the primary employee or not. Column G should provide a "Yes" or "No" value if all 3 frequency-related columns are not blank.
Simple version of this would be =if(and(D1<>"",E1<>"",F1<>""),"Yes","No"). That works in the simple version, but,I need it to autofill down for the number of rows of data because they change dynamically based on scripting.
Next step I tried was =arrayformula(if(and(D2<>"",E2<>"",F2<>""),"Yes","No")) which doesn't autofill down (continue) because it's not really an array.
I modified it to =arrayformula(if($D2:$F="","No","Yes")), which fills down to the end of the data, but it does not return a single Yes or No to Column G, but instead Yes or No for each of the 3 columns to G, H, and I. I could hide these columns and do a =countif("Yes") on J, but I'm still in the same boat with autofilling down.
I can do it with a script that sets a formula in column G, but that adds another run of scripting and another trigger to the process, when I would like to just keep a running tab on whether or not D, E, and F are complete.
I'm not really sure what the easiest method is here. I really need this to autofill down without additional effort from the user. onEdit script might be the only choice, but I'd rather not have to do that.
I've tried everything I can think of, and have searched this topic until any remotely relevant link is purple. I can't find anything that I could even modify to get to the same ends.
706
When all cells are blank, the formula returns "no value". The value returned when all cells are blank can be adjusted as desired. In Excel, empty double quotes ("") mean empty string.
Re: Writing a formula to return a blank if no data is in an adjacent cell Dear Chris, You can also replace this part: E7="" , by this one: ISBLANK(E7) This will help you to restrict the applying of the formula more then ever. The applying with this replacement will stop only for the completely blank cells.
Evaluates to TRUE if a cell is visually empty, even if it contains a zero-length string. Otherwise, evaluates to FALSE. Returns 0 if A1 is visually blank. Otherwise returns 1. If A1 contains an empty string (""), the formula returns 0. Evaluates to TRUE is a cell contains absolutely nothing - no formula, no spaces, no empty strings.
The formula in cell F5 is: which returns a value from the first non-blank cell, B5, C5, D5, or E5, respectively. When all cells are blank, the formula returns "no value". The value returned when all cells are blank can be adjusted as desired. In Excel, empty double quotes ("") mean empty string.
The simple way is to use the * operator, which simulates a logical AND over an array:
=ARRAYFORMULA(IF((D2:D<>"")*(E2:E<>"")*(F2:F<>""),"Yes","No"))
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
