Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Collect a Name from a List

I am trying to keep a list of travel expenses for specific meetings that occur. I have the master list of authorizations/reimbursements that gets updated weekly. If I enter the meeting ID I want it to pull every person that has that meeting ID. There could be 5-25 individuals at the meeting and each might have multiple authorizations of reimbursements. If Person A for meeting 1 has 3 entries, I want Person A's name and the amount of each of their entries to appear in a row on another sheet where I enter the meeting ID. I also want Person B - Person F to appear as well.\ in different rows with their associated amounts. Any ideas how to do this without having to already have a list of every possible name and Meeting ID combination?

Thanks!

Best Answer

  • Community Champion
    Answer ✓

    Once I started building, I realized this was actually a lot less complicated that I originally though.

    Insert a helper column on the source sheet that combines the meeting id and name into a string.

    =[Meeting ID]@row + "-" + [Traveler Name]@row

    In the summary sheet, insert the [Number] column that is manually populated with numbers starting with 1 at the top of the sheet.

    Then a text/number column called "ID-Name" with this column formula:

    =IFERROR(INDEX(DISTINCT(COLLECT({Master List: ID-Name}, {Master List: ID-Name}, @cell <> "")), Number@row), "")

    [Traveler Name] is:

    =IF([ID-Name]@row <> "", SUBSTITUTE([ID-Name]@row, [Meeting ID]@row + "-", ""))

    [Meeting ID] is:

    =IF([ID-Name]@row <> "", LEFT([ID-Name]@row, FIND("-", [ID-Name]@row) - 1))

    The reimbursement and authorization columns are SUMIFS suing the [ID-Name] column as one of the range/criteria sets, and then the difference column is simply one minus the other.

    You can take it another step and put in a flag column that throws a flag when the number of entries is getting close to the number of rows you have pre-populated in the [Number] column and sends you an alert.

    =IF(AND(Number@row = MAX(COLLECT(Number:Number, [ID-Name]:[ID-Name], @cell <> "")), MAX(Number:Number) - 15 < Number@row), 1)

    Set up a daily automation to send you an alert when that trigger column becomes flagged, and you should get notified when your sheet needs more numbers. Since the rest of the columns are column formulas, that should be the only column you need to manually adjust. The -15 portion in the formula below is how many rows are left when you get the alert. If you want an alert when you have 30 blank rows left in your summary sheet, you would adjust that to -30.

«1

Answers

  • ✭✭✭

    So here you see three different people with the same Meeting ID. There are reimbursements and authorizations. I would like each person to appear on one line with the total Authorization and Reimbursements amounts. If possible I would like to have two columns for each expense type, one for Reimbursements and one for Authorizations. I am trying to keep track of what has been submitted, what has already been reimbursed, and what is still pending. The totals reimbursement and authorization should equal out to zero but I want to easily be able to find where the issue is.

  • ✭✭✭✭✭

    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…………………………………

  • Community Champion

    I would suggest creating a second sheet. We will start with a Sheet Summary field called "Meeting ID" which is where you can put a meeting ID.

    The first column in the sheet will be a text/number column (called "Number" in this example). This will be manually populated with numbers starting at 1 and going to however many you think you will need at the maximum (plus a little buffer just in case).

    Next column would be another text/number column (called "Name" here). This will use a column formula of

    =IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Name Column}, {Source Sheet Name Column}, @cell <> "", {Source Sheet Meeting ID Column}, @cell = [Meeting ID]#)), Number@row), "")

    Next will be two more text/number columns (Reimbursements and Authorizations). You would use a SUMIFS in these.

    =SUMIFS({Source Sheet $$ Column}, {Source Sheet Name Column}, @cell = Name@row, {Source Sheet Type Column}, @cell = "Authorizations", {Source Sheet Meeting ID Column}, @cell = [Meeting ID]#)

    The above should get you what you need. Also note… There is no automation that can merge rows. You would need to leverage the API, the premium add-on Bridge, or some other similar third party app.

  • ✭✭✭✭✭

    Paul, wouldn't your solution require a new sheet for each meeting? I think the OP was looking for an answer that would make an ever growing second sheet that had 5-25 rows per meeting, 1 per attendee per meeting. That was why I went down the rabbit hole of figuring out how to determine and identify the numbers of attendees per meetings and number of meetings. I didn't quite get to the point of figuring out how to USE that information before deciding I MUST be on the wrong (or at least most complicated) track though.

    I'm bummed to here there is no automation for this (I've not used automation much - as is obvious from my answer I tend to default to thinking about how I'd address it in Excel and then emulating); this seems like an ideal use case for automation.

  • ✭✭✭

    Jgorsich is correct that I am trying to create an every growing list for the fiscal year. I would love if you could automate part of a row to copy to a new sheet instead of the whole role. Sounds like I might have to do at least some of this manually.

  • Community Champion

    I must have misunderstood. My apologies.

    Have you looked into a row report with grouping and summaries? I know it isn't the EXACT format you are looking for but grouping by Meeting ID then Person then Type and using the summary feature within the report to get your totals would be the absolute easiest way to view these metrics.

    If it HAS to be all in one sheet, we could do this with just an additional column or two in the second sheet that I outlined above.

  • ✭✭✭✭✭

    oofff… this one was tricky, but I think I found a way to do it. Mind you, it will mean periodically expanding formulas in 4 columns (the way they are written, a couple of them can't be column formulas), so it is far from ideal - but it seems to work in the two test sheets I set up.

    Note - anything with curly brackets are references to your actual data sheet. I tried to make the naming convention fairly obvious based on the names I recommended above.

    Start by doing everything I said above in the "Don't Do This" section. Call that final helper column in your summary sheet "Row".

    All of the rest happens in your summary sheet.

    Insert a row before your previous row 1, so that the value "1" in "Row" is actually in row 2.

    Insert a column to the right of that and call it "NeededRows" with the following formula entered on [NeededRows]2. Note that you will need to manually fill it downward but you can have a significant excess of fills - just always make it go down farther in the sheet than you need. (I THINK you might be able to convert this one to a column formula, feel free to test after)

    =IFERROR(MAX(COLLECT({DataSheet-AttendeeCycle}, {DataSheet-MeetingID}, INDEX(COLLECT({DataSheet-MeetingID}, {DataSheet-MeetingCounter}, 1), Row@row))), "")

    Insert another column to the right and call it "Meeting". This is another one you'll have to fill downward. In [Meeting]2 add the following formula and then fill it downward as far as you care to go. I THINK you might be able to convert this one to a column formula also:

    =IFERROR(INDEX(COLLECT({DataSheet-MeetingID}, {DataSheet-MeetingCounter}, 1), Row@row), "")

    Insert another column to the right and call it "MeetingCycle". This one can not be converted to a column formula and will have to be filled downward. In [MeetingCycle]2 enter the following formula and fill it downward as far as you care to go:

    =IF(Row@row = 1, 1, IF(COUNTIFS(MeetingCycle$1:MeetingCycle1, MeetingCycle1) < INDEX(NeededRows:NeededRows, MATCH(MeetingCycle1, Row:Row, 0)), MeetingCycle1, MeetingCycle1 + 1))

    The following columns CAN all be column formulas and it is highly recommended that they are.

    Make a column called "MeetingID" and enter the following formula:

    =INDEX(Meeting:Meeting, MATCH(MeetingCycle@row, Row:Row, 0))

    Make a column called "AttendeeCycle" and enter the following formula:

    =COUNTIFS(MeetingID:MeetingID, MeetingID@row, Row:Row, <=Row@row)

    Make a column called "Traveler Name" and enter the following formula:

    =IFERROR(INDEX(COLLECT({DataSheet-TravelerName}, {DataSheet-MeetingID}, MeetingID@row, {DataSheet-AttendeeCycle}, AttendeeCycle@row, {DataSheet-AttendeeCounter}, 1), 1), "")

    Now you've got a list of meeting IDs and attendees that you can do sumifs() on for your actual reimbursements and authorizations, one row per meeting per attendee and it will automatically fill in as you fill your original data sheet!

  • Community Champion

    @Jgorsich There is a way to do it with column formulas without having to go crazy with a bunch of helper columns (will still require a few though). I have done something very similar before. It was the same concept but different use case.

    I haven't outlined it here yet simply because I wanted to see if using a report will work. While technically not the same exact layout, it does display the same info.

    Using the sheet is definitely more complicated, so I am hoping that we can save a little time and avoid all the hassle of formulas (even though it is a lot of fun writing them out haha).

  • ✭✭✭✭✭

    @Paul Newcome If there is anyone that I'm confident can take one look at my formulas and come back with a legitimate "Nah, there is an easier way", you are definitely that person… 🙂 When I first started using Smartsheet I spent at least one afternoon trolling through your history of responses purely to get a handle on what kind of unique weirdness is required.

    If you DO feel like posting a method with column formulas, I'd be quite interested to read it - I kept getting stuck on circular formulas that broke my test sheet when I tried it and resorted to the filled versions above to enable something like a "check every cell above this one and do logic on them" command. I've definitely been forced to use this kind of brute force method in my sheets before (when I need a summary like a report would provide but need to do math on it afterwards) and would love to learn a new trick!

    And I agree on the "fun" part of it - this was a pleasant break from the tracker I'm currently compiling on document status…

  • ✭✭✭

    @Jgorsich

    Thanks! I will try this tomorrow morning. I am leaving early today and my brain hurts from this and working on a separate project using SQL 😣

  • Community Champion

    @Jgorsich The basic idea is that you will still pre-populate the rows with 1 - whatever. But then we use a formula that does a couple of things to basically output a meeting ID on every row and using some math logic to jump to the next one based on how many come before it. If the meeting id is a number, we can leverage that. Otherwise we would use a helper column on the source sheet to basically label the meeting ids as first, second, third, etc..

    Essentially a formula that can count how many unique entries for the first Meeting ID. Then an IF statement that says if our manually populated number column is less than or equal to that number, output the first Meeting ID. But we would have it a little more ambiguous so that it scales up.

    Sometimes I will use a helper column to output the numbers themselves such as

    1

    2

    3

    4

    5

    1

    2

    3

    4

    5

    6

    7

    1

    2

    3

    resetting at 1 when we hit the limit for the meeting. Then I can just use this in an INDEX/COLLECT to pull the name over.

    I'll see if I can find some time to put together a quick sample for you at some point this week.

  • ✭✭✭✭✭

    @Paul Newcome - that'd be awesome, thanks! I can see how that would work with the exception of the "jump to the next based on how many come before it" bit - I could make that work with my first hypothetical meeting (where the row numbers corresponded to the count of rows for that meeting) but on the second I was hitting issues that I couldn't resolve without either going the non-column route or hitting a circular error. For the life of me I've yet to figure out how to include logic for "how many come before" while preserving something that is allowed to be a column formula (in essence, how to do the old excel trick of A$1:A1 so it fills down to become A$1:A14 a few rows down in the same column I'm writing in - works like a champ to do "row:row, < = row@row" if it is a different column).

    Though, now that I think about it, given that in the proposed answer I gave to the OP I WAS able to get the meeting IDs and number of needed rows with something that could have been column formulas, it seems like possibly doing a formula around mod() based logic or even simply sum() logic COULD work by making the actual count be externalized to the data - rather than counting how many "meeting 5"s I have, just see if I'm at the right row count for the total of meetings 1/2/3/4/5 … I'm looking forward to seeing your sample! Incidentally, given that it took me a good couple of hours to wrap my head around it, I also fully understand if you don't get around to that sample. Cheers!

  • Community Champion
    Answer ✓

    Once I started building, I realized this was actually a lot less complicated that I originally though.

    Insert a helper column on the source sheet that combines the meeting id and name into a string.

    =[Meeting ID]@row + "-" + [Traveler Name]@row

    In the summary sheet, insert the [Number] column that is manually populated with numbers starting with 1 at the top of the sheet.

    Then a text/number column called "ID-Name" with this column formula:

    =IFERROR(INDEX(DISTINCT(COLLECT({Master List: ID-Name}, {Master List: ID-Name}, @cell <> "")), Number@row), "")

    [Traveler Name] is:

    =IF([ID-Name]@row <> "", SUBSTITUTE([ID-Name]@row, [Meeting ID]@row + "-", ""))

    [Meeting ID] is:

    =IF([ID-Name]@row <> "", LEFT([ID-Name]@row, FIND("-", [ID-Name]@row) - 1))

    The reimbursement and authorization columns are SUMIFS suing the [ID-Name] column as one of the range/criteria sets, and then the difference column is simply one minus the other.

    You can take it another step and put in a flag column that throws a flag when the number of entries is getting close to the number of rows you have pre-populated in the [Number] column and sends you an alert.

    =IF(AND(Number@row = MAX(COLLECT(Number:Number, [ID-Name]:[ID-Name], @cell <> "")), MAX(Number:Number) - 15 < Number@row), 1)

    Set up a daily automation to send you an alert when that trigger column becomes flagged, and you should get notified when your sheet needs more numbers. Since the rest of the columns are column formulas, that should be the only column you need to manually adjust. The -15 portion in the formula below is how many rows are left when you get the alert. If you want an alert when you have 30 blank rows left in your summary sheet, you would adjust that to -30.

  • ✭✭✭✭✭

    Yeesh… yeah, that makes it WAY easier. Can't believe I didn't think to collapse meeting ID and Traveller name to a single column so Distinct() could work.

    Excellent solution!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions