Surfacing Text from one sheet to another

Laurie A

Amazing! Thank you both so much. This makes my life easier!

Paul Newcome

@Laurie A That's one thing I did forget to mention...

Even with my solution, you are going to wan to "prefill" the formulas that get dragged down in my solution to account for the maximum number of entries expected. I usually give myself a buffer as well.

@L@123 Did you notice my comment about +1?

If you pull the first entry with the first formula into row 1 and then use

COUNT([Invitee]$1:[Invitee]1)

in row 2, wouldn't that provide a count of 1 which would pull that first entry a second time? I haven't had a chance to test it out yet.

L_123

@Paul Newcome Yeah you're right, I always forget that. It should be COUNT([Invitee]$1:[Invitee]1)+1 that goes in that slot.

I don't think i've ever written that formula and not forgotten that until I look at the results hahah. I missed your comment, I'm not a fan of how the comments are organized now on the forum, its hard to figure out the path.

Paul Newcome

@Laurie A Please see above re: +1

@L@123 Haha. I have a few formulas that I do that with. No matter how many times I write it, I always forget the same exact piece until I am looking at the results.

Laurie A

Thanks to both of you. I haven't had a chance to get back to this to test, but I will be doing that this week.

@Paul Newcome - Related follow up (given your extensive knowledge of parsing) would you happen to know if it's possible to parse out the sequential number from the prefix and suffix of an auto-number column (system column Row ID)? Eg. Parse the 001 out of auto number AT-001-END.

Laurie

Paul Newcome

Yes. What is the exact format of your auto-number? Can any of the 3 sections vary in the number of digits or will it always be the same number of digits for each section?

Laurie A

Exact format is ACTID_TSM: 0001 - AB65. The number of characters will always be the same, but the prefix and suffix will change based on department and rep.

Paul Newcome

Ok. Try something like this...

=MID([Auto-Number]@row, FIND(":", [Auto-Number]@row) + 2, 4)

We use a MID statement and tell it to start pulling 2 digits after the colon and to pull 4 digits.

You said the prefix could change based on the department, so basing it off of the colon allows you to accommodate any variations in the prefix. This is also assuming there will always be a space immediately after the colon and that the numbers you want to pull are always 4 digits long.

Laurie A

Another winner! Thank you!

Also I appreciate you walking me through it so I can learn how to do it.

Laurie!

Paul Newcome

@Laurie A Happy to help! 👍️

Laurie A

I had a chance to test this today and I ended up going with @L@123 's JOIN COLLECT solution since it's a small list I'm working with. I made sure to incorporate the +1 as noted by @Paul Newcome to avoid duplicating the first position. I also made sure to leave 'extra room' at the end.

It worked perfectly. So thank you very much, I appreciate the help.

Final thought, I don't suppose there is a way to "hide" the #INVALID VALUE return in the extra cells? I tried a IFERROR before the JOIN function but that worked so well all the cells returned blank! I tried searching for that and only found formulas that were returning invalid error messages.

Laurie

Paul Newcome

The IFERROR should not have returned a blank for the cells that contained data. That's odd.

How did you incorporate the IFERROR?

Laurie A

I must have just been tired and put the ,"" in the wrong spot yesterday because when I went back to enter the formula again to send to you it's working. For a sanity check, the one that's working is below. I wrapped the entire Index collect statement in the IFERROR which seems to be working, so I think it's right.

=IFERROR(INDEX(COLLECT({Invitee}, {Meeting 2}, @cell = 1), COUNT(Invitees$1:Invitees7) + 1), "")

Laurie

Paul Newcome

That is exactly correct.

Sometimes it helps to just enter

=INDEX(formula, "")

and then copy/paste the formula in.

I use that method a lot when building out complex formulas that involve a lot of different things nested all over the place. Either that or I will put the formula in one cell then use a cell reference. Once I make sure all parts are working individually, I can use copy/paste to pull the formula from the cell and drop it in place of the cell reference in the other one.