Index/Match between 2 sheets again

ChrisL

I have searched over and over again and I can't seem to get a formula that works.

I am trying to grab a range from this sheet

Whenever the LXM cell is checked.

I am wanting to put the column with the match in a helper sheet (below)

This hasn't worked:

Can anyone help me with the correct formula?

Thank you and much appreciated!

Kelly Moore

Hey @ChrisL

From the screenshot, it is not visible what information on the Helper Sheet (the destination sheet) is the reference to the data on your Sheet 1. In other words, what can Sheet1 (source sheet) use to know that the information in Sheet1 row1 goes into, for example, Sheet 2 row 33?

Or, if this is a 1 to 1 transfer of information between the two sheets, have you considered using the Copy Row automation on Sheet1 that is triggered when the Sheet1 LXM box gets checked. The automation would then copy the entire Sheet1 row to the next empty row in Sheet2.

Is this what you are hoping to accomplish?

Kelly

ChrisL

The LXM column in the source sheet , when checkbox is checked designates a row that I want copied to the helper sheet.

Kelly Moore

Yes, I understand that. Are you copying the entire row?

If yes, you would do this with automation , not with a formula.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/move-and-copy-rows

ChrisL

No I only want to copy a contiguous range.

Kelly Moore

Hey @ChrisL

On sheet1 (source)

1. Insert helper column [Row ID] from System Auto Number
2. Insert Helper Text/Number column 'CountChecks"
   1. =IF(LXM@row = 1, COUNTIFS([Row ID]:[Row ID], <=[Row ID]@row, LXM:LXM, 1))

On sheet 2 (destination)

1. Insert helper column [Row ID] from System Auto Number
2. Create the following formula, replacing Index term appropriately, for each of 3 columns
   1. =INDEX({sheet1 Program column}, MATCH([Row ID]@row, {sheet 1 CountChecks column},0))

TLDR:

There isn't a way to populate three columns of data directly using a formula in one single cell. There will need to be a formula in each of the three columns on the destination sheet (sheet2). (If, by chance, your license gives you access to the Bridge, let me know as this can change the approach).

Using what is visible in screenshots above, it is not apparent there is an identifier on both sheets that link the sheets, and more specifically, differentiate the rows from one another. In other words there is nothing on sheet2 that calls to sheet1 and says I need your row3 data. And, because there is no anchor of sorts on sheet2, there is nothing to force sheet 2 to freeze any data on that sheet 2-row vs overwriting that same row over and over when new data comes to sheet1.

To overcome this you need 2 helper columns on your source sheet and one helper on your destination sheet. Once we have this set up you can shove these helper columns to the far right and hide them if desired. Sheet1 Helper 1 is formatted as a Auto-Number/System Column - Auto-Number. Once saved you will see [Row ID] numbers in this row. The other Sheet1 helper column is formatted as Text/Number. I'm calling it CountChecks. You can call it whatever. I am assuming that you are not changing the order of rows on sheet1 prior to the checkbox getting checked, that is dragging rows up and down sheet1.

In CountChecks column (sheet1) insert this formula:

=IF(LXM@row = 1, COUNTIFS([Row ID]:[Row ID], <=[Row ID]@row, LXM:LXM, 1))

Now, on your Destination sheet "Helper for LMX Media Production Plans..." (the one I call sheet2) also create the helper column [Row ID] by inserting a new column formatted as a Auto-Number/System Column - Auto-Number. We will use the Index/Match set of functions and match sheet2 [Row ID] to CountChecks on Sheet1. As a reminder, when working with cross sheet formulas you must create the cross sheet reference - you cannot simply copy paste my formula below.

In the Sheet 2 [Program] column, create this formula.

=INDEX({sheet1 Program column}, MATCH([Row ID]@row, {sheet 1 CountChecks column},0))

This formula will need to be created for each of your columns. You must create a new Index reference by inserting a completely new reference (do not just edit the name in the formula) for each column. The Match portion will remain the same for all 3 columns you want 'copied'.

Reach out if you get stuck. Let me know if this works for you.

Kelly