Creating sub-sheet from master sheet

KMH Spinner

Hello everyone,

I have been breaking my head over this and am probably doing something spectacularly stupid or missing something remarkably obvious.

I have a master sheet. I need to get data from one column linked into another sheet based on certain criteria. I tried index and match, which worked well for the first cell in the subsheet, but then realized I couldn't just copy the formula down the column on the new sheet (there are ~2800 rows on the master) and it might not update if we added new rows to the master.

I love reports, but cannot use them in this instance because I will have to add additional data (that we don't want in the master) and do all sorts of calculations in the subsheet.

In short, if [Column A, cell 1 Master] is in a row where [Column C, cell 1 Master] is equal to [value], then put the value from [Column A, cell 1 Master] into [Column X Subsheet]. I thought about automation, but from what I'm reading it seems I would have to copy a whole row to the other sheet, not just a cell.

Help please? Thanks tons!

Paul Newcome

I would suggest a text/number column (called "Number" in this example) where you manually enter as many numbers as you think you will need plus more as a bit of a buffer. If you think the maximum number of rows to pull is 300, then my suggestion would be 3500 or whatever you feel comfortable with (there is a way to set up a check column with an automation to alert you if you are running close to the end of your manually entered numbers and need to add more).

From there you would use something along the lines of...

=INDEX(COLLECT({Source Sheet Column A}, {Source Sheet Column C}, "[value]"), Number@row)

Andrée Starå

Hi @KMH Spinner

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest a text/number column (called "Number" in this example) where you manually enter as many numbers as you think you will need plus more as a bit of a buffer. If you think the maximum number of rows to pull is 300, then my suggestion would be 3500 or whatever you feel comfortable with (there is a way to set up a check column with an automation to alert you if you are running close to the end of your manually entered numbers and need to add more).

From there you would use something along the lines of...

=INDEX(COLLECT({Source Sheet Column A}, {Source Sheet Column C}, "[value]"), Number@row)

KMH Spinner

Aaahhh, that's the bit I was missing--a helper column. I knew it would be something that would make me smack my forehead when I saw it. Thank you! I will try that!

Andrée Starå

@KMH Spinner

Excellent! Easy to miss!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Happy to help. 👍️

KMH Spinner

Hello @Paul Newcome and @Andrée Starå!

Thank you for your past help with this problem. I just realized today that when I was working on adding some more things to this sheet that the formula I have for pulling the organization name into Sheet 2 works great, but I don't really understand why it works. As a memory aid, here is Sheet 2 showing the formula for pulling in the organization name from Sheet 1.

The organization is only pulled if the checkbox is checked (i.e., = 1). That part I completely understand. What I don't get is exactly what the helper column (Number for Formulas) is telling Smartsheet to do. I have helper columns in Sheet 1 and Sheet 2, but Sheet 2 has a mere fraction of the number of orgs that are on Sheet 1. So, where Org A is number 2 on this sheet, it is number 1150 on Sheet 1. That's where my brain is breaking down.

Obviously since it's all working, this is a low priority item. I just need to learn exactly what's happening so I can keep creating formulas for this project successfully. Thank you!

KMH Spinner

Actually, it's not working. One of my colleagues just added some rows to Sheet 1 and everything went haywire. :(

Paul Newcome

@KMH Spinner The COLLECT function is working exactly as you describe. Pulling in all of the Orgs based on the box being checked. To help figure out the next part, the COLLECT function is actually storing a list of ALL of those entries on the back end.

We then use the INDEX function to pull a single entry from that list, and the helper column with 1, 2, 3, 4, 5, etc. is telling it to pull in the first, second, third, etc. entry.

KMH Spinner

Thank you so much @Paul Newcome! I'm going to have to figure out what happened last week because a colleague added a row in Sheet 1 and all the information in the columns in Sheet 2 got displaced from the correct organization.

KMH Spinner

I have discovered a flaw in this strategy. If someone enters a new row somewhere in the middle of Sheet 1, for example we have a new contact for an existing organization, it is copied to Sheet 2 like it should. The list number for each organization in Sheet 2 that is held in COLLECT's memory changes as things move, BUT all the data I've entered into Sheet 2 for the corresponding organizations does not move with them.

To illustrate:

In this example NUMBER is the helper column to pull in the Org Name from Sheet 1. I have some other data from Sheet 1 I'm pulling in as well, but also some separate data I need to enter in Sheet 2 for each org. Amount is the data entered in Sheet 2 for this example. If someone enters a new row in Sheet 1 so that D & E get bumped down to numbers 5 and 6 in COLLECT's memory bank, the Amount does not move with them when the sheet is refreshed. So D will suddenly have 100 for its amount, E will have nothing and C2 will be 200. What do I need to do to ensure that if the org list order changes in Sheet 1, all the cells in a row in Sheet 2 shift with the org name?

By the way, if the proper etiquette is that I should open a new discussion rather than continuing an old one, please let me know.

Paul Newcome

No need to create a new thread. This is all related to the original post.

The easiest question to ask would be... Is it possible the data you are entering on sheet 2 can be calculated from the entries on sheet 1, or is it all brand new data? We can set it up so that it retains the original order based on when something is entered, but it may be easier to use formulas to generate the calculations with cross sheet references (if possible).

KMH Spinner

Thank you for replying! It's all new data that we're putting on Sheet 2. We'd rather not have the financial information about this project on Sheet 1 (the master sheet) if possible--that's what is on Sheet 2.

Paul Newcome

Ok. In that case you will need to insert an auto-number column with no special formatting on Sheet 1.

From there you would use something like this:

=INDEX(COLLECT({Org}, {Checkbox}, @cell = 1), SMALL({Auto Number}, Number@row))

KMH Spinner

Very interesting! I'll check out the SMALL function. I wish I could hit your Awesome button more than once!

Paul Newcome

In typing the below out, I realized this may not work exactly as expected though...

Here is the logic behind it...

The auto-number column generates a unique number on each row. That number stays with that row no matter where it moves to on the sheet.

The SMALL function pulls the nth smallest number. So

SMALL({Column}, 1) will pull the smallest. Changing that to a 2 will pull the second smallest, so on and so forth.

The reason I don't think this will work now is that we are basically just pulling in "Number@row". I think I may have gotten a little ahead of myself and not thought it out completely.

If that last one doesn't work, give this a try instead:

=INDEX({Org}, MATCH(SMALL(COLLECT({Auto Number}, {Checkbox}, @cell = 1), Number@row), {Auto Number}, 0))

The modification basically pulls in the nth smallest number from the auto-number column that is checked off and then MATCHes against it to get the row number from the sheet.