Nov 17 2018 12:03 PM
4 | this cell to print |
4 | and this cell as well |
4 | and this cell as well |
HI guys, I thought I had IF statements down, but this one is not working for me. What I am trying to do in one cell [where the answer needs to go]is look at these three cells and if the cell on the left =4 then print what is in the cell next to it. So three statements in one, with all three answers printing if they are all 4, if not then it returns nothing for that cell. It only pulls the first one using this formula:
"=IF(a1=4,b1,if(a2=4,b2,IF(a3=4,b3,"")))" I need it to pull all three b1,b2,b3 because they all equal 4.
Nov 17 2018 12:17 PM
Hi Linda,
Could you please attach small sample file with your data and desired calculations return, where and what it shall be (without any formulas)
Nov 17 2018 12:54 PM
sorry I wasn't sure where i needed to reply, so reply here as well.
My issue is that my IF statement is only picking up the first answer, but I need to have it pick up multiple answers. In this case both that are highlighted in yellow, per the IF statement written. You will see what I am doing when you look at the formula.
Nov 17 2018 02:17 PM
Hi Linda,
Thank you for the file. Formula could be
=INDEX($G$14:$G$17,MATCH(1,$B$14:$B$17,0)) & " " & INDEX($G$20:$G$23,MATCH(1,$B$20:$B$23,0)) & " " & INDEX($G$8:$G$11,MATCH(1,$B$8:$B$11,0))
it's in D24 of the attached file. No error handling assuming you always 1, but that easily could be added.
More important assumption you have only one 1 in each block of B cells, otherwise formula doesn't work and we need another one.
Nov 17 2018 02:28 PM
I actually see that you used a range in column B. I actually need it to be just one line, as each line has a different response that goes into a different location on the form. How would this look like based upon how I did the IF statements?
Nov 17 2018 02:40 PM - edited Nov 17 2018 03:07 PM
I see. And how do you define which lines to pickup? In your formula
=IF(B8=1,G8,IF(B14=1,G14,IF(B23=1,G23,"Nothing noted in this section")))
that is first line in first and second groups (B1, B14) and the last line in the third groups (G23). You'd like to combine positions manually, or we take all first positions where 1, into another cell all second positions with 1, etc.
If manually that could be
=IF( (B8=1)+(B14=1)+(B23=1), IF(B8=1,G8 & " ", "") & IF(B14=1,G14 & " ","") & IF(B23=1,G23, ""),"Nothing noted in this section")
and in cell D25 attached
Nov 17 2018 04:39 PM
I pick all the lines that have red answers, just incase they pick that answer, but only the ones that are picked will the answers print. Does that make sense?
do I need these three below?
(B8=1)+(B14=1)+(B23=1)
Nov 19 2018 02:00 AM
Linda, it's possible but bit hard to automate selection of answers based on colour. You have some numbers in column F, better based on them. Let say
if any of b8:b11 is equal to 1 then add answer where F8:F11 is equal to 3,5,7 or 10 depends on the position of 1 in b8:b11, else nothing
and combine above for all groups.
(B8=1)+(B14=1)+(B23=1) means you have 1 at least in one of these cells, thus some answers from G picked-up, otherwise nothing is noted
Jan 19 2022 03:29 AM