INDEX(COLLECT( )) where some rows are blank

KMH Spinner

Hello Community!

I'm missing something here, but I'm not sure what. In this sheet I need to pull a date from a master sheet.

The formula works perfectly for the first two organizations, but then the next one it pulls is simply the next cell in the date column in the master that isn't blank. It isn't the date that goes with the third organization. When I tried without ISDATE and just put <>"" for the criteria, I got the same results.

You helped me figure out that I need a helper to pull the organizations in, which is working wonderfully (😀!), but there are no empty cells in the Organization column in the master sheet, though not all organizations in the master sheet are receiving funding. I don't know if this is a factor here. Do I need to refer to the Organization column perhaps?

In a nutshell, I need to pull the Date Funding Awarded from a column in the master sheet if it isn't blank. I need to match (do I need a nested MATCH?!) the organization with the date it received funding, both of which come from the master sheet.

Many thanks in advance for any insight you can provide.

Ryan Sides

Hi KMH, without talking in formulas, are you trying to get the date funded from the Master by looking for the Organization Name?

If so, does the Organization and Date combo exist only once on your Master? Or could an Organization be funded more than once?

Thanks,

Ryan

Kelly Moore

Hey @KMH Spinner

Building on Ryan's questions... is it possible for you to share the data from your source sheet? We're trying to determine if there is only one criteria you need as you match your destination sheet to your source sheet, or if multiple criteria determine what date is returned for each row. Perhaps you could mock up the data to designate the Organizations as A, B, C, D, etc, on both sheets. Assuming row 3 on the screenshot above is Org C, what date from the source sheet (if you can share) would you expect the formula to return?

Kelly

KMH Spinner

Thanks for your responses @Ryan Sides and @Kelly Moore!

(1) An organization receives funding only once. Organization names repeat in the master if we have multiple contacts for them. I've used parent & child relationships in the master for that. As of now we have ~2800 organizations listed in the masster, but only ~40 have received funding. The organization is pulled from the master sheet into the funding sheet based on a checkbox column in the master.

(2) For Organization C, I expect the date Organization C executed the agreement (Executed Date column on the Master) to populate in the Date Funding Awarded column in the incentive funding sheet.

Here is a snapshot. Organization B pulls the correct date from the master into the funding sheet (i.e., from the same organization's row in the master), even though there are 6 blank cells in the Executed Date column (on master sheet) between Organization A and Organization B, which leads me to believe that blank cells might not be the problem.

For Organization C, I would expect the date below to populate to the funding sheet because it is the date from Organization C's row in the master:

Note: In the screen shot in my original question I had typed the correct date in so I didn't lose my place. (I don't know if it matters that we have two contacts for this organization.)

However, below is what is pulled from the Master. This row is ~100 rows above Organization C, but it is also the first non-blank cell in the Executed Date column after Organization B's date. There are approximately 30 rows between Organization B and this Not Funded organization. There are approximately 100 rows between Organization B and Organization C.

Very appreciative of a shove in the right direction if you can! Please let me know if this is as clear as mud and I'll try again.

Ryan Sides

@KMH Spinner I'm not 100% sure here, but here goes. When you say "The organization is pulled from the master sheet into the funding sheet based on a checkbox column in the master." Is this because there is a "copy" automation that copies this row to the Funding sheet?

If that's the case, your Executed Date is already being pulled into your Funding sheet.

So you need to point your Date Funding Awarded column to that column ( = [...Executed Date]@row)

If you are not using the copy function, are you expecting the Funding sheet to grab the correct Date by looking for the Organization Name? And for it to disregard all blank date rows for that Org's name?

KMH Spinner

Thank you for sticking with me this far @Ryan Sides!

Not using automation for this. Basically this funding sheet has to keep all the financial info that should not be kept on the master [insert complicated, boring reasons here]. For the funding sheet all I need from the master is the organization name and executed date.

I have a checkbox column on the master called Receiving Funding. If that is checked, i.e., =1 then the formula I have in the org column in the incentive sheet slurps up the org name and adds it to the org column in the funding sheet. (I've got the formula dragged way down to compensate for future additions.) This is the formula I'm using for that—Range 1 is the checkbox column on the master, For Archive Formulas is the autonumbered helper column in the funding sheet:

=INDEX(COLLECT({RAP Master Org}, {RAP Master Range 1}, 1), [For Archive Formulas]@row)

So I started there trying to see if a similar formula would work for the dates. What I was thinking is asking the formula to check the org name in the row in the incentive funding sheet using the helper column, go over to the master, find that org and grab the executed date from the row and put it into the incentive funding sheet for the corresponding row. And... now my wheels are turning a bit. Right now my formula is this:

=INDEX(COLLECT({RAP Master EL FE}, {RAP Master EL FE}, ISDATE(@cell)), [For Archive Formulas]@row)

So this is what I think this formula does: it tells Smartsheet that for each of the helper column rows in the incentive sheet, go look at the executed column in the master, if that column has a date in it (or isn't empty) bring it into the incentive funding sheet.

And now that I'm looking at this some more, I think I've fundamentally broken or misunderstood something. 😂

Ryan Sides

@KMH Spinner You could use something as simple as an INDEX/MATCH that goes to the master, looks for the org name and brings back the executed date.

=INDEX({RAP Master EL FE}, MATCH([Organization (Primary Col)]@row, {RAP Master Org}, 0))

Put this^ in your Executed Date column in your funding sheet.

Where this breaks down is if your Master has multiple entries for the same Org Name.

KMH Spinner

Thank you @Ryan Sides! (Sorry I fell off the earth for a couple of days--it's reporting season!)

Ryan Sides

No worries! Yeah, it's that time of year. 😁