Use INDEX COLLECT to identify Row and Column of another sheet

dhall

I have a sheet that is filled out by an individual and then when the submission is complete, it will create a unique code.

I want to copy their data over to another sheet on an individual basis. Context is each week the person will fill out their form and update their results. Based on their email address and the week they select, that information will get pulled to their sheet.

So I have it successfully pulling the Incrementing Unique Code. From that Unique Code and a Label, I want to pull over the information that is in certain columns. So the Unique Code would figure out the Row, and the Label would figure out the Column.

=INDEX({L2F - Sheet}, COLLECT({L2F - OBCODE}, {L2F - OBCODE}, =[Week 1]8), COLLECT({L2F -Label}, {L2F - Label}, =Label@row))

With this, I get Invalid Data Type. Here's the row and sheet it should be pulling from

I tried creating a Helper Column (HLP-Phase) that is a simple formula "=Phase@row". I figured this would clear my invalid data type error since the Phase column is a dropdown.

My references are as follows:

L2F - Sheet -- The entire sheet the form submits to

L2F - OBCODE -- the Onboarding Code Column on the sheet

L2F - Label -- the first row of the sheet

The reason why I'm trying to use INDEX COLLECT in this manner is so I don't have to create potentially 100 references and their respective formulas.

Thoughts? I figured row_index and column_index would pinpoint the cell I'm looking for but it doesn't work the way my brain thinks it does.

Paul Newcome

You want to look into

=INDEX({Whole Sheet}, MATCH(OB Code), MATCH(Label Row))

Of course using the proper syntax for the MATCH functions.

Paul Newcome

Your formula syntax is off. What do each of your cross sheet references cover?

dhall

Hi Paul, thanks for the response! Here's screenshots of my cross sheet references:

L2F - Sheet (the whole sheet)

L2F - OBCODE (just the Onboarding Code column)

this column is automatically filled to create unique IDs for each entry.

L2F - Label (row 1 of the sheet. The labels here would match the labels on the first column of the destination sheet)

dhall

To add to my previous reply, I did notice there was a space that was missing between the dash "-" and "Label", but fixing it made no change to the results.

I also tried this formula syntax and knew it wouldn't work, but gave it a shot anyways:

=INDEX({L2F - Sheet}, {L2F - OBCODE} = [Week 1]8, {L2F - Label} = Label@row)

dhall

@Paul Newcome , my hopes of not having to create a reference for each column seem to be diminishing.

If I do need to make a reference per column on the data sheets, I'm looking at 41 (give or take 2) references.

My follow-up to that is this: There is a limitation of 100 cross sheet references. Is that specific to the sheet that the reference is created on?

For example, my data source sheets could potentially have 6 sheets pulling from them from, and if each sheet has say 40, that would put the data source sheets at over 240 references (split across two sheets, so about 120 a piece).

I'm looking to see what my options are before I start creating tons of references and formulas to not have them work out. Thanks for the assist here!

Paul Newcome

You want to look into

=INDEX({Whole Sheet}, MATCH(OB Code), MATCH(Label Row))

Of course using the proper syntax for the MATCH functions.

dhall

Hey @Paul Newcome ,

That worked! I had to make sure to include the ",0" for not sorted in the MATCH portions as the default of 1 returned "#NO MATCH" error.

Here's an example of my new formula:

=INDEX({L2F - Sheet}, MATCH([Week 1]8, {L2F - OBCODE}, 0), MATCH(Label@row, {L2F - Label}, 0))

The below is for anyone who is interested in what I did to overcome a few things I encountered.

1) Copying and pasting the formula across several rows/columns (labels and weeks, respectively)

1a)Get the formula in the first cell within a column correct. Once correct, copy the formula, not the cell. Highlight all other cells that will use the exact same references. Paste. The Label@row should do all the work and you'll still keep [Week 1]8. Dragging in either direction messes up the MATCH(<defined Cell>

1b) Moving over to the next column (for the next week), paste your formula in the first cell of that column. Edit the formula and change your column cell. In my case, I changed [Week 1]8 to [Week 2]8. Repeat step 1a.

1c) continue through 1b and 1a repeatedly until you complete out all your weeks. I had 16 weeks so it didn't take me long.

2) my source sheets had to have helper columns in them to convert star ratings to actual numbers. Otherwise "Empty" shows up and I need a 0 for the metrics I'm tracking.

2a) create your IF formula to convert star rating to a digit. There are several all over this community, but here's mine in case you need an example.

=IF([Star Column Name]@row = "Empty", "0", IF([Star Column Name]@row = "One", "1", IF([Star Column Name]@row = "Two", "2", IF([Star Column Name]@row = "Three", "3", IF([Star Column Name]@row = "Four", "4", IF([Star Column Name]@row = "Five", "5", "0"))))))

Now, the problem I ran into here was interesting, but I figured it out. I had to have this formula as a column formula. However, if it's a column formula, I couldn't use a "Label Row" as used in my previous posts in this thread. But, there is a way around it.

2b) Convert the formula to a column formula. Repeat this for all the columns in your source sheet. Once done, revert it back to a Cell Formula. The formula will stick around for any rows you already have existing.

2c) Rename the appropriate row 1 cells in the correct columns with your helper labels. So instead of labeling the Star Column with the label, I moved the label to the respective column the formula converted the star to a value for.

2d) Since I'm using forms to populate the source sheets, I had to make sure that the setting on the form allowed new entries to enter the "bottom" of the sheet. Top of the sheet would have screwed up my Label reference. How this works is, when a new row is inserted, it copies the formulas that are applied to the row above it. So what was previously a column formula but is now a cell formula, that formula gets copied to the row beneath it.

Note: This is only in testing over this past day. I haven't done super extensive testing on this yet or allowed the actual audience to start using the forms yet.

If this goes haywire, I'll update this thread. But anyways, @Paul Newcome , you are a lifesaver. I went from about 50 references on a sheet down to 14. Thank you!!

Paul Newcome

Happy to help. 👍️

If you wanted to dragfill, you could use absolute references:

=INDEX({L2F - Sheet}, MATCH([Week 1]$8, {L2F - OBCODE}, 0), MATCH($Label@row, {L2F - Label}, 0))

This locks in the parts that you don't want as variables but allows the dragfill to update the rest as needed.