• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup Year Date and Time

B

BJ576

New Member
Hi,

What formulas can fill the Jobs sheet in the highlighted yellow areas from the appropriate year sheet?

Thanks
 

Attachments

  • Lookup Year.xlsm
    143.1 KB · Views: 6
Hello,

Thanks but it doesn't change for the year sheets. Please see attached. I used Unhide to unhide the sheets. When cell E1 of the Schedule sheet changes to 2021 the cell E1 in the Jobs sheet also changes to 2021. I would like to show if there are appointments in that year and for future years when E1 changes please.
 

Attachments

  • Calendar WIth Events Rev 11.8.xlsm
    133.7 KB · Views: 3
@BJ576

I did not get you point, it pulling out data based on the year in cell E1 of Jobs sheet.

What exactly you need. yo do not have any data on other sheets.

In a blank case it will just return 1st day of the year and first hour of the day.

I think you asked formula in yellow cells at first place.

Regards,
 
The jobs are created in the Jobs sheet. Then the time and day are set in the Schedule sheet which by VBA will post the time and day in the Year sheets. I want formulas in the Jobs sheet to retrieve the time and day for those appointments.

I made some appointments for this year in yellow and one in 2021 orange and one in 2022 pink to show what I would like to retrieve in the Jobs sheet please.

Thanks
 

Attachments

  • Calendar WIth Events Rev 11.9.xlsm
    140.1 KB · Views: 4
Last edited:
BJ576 said:
The jobs are created in the Jobs sheet. Then the time and day are set in the Schedule sheet which by VBA will post the time and day in the Year sheets. I want formulas in the Jobs sheet to retrieve the time and day for those appointments.

I made some appointments for this year in yellow and one in 2021 orange and one in 2022 pink to show what I would like to retrieve in the Jobs sheet please.

Thanks
Click to expand...
Hi,

Your this requirement is different from your earlier requirement.
If I remember correctly, your earlier requirement is to fetch date and time based on year in E1 cell. But now you are asking to make a summary sheet based on all the years.

Regards,
 
I am sorry for the confusion. I want to lookup the dates and times for the Jobs in the year sheets. Does that help?
 
BJ576
Would You 'lookup' this to get those 'Jobs' from those year sheets?
 

Attachments

  • Calendar WIth Events Rev 11.9.xlsm
    147.4 KB · Views: 6
No I only want to lookup the date and start time but keep the other column formulas please.
 

Attachments

  • Calendar WIth Events Rev 12.2.xlsm
    508.4 KB · Views: 1
BJ576
... and You could not clear few lines of code ... okay
 

Attachments

  • Calendar WIth Events Rev 12.2.xlsb
    216.6 KB · Views: 3
My version brings all datas from those year sheets and show only those as Date and Time - nothing more.
... those do not lookup anything.
As in Your previous version ... there were one lonely 'Bob'
 
Okay I have J106Farm-10 in the 2030 sheet at 7:00 am on 1/4/2030. It should show that in the Jobs sheet please.
 

Attachments

  • Calendar WIth Events Rev 12.2.96.xlsb
    218.8 KB · Views: 2
Okay I attached new version showing the proper date and times that match the JobID. I moved the column to show the lookup.

There are only 2 events so far but there will be many more.
 

Attachments

  • Calendar WIth Events Rev 12.3.xlsb
    218.8 KB · Views: 4
BJ576
There could be a lot of data for each 'JobID' ... hundreds ... thousands data as You wrote.
... my opinion is that skip Your 'the lookup'.

Your could fill ... select somewhere known places, eg those 'JobID's, which You would like to see
and 'soon' ... ( after 'click') You could see all data from those 'JobID's. There could be a lot of lines.
Same way, it would be useful that eg 'history' data won't come any more?
Minor note. With Your year data layout limits that there can start one Job every 30minutes ( no matter number of customers or so).
 
Before start to make more 'testing... testing',
You should able to figure
- what do You really need?
- how would You use it?
- how those datas could fill?
- how many 'jobs' should able to be 'running' in same time? ... one per person ... but many jobs in same time ... or how?
- how many tasks (jobs) there would be totally?
... and so on
==> could/should Your 'year'-data sheets to 'modify' ... 'little'? = could mean, a lot!

Above are Your tasks to solve - figure, especially 'what do You really need?'
 
I foresee about 400 jobs per year. The jobs will not overlap because I will be the one on the jobs unless I can figure out how to clone myself. :p

I see my system as this:

Step one: create the job with tasks and times in the Jobs sheet.
Step two: go to Schedule sheet and click on the calendar day and set the time for the job by using the drop down list in column M for the time slot.

Then make sure the time and date is correct for that job on the Jobs sheet.
 
BJ576
... hmm?
How those 'jobs' goes to year-sheets?
Is 'only' in 'Jobs'-sheet is data - what will You do? ... why not in year-sheets?
... or You would always fill those every time again-and-again? ...hmm?
Do You need 'only' one year jobs in the 'Jobs'-sheet?
... one shouting dog needs help now ;)
 
The jobs go to year sheets by VBA. I suppose only one year at a time per workbook. I could create new book for each year. Maybe it is time to start over?

Do you know of job appointment template that shows appointments on monthly calendar?

Thank you
 
BJ576
You seems to skip some my questions ...
Why need many files? What amazing would happen then year will change?
Could You save Your data, including jobs details other way - not only 'one detail'?

.. and totally other ... way ...
If 'You won't clone Yourself' then
... if You could use 'normal Calendar-program' which You have in Your computer?
There are many useful features ... or what is missing? :(
 
One file would be better. The jobs should be with one ID that are stored so they can be referenced for later if needed. I will have accounting to do also that will come later. For now I want to schedule jobs using excel.
 
... to use was 'verification'-question
with Excel ... You could get what You would need...
Try to figure Your datas ... as one row one data with all details.
date - start stamp - length ... and so on.
after that ... You could show those with Your 'calendar'-sheet too ( after 'minor' changes of code ;) )
 
You must log in or register to reply here.
Back
Top