mirror the invoice date on another sheet
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å ✭✭✭✭✭
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.
0
Answers
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.
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
@Samuel Dowdy Jr
Try something like this.
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.
I keep getting this...
both clomns are set to dates
=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
@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.
@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.
=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
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.
Im still getting incorrect argument
=VLOOKUP([Service Ticket Name]170, {MASTER - Invoice Log - Invoice Date}, {MASTER - Invoice Log Sheet Range 4}, 0)
@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.
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
@Andrée Starå thank you for working with me on this. You were alot of help!