Google Spreadsheet Post #2462
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI Jun-16-2018
question by: dlrbestpublic
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/79stcxty14o;context-place=mydiscussions
How to create an array that omits blanks and writes to one cell?
Need some help please. I am using a google form to input certification into a Google Sheets spreadsheet. The form answers get written into a worksheet named 'Form Responses 1'. That tab and my Master tab have a Member ID column for matching purposes.
Right now, I have no problem copying the first matching line (using Index/Match) from the Form Responses 1 tab into the Master tab to populate that member's certifications.
However, as I have a few folks that are not familiar with Excel/Google Sheets, I would like the Google Form to be used for certification updates as well -not just a one time entry per member. For example, on initial entry, Certifications 1 and 2 were recorded for Member 1. Then one month later, Member 1 received Certification 3. Using the form to input that adds another line to the Form Responses 1 tab for Member 1. I would like to create an array of all entries for Member 1 and for each cell that is not blank, write that information into one single cell on the Master tab. So far, I cannot figure out how to get it to disregard blanks and to write to the same cell. This is the base formula I have been working with:
Column B has the Member ID in the one sheet and A3 has it in the other. Column J is just one of the 28 certifications that are being tracked. Let's call that Cert1. I am trying to write the responses found in J (knowing that all will be blank but 1) into cell I3 on the master sheet. I have tried using ISTEXT, <>"", isblank, but the answer is eluding me. Any help is most appreciated.
No comments:
Post a Comment