Best Of

"Tile"

would change to

HAS(@cell, "Tile")

Paul is likely to come up with a cleaner answer, so if he replies please happily skip to his answer instead of mine! :)

**** Edited after I started trying to brute force an answer*****

I'm pretty sure there is a way via sheet Automation (under the Automation menu) to say something like "when a new line is added, if it is marked as a reimbursement add it to the row with the same meeting ID and attendee name in my summary sheet. If there ISN'T a row with that same meeting ID and attendee name, make such a row". This is the best way I can think of to address your problem.

Below you can see what I was starting to suggest - it would WORK but would be inefficient and prone to breakage. I mainly left it because under OTHER circumstances it might be helpful. Additionally, some of the formulas might be helpful for the automation - for example, if you can't embed that "if()" statement in the automation, you can instead trigger two different automation routines - one to add the value to appropriate column (reimbursement or authorization) if "AttendeeCounter" >1 and one to add a new row to your sheet if "AttendeeCounter"=1.

********

………………………………… Here is what I was originally going to suggest, don't do this………………………

It would look to me like you are dealing with a somewhat tricky issue here due to any given meeting having a variable number of possibly attendees - one meeting might have 3, another might have 7. So, you need a solution that adds a variable number of rows based on the meeting.

I'd start by adding 6 helper columns to your data collection sheet:

2 helper columns that give you a "Row" column - see this post from Paul where he tells you how to do this:

Is there a way to reference the row above? — Smartsheet Community

Insert an auto-number column (called "Auto) that has no special formatting.

Next insert a text/number column (called "Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

1 helper column that gives you a "MeetingCounter" value (basically, a column that increments for every new row in a given meeting and starts back over at 1 for the next meeting):

=COUNTIFS([Meeting ID]:[Meeting ID], [Meeting ID]@row, Row:Row, <=Row@row)

1 helper column that gives you a "MeetingCycle" value (one that identifies your first meeting, your second meeting, etc)

=COUNTIFS(MeetingCounter:MeetingCounter, 1, Row:Row, <=Row@row)

1 helper column that gives you a "AttendeeCounter" (similar to MeetingCounter, but increments on the rows per attendee per meeting):

=COUNTIFS([Traveler Name]:[Traveler Name], [Traveler Name]@row, [Meeting ID]:[Meeting ID], [Meeting ID]@row, Row:Row, <=Row@row)

and 1 helper column that gives you an AttendeeCycle (similar to MeetingCycle in that it identifies the maximum number of attendees in each meeting and only increments with a new attendee)

=COUNTIFS(AttendeeCounter:AttendeeCounter, 1, [Meeting ID]:[Meeting ID], [Meeting ID]@row, Row:Row, <=Row@row)

Now, on the sheet where you want you single row per attendee per meeting you would need the following:

1 helper column where you just create an incrementing list of integers - start at one and fill downward, and expand as needed. This "expand as needed" is the "forced updating" bit I was describing above - it isn't ideal and allows the sheet to break if you aren't paying attention to it, which is unfortunate. Call this "Row".

……this was going to finish with some convoluted way of then pulling in meeting ID numbers on each row with repetitions to match the maximum number under "AttendeeCycle" for that same meeting ID number, then index through the Attendee names for that meeting by looking only at values of "1" for that meeting ID number in the "AttendeeCounter" column and then using a sumifs to grab the applicable reimbursements and expenses per attendee. It would WORK, but there is no way that is the best way. Don't do this…………………………………