Cross sheet data gathering

jb@59069

Hello again. We have reached a need for cross sheet data gathering finally and I'm struggling.

We are bringing on outside vendors that we will be billing clients as time rather than hard costs. Each of these vendors has variable rates they will be charging so each has their own sheet(s) which has a form for entering which client, Service Line, Category, time worked, rate code, formulas for performing calculations, etc. Some of this information then needs to be copied into the specific client master time sheet(s).

I have created an automation to copy a row when added from the vendor sheet to the client sheet, based on the Client column value. Works very well in testing except that it also created more columns in the client master time sheet. Since each vendor has unique rate code and other columns necessary, this will create a whole bunch of unwanted columns in each client's master time sheet.

I'm struggling with INDEX/MATCH to even get anything to generate, get a viable formula without errors. I need the values from 10(?) cells to get copied from the vendor's time sheets over to the client's master time sheet.

Each client master time sheet needs to be able to receive data from however many vendor time sheets we need to link to. Additionally, each client master time sheet needs to be able to accept nearly identical data from at least two (2) of it's own forms (already in place and working beautifully).

Am I asking the impossible?

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

I'm happy to help with this! It does sound like an INDEX(MATCH would be best suited to bring this information across.

Here's the basic structure of an INDEX(MATCH:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

Index Match with multiple criteria would be an INDEX(COLLECT. Paul has a great example of this, here.

Based on your two images, try something like this:

=INDEX({AM Column}, MATCH(Client@row, {Client Column}))

You would need to build out the same formula per-column, swapping out the initial range to look at the next column needed. So, for the AM Email column it would be this:

=INDEX({AM Email Column}, MATCH(Client@row, {Client Column}))

Let me know if this works for you! If not, it would be helpful to know the exact error you're getting, and to see screen captures of each of the 2 references in the cross-sheet reference pop-up window.

Cheers,

Genevieve

Unknown

This content has been removed.

Unknown

This content has been removed.

Unknown

This content has been removed.

Unknown

This content has been removed.