Calendar link

richard_abra
richard_abra ✭✭✭✭
edited 02/22/24 in Formulas and Functions

Hi there, i need some help with linking one sheet to another.

I have a Active Orders sheet which at the end you select two dates (template date & 14 days later the fit date) I want this to then go to the selected date on our template and fit planner. We dont use calendar view, just a sheet with a vertical calendar. Ill try and explain better below.

You manually select the dates you want on the far right, then i want this to populate the hightled cells (in one if poss) in the planner in the second screenshot.


Hope that makes sense!


Thanks


Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭

    Hopefully, I'm grasping what you are hoping to achieve....In your destination sheet, will the data from the source sheet always be going into the same column (i.e. [Kayne Wymer]@row)?

    If so, a few notes here. In your source sheet, if the fit date is always 14 days after the template date, I would recommend writing a column formula in to the effect of =[Template Date]@row+14. Assuming you don't have dependency settings that will exclude certain days, this should auto-populate the fit date 14 calendar days from the template date.

    Additionally, in your source sheet, I would create a helper column (can remain hidden once set up; Let's call this column "Planner Helper" for this example) with either an addition or join formula. The Join formula requires all the fields to be in sequence. So, I would default to the following in your helper column: =[Quote Number]@row+" - "+Customer@row+" - "+[Project Reference]@row. This formula will join the three highlighted fields into one cell, allowing you to just pull from that cell in your destination sheet.

    In your destination sheet, I would ensure your "Date" column is actually formatted as a date column. This will help with the next stage of grabbing info from your source sheet. Note: If the Date column is currently your primary column, it is stuck being formatted as a text column and you'd have to add another column.

    Beyond that, you are likely best off using an INDEX(Collect) formula (with helper columns if you are needing to pull based of both fit and template dates). Without knowing more, though, it's hard to advise. What columns do information need to be populated in and based off of what conditions...?

  • richard_abra
    richard_abra ✭✭✭✭

    Thanks i will creat those columns now. The wont always go on to the same person. However, so long at is goes to that date we can move it around as we see fit. The planner is always changing anyway/

  • richard_abra
    richard_abra ✭✭✭✭

    [Template Date]@row+14 cant get this to work!

  • richard_abra
    richard_abra ✭✭✭✭

    Can anyone help with the formula needed to put this helper cell in both Template and Fit dates on my temp and fit planner?



  • richard_abra
    richard_abra ✭✭✭✭

    anyone? is this even possible?

  • Hi @richard_abra

    To bring data from one sheet to another you can use a JOIN(COLLECT. I'm using JOIN instead of INDEX just in case more than one row has the same date. Here's more information: Formula combinations for cross sheet references

    What I would do is have one column to bring in all the "Helper" column details into your master planner sheet. For example:

    =JOIN(COLLECT({Helper Column}, {Template Date Column}, Date@row), "// ")

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • richard_abra
    richard_abra ✭✭✭✭

    Thank you so much!! This is the issue I was now getting and didn’t think there was a work around !

    what about a way of the cell returning an error if something is already booked in for that day ? Possible ?

  • Hey @richard_abra

    Sure! You can add a new "flag" column on your intake sheet that checks if that date has been selected already.

    =IF(COUNTIFS([Template Date]:[Template Date], [Template Date]@row) > 1, 1, 0)

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • richard_abra
    richard_abra ✭✭✭✭

    This Almost works perfectly..!



    When i enter the formula it seems to work backwards, and highlights all the flags. Also for some reason, i can only get this to work in the template date, i need the same for fitter etc...


    Also, i would like to "join" another cell from another sheet. Is this possible? something like this

    =JOIN(COLLECT({4 -Active Orders Range 1}, {4 -Active Orders Range 2},Date@row, {J - Holiday Range 1}, {J - Holiday Range 2}, Date@row))

  • Hi @richard_abra

    Can you clarify how you're using the formula?

    It looks at the Template Date in this current row and checks if that same date is listed anywhere else in the column. If it is, then it will check the flag (the formula is put in the Flag column).

    You can change out the column you're looking into to find duplicates in any other column as well:

    =IF(COUNTIFS([Fit Date]:[Fit Date], [Fit Date]@row) > 1, 1, 0)


    You can join content from another sheet! First, write the separate formula that brings the data in, then use + to add the other content with it.

    So:

    =JOIN(formula) + JOIN(formula)

    or

    =JOIN(formula) + [Current sheet cell]@row + "other content"

    Does that make sense? See: Combine (concatenate) text or values from two or more cells with a simple formula

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • richard_abra
    richard_abra ✭✭✭✭

    struggling to get this to work. ive got


    =JOIN(COLLECT({F - Active Orders Range 1}, {F - Active Orders Range 2}, Date@row), "// ") + JOIN(COLLECT({J - Holiday Range 2}, {J - Holiday Range 1}, Date@row), "// ")

    Which dosnt error but equally dosnt pull the data.

    Starting from the top i need the holiday data to populate in




    And then also for the 'job info' helper column to populate a specified date in this sheet against whoevers name i decide to do the job.

    so ultimatley theres 2 sources (holiday,active orders) Feeding the two schedulers.


    hope this makes sense!

  • richard_abra
    richard_abra ✭✭✭✭

    Ok ive got this to work in probably the most complicated way but it does work!!! Im not just feeding the sheets with seperate planners =join(collect( into a helper and reference the helper cell to where i want it. only down side is that the cells are fixed on the planners and to make changes you have to go to the relivent planner.

    But also, where i select the dates here i only populates one cell obviously but some jobs could take a week to make. can anyone think of a way to maybe have a number column next to the date which denotes how many days the job will take and also, fill the same info from the helper into the number of days.

    This is the calendar and as you can see some jobs take longer than one day, some do.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!