Creating sub-sheet from master sheet

KMH Spinner

Hi Paul! I was just coming back to report that everything went swimmingly for a couple of days and then today we added a couple of new orgs and things went kablooey on Sheet 2. I really appreciate your thorough explanation! I'll try that now. 😀

Paul Newcome

@KMH Spinner If you are unable to get it working, feel free to post some screenshots.

KMH Spinner

Hi @Paul Newcome. Unfortunately the new one isn't working either. Everything went swimmingly until we deleted a row in Sheet 1 for an org we are no longer working with. I am completely stumped.

Fortunately I've taken to exporting to Excel frequently so I won't have to rebuild all the data from scratch. I could go to Excel if I have to, but I'd rather keep everything in Smartsheet for ease of acess.

Paul Newcome

Can you post some screenshots so we can see exactly what is happening?

KMH Spinner

@Paul Newcome I'll gather some later today. Thank you for your continued support!

Paul Newcome

No worries at all. I'll keep an eye out.

KMH Spinner

Hello @Paul Newcome ! Sorry for the radio silence—we had a lot of fires spring up right before the US Thanksgiving holiday. Here is a straightforward example of what is happening, which is easy to trace because only one row was added to the master sheet when this happened.

This is the Funding sheet (which I called Sheet 2 above). The formula in Organization is what you suggested =INDEX({RAP Master Org}, MATCH(SMALL(COLLECT({RAP Master Autonumber}, {RAP Master Incentive Checkbox}, @cell = 1), [Number for Formulas]@row), {RAP Master Autonumber}, 0))

The formula in Number of Committed is =INDEX({RAP Master Est Number}, MATCH(Organization@row, {RAP Master Org}, 0))

The formula for Date Awarded is =INDEX({RAP Master EL FE}, MATCH(Organization@row, {RAP Master Org}, 0))

The data in Amount Awarded per, Agreement End date, Total Paid BY, and Total Paid OY1 I enter manually. Total Awarded and Total Paid By & OY1 are simple arithmetic formulas.

We added a new contact that starts with A in the Master sheet (which I called Sheet 1 above). For ease of working with the Funding sheet, we sort alpha by Organization. What you are looking at is the end of the alpha order in the funding sheet after the new contact was pulled in. What you can see is the following: For Organization 1, the information in Number of Committed and Date awarded are correct--they are being pulled in from the RAP Master and being matched appropriately. However, the other information in that row belongs to Organization 3. Also, the $500 in the Amount per column above Organization 1 belongs to Organization 2.

Resorting by Number for Formulas does not "undo" the data problems (not that I expected it would). Similar data scrambling happens if we keep it sorted by Number for Formulas and are adding or deleting rows from RAP Master.

The formula numbers in both sheets do not change, for example, if we add or delete a row, Organization 1 remains Number 62 and Organization 3 remains 64, even after alpha sorting. It's working the same on the RAP Master with the autonumbers. The question is how to get the information manually entered on each row in the Funding sheet to stay with its organization/row.

Thank you for slogging through this wall of text. I hope it makes sense. Please let me know if it doesn't.

Paul Newcome

Let me make sure I understand...

You inserted a new row in "Master". It pulled in correctly (initially) to "Funding". You then sorted "Funding" by the Organization column, and that's when things went wonky?

KMH Spinner

I think the data scrambling happens before the sort so that info may be a red herring. Let me run a check.

KMH Spinner

Still working on it, but ran into some trouble. I had the original Funding Organization column hidden. I had kept it to compare when I put the new formula in. I tried to delete it, but then got this when I went to save the Funding sheet:

I tried editing the column name, the column properties, everything, but get the same error every time I try to delete that column. I can safe perfectly fine if it is there. It can live hidden on the sheet if needed, but I thought it odd behavior.

I have to leave work for the rest of the day, so I'll catch up tomorrow. Thank you again for your help!😀

Paul Newcome

@KMH Spinner You may want to reach out to Support about that error message.

KMH Spinner

@Paul Newcome Long time no see! I hope you have been well! I will be out of the office after today until Jan 3, so I am not looking for any speedy answers, even if you happen to be in today.

I'm finally getting a chance to get back to this sheet, which I'd really like to get to work by the end of January so I can have all my data in one place. Weirdly, I was able to delete the troublesome column in Edge. I don't know why it wouldn't work in Chrome. However, I'm still having data get misaligned in ways it shouldn't (theoretically).

Here's a reminder of what the sheet looks like (in part):

I currently have the sheet sorted alpha by Organization. Here you can see that Org F and Org G are new and they have the appropriate manual number in the column to the left. Depending on what I'm doing, I resort by Number for Formulas and Organization.

This is how I understand the formula you developed:

=INDEX({RAP Master Org}, MATCH(SMALL(COLLECT({RAP Master Autonumber}, {RAP Master Incentive Checkbox}, @cell = 1), [Number for Formulas]@row), {RAP Master Autonumber}, 0))

SMALL(COLLECT...) returns the Master Autonumber from the Nth smallest row that has the Incentive Checkbox = 1; the number of the Nth row is generated from the [Number for Formulas] in the current sheet

MATCH returns the row number the Master Autonumber is found in

INDEX returns the Org Name in the row number found by MATCH.

I set up the COLLECT Test column to see whether the SMALL(COLLECT...) part of the formula is working, and it is.

Therefore, I would think the whole formula would work without fail. However, here is an example cell history from the Organization column. (Note: A, B, etc. here do not correspond to the letters in the picture above.)

This is the bit that completely confounds me. No matter how I sort the sheet, the number in the COLLECT Test column remains the same, i.e., it pulls the Nth row number corresponding to the manual Number for Formulas. It does not jump around. However, as you can see, clearly the values in the Org column do. I assume this is why all the information I've entered manually in this sheet gets out of alignment with the org.

KMH Spinner

Hello @Andrée Starå and @Paul Newcome

I hope you both are well!

This is the formula that collects the organization name from Sheet 1 (RAP Master) and puts it in Sheet 2.

=INDEX({RAP Master Org}, MATCH(SMALL(COLLECT({RAP Master Autonumber}, {RAP Master Incentive Checkbox}, @cell = 1), [Number for Formulas]@row), {RAP Master Autonumber}, 0))

After much frustration I just figured out the problem, which seems so obvious now!

If we enter a new contact for X organization on Sheet 1 and that contact needs to be the funding contact (say the original contact has new responsibilities), then that contact gets an autonumber at the bottom of the list on Sheet 1, currently 3200 something, which is how the autonumbering should worked. However, when the formula on the other sheet runs, the new contact for organization X is no longer the nth smallest--we'll say 65th--it's now the 100th smallest. That's how the organization names get disconnected from the information that has been manually input. For instance, the invoice data I entered manually on the 65th row in Sheet 2 should now be on the 100th row, but it stays on the 65th row.

So I need to figure out how to make this work without the SMALL function so that the org and its information stay together. Any ideas where to go from here? Thank you!