Cross sheet reference strategy

Options

Hi Folks,

We have two sheets, which are used by two stakeholder groups. I've populated sheet B with some of the data from sheet A (the main sheet). To do this, there is a unique and corresponding ref on both sheets, and this has been used for a vlookup formula. We've now hit the max cross references and I've converted the vlookup formula on sheet B with an Index/Match formula.

This has been working well, but with vlookup, we could pre-populate the blank rows on sheet B with the vlookup formula, then once data hit sheet A, it would auto-populate. The only thing we needed to ensure was the constant corresponding unique reference ID on sheet B.

Now that I've converted the vlookup to index/match, this no longer works. When attempting to copy the index/match formula to a blank row on sheet B, it will clone the data on the last row with data, rather than the empty formula.

A couple of questions on this:

1) Is there anything we can do to restore the earlier functionality we had?

2) Is there any way we can find out what our number of cross sheet references is?

Appreciate any help.

Thanks, Jason.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 09/22/20
    Options

    Hi @Jason Murphy

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jason Murphy
    Options

    Hi Andrée,

    Thanks very much for your note. Unfortunately, due to confidentiality, I can't share the sheet. I hope this generalised example might help explain things:

    Sheet A

    Column 1 (Ref) / Column 2 (Name) / Column 3 (Email)

    Row 1 Data

    Row 2 Data

    Row 3 blank

    Sheet B

    Column 1 (Ref) / Column 2 (Name)

    Row 1 Formula-Data

    Row 2 Formula-Data

    Row 3 Formula

    To extract 'Name' from Sheet A to Sheet B, we were using vlookup. We then exceeded the 100,000 cross sheet reference threshold.

    I converted the vlookup formulas to Index/Match. This has delayed our threshold for the time being.

    In the example above, when using vlookup, Sheet B - Row 3 would contain the blank cell with the vlookup formula. Once data was entered on Sheet A - Row 3, the data would populate on Sheet B.

    Now, after converting to Index/Match, this no longer applies.

    In the scenario above, when Sheet B - Row 3 has no corresponding data from Sheet A, it simply copies the data Sheet B - Row 2.

    Do you know if there is any way to replicate the scenario we had when we were using vlookup?

    The other part of my question was, is there any way we can determine the number of cross-sheet references so that we can avoid any major issues going forward?

    Many thanks,

    Jason.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Jason Murphy

    I hope you're well and safe!

    Excuse the late response.

    Crazy times and I missed coming back to you.

    Have you solved it, or do you still need help?


    Be safe and have a fantastic weekend!

    Best,

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!