Calendar link

richard_abra

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

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

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

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

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

anyone? is this even possible?

Genevieve P.

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), "// ")

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 ?

Genevieve P.

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)

richard_abra

https://community.smartsheet.com/discussion/comment/420758#Comment_420758

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))

Genevieve P.

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

richard_abra

https://community.smartsheet.com/discussion/comment/421029#Comment_421029

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

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.