mirror the invoice date on another sheet

02/20/21
Accepted

Im trying to say, if the service ticket name on another sheet matches the service ticket name on this sheet then the cell = the invoice date

Best Answer

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Samuel Dowdy Jr

    Try something like this.

    =VLOOKUP([Service Ticket Name]@row, {Log - Invoice Date}, 2, 0)
    

    The range, Log - Invoice Date, is the Service Ticket Name and Invoice Date column.

    Make sense?

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

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

    Hi @Samuel Dowdy Jr

    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.

    Make sense?

    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, [email protected])

    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 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    This is my log sheet for all customer invoices


    This is my master sheet that houses all service leads. I want the invoice date to populate based off the invoice log above


  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Samuel Dowdy Jr

    Try something like this.

    =VLOOKUP([Service Ticket Name]@row, {Log - Invoice Date}, 2, 0)
    

    The range, Log - Invoice Date, is the Service Ticket Name and Invoice Date column.

    Make sense?

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    I keep getting this...

    both clomns are set to dates

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    =VLOOKUP([Service Ticket Name]@row, {MASTER - Invoice Log Sheet Range 2}, {MASTER - Invoice Log Sheet Range 4}, 2, 0)


    This is what im trying and its not working

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

    @Samuel Dowdy Jr

    What happens if you allow it?


    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

    @Samuel Dowdy Jr

    Glad we got it working!

    Remember! 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    =VLOOKUP([Service Ticket Name]170, {MASTER - Invoice Log - Invoice Date}, {MASTER - Invoice Log Sheet Range 4}, 0)


    Still cant get the formula to work.

    I want it to match the invoice date if the service ticket column matches the service ticket column

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 02/23/21

    Dear @Samuel Dowdy Jr

    Hope you are fine, the formula that @Andrée Starå write for you is ok but the error you get that stop the formula from working is that you make the column [Invoice Date] in your master sheet is restricted to date, please try to edit this column properties and remove the check box in restricted to date only.

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    Im still getting incorrect argument

    =VLOOKUP([Service Ticket Name]170, {MASTER - Invoice Log - Invoice Date}, {MASTER - Invoice Log Sheet Range 4}, 0)

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

    @Samuel Dowdy Jr

    I'll reach out through email instead because it's working for me.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    I was hoping to get this resolved today. Can someone help with this?

  • Hi @Samuel Dowdy Jr

    The VLOOKUP should work, but personally I prefer to use an INDEX(MATCH formula so you don't need to reference a range but instead just select the 2 columns with the criteria.

    An INDEX(MATCH Works like this:

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


    So in your case:

    =INDEX({Invoice Date column}, MATCH([Service Ticket Name]@row, {Service Ticket Name}, 0))


    I would advise using the @row function instead of the row numbers, like in your previous formula. It helps the formula calculate faster since it doesn't have to search your sheet for the specific row.

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Samuel Dowdy JrSamuel Dowdy Jr ✭✭✭✭✭

    @Andrée Starå thank you for working with me on this. You were alot of help!

Sign In or Register to comment.