Spreadsheet to calendar

[lavudyar]

Hi folks

I did do a search, but wasn't able to easily find a similar issue. If I did , a link to the thread would be awesome!

Anyway, what I have is an Excel sheet that we maintain that shows our delivery schedule. Each workbook is a month, each tab a week. The columns are the dates, and each delivery is on a row assigned to 1 of 4 trucks. What I need to do is pull data from only the cells with content and populate a list on a separate tab. So in the attached sheet, I have a date in B3 then customer data in D5, D6, D15, D16, D17, etc. (as well as columns E and N). Similar data repeats starting column P, AD, AR, and BF.

I can pull the data over to the new List tab, but I don't know how to do so with no gaps/blanks in the rows. I need the list tight so that I can push it to a Google Calendar. Thanks for any help or insight!

Ray

[CAntosh]

This can be done with formulas, but your data setup makes it a little tricky. We need to be able to tell Excel, "If there's data in THIS column, then include that entry as a delivery." Order # seemed like the best column to use, since every apparent delivery had an order #, but your order # column for Tues. - Thurs. also included transfer information (e.g. STRAIT & LAMP XFER1) that didn't look like it should be counted as an order. To clear the column, then, I moved those three transfer information blurbs over one cell so they would clog the C Code columns and clear the Ord # columns for use as our identifier columns. I then used the following formula in B2. It should be array-entered (confirm with Ctrl + Shift + Enter instead of just Enter), then filled down beyond what you think you'll need (I filled it through row 100 in the attachment):

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX('wk1'!$D$5:$D$54,SMALL(IF('wk1'!$E$5:$E$54<>"",ROW('wk1'!$E$5:$E$54)),ROW(1:1))-4),INDEX('wk1'!$R$5:$R$54,SMALL(IF('wk1'!$S$5:$S$54<>"",ROW('wk1'!$S$5:$S$54)),ROW(1:1)-COUNT('wk1'!$E$5:$E$54))-4)),INDEX('wk1'!$AF$5:$AF$54,SMALL(IF('wk1'!$AG$5:$AG$54<>"",ROW('wk1'!$AG$5:$AG$54)),ROW(1:1)-COUNT('wk1'!$E$5:$E$54)-COUNT('wk1'!$S$5:$S$54))-4)),INDEX('wk1'!$AT$5:$AT$54,SMALL(IF('wk1'!$AU$5:$AU$54<>"",ROW('wk1'!$AU$5:$AU$54)),ROW(1:1)-COUNT('wk1'!$E$5:$E$54)-COUNT('wk1'!$S$5:$S$54)-COUNT('wk1'!$AG$5:$AG$54))-4)),INDEX('wk1'!$BH$5:$BH$54,SMALL(IF('wk1'!$BI$5:$BI$54<>"",ROW('wk1'!$BI$5:$BI$54)),ROW(1:1)-COUNT('wk1'!$E$5:$E$54)-COUNT('wk1'!$S$5:$S$54)-COUNT('wk1'!$AG$5:$AG$54)-COUNT('wk1'!$AU$5:$AU$54))-4)),"")

The formula looks first in Monday for entries in the Ord # column, then Tuesday, etc. The range immediately after each INDEX function (e.g. INDEX('wk1'!$D$5:$D$54...) indicates which column is providing the return information. The formula above returns the C Code (column D). To return the Ord #, I changed the ranges immediately after each of the 5 INDEX functions to match the Ord # columns. Thus, INDEX('wk1'!$D$5:$D$54... becomes INDEX('wk1'!$E$5:$E$54..., etc. The new formula goes in C2, array-entered and filled down. For the location, change the five INDEX ranges to match the delivery city columns, so INDEX('wk1'!$D$5:$D$54... of the original formula becomes INDEX('wk1'!$N$5:$N$54... etc.

The date is easier. Use the formula below, array-entered, in A2 and fill down:

=IF($C2="","",INDEX('wk1'!$B$3:$BS$3,1,MIN(IF('wk1'!$D$5:$BI$54=$C2,COLUMN('wk1'!$D$5:$BI$54)))-4))

The formulas are a bit of an eyesore to look at, but once they're all in, the aggregated list on Sheet2 should automatically update as you add to, subtract from, or modify the information on the 'wk1' sheet. Play around a bit with the attachment to see if it'll do the trick:

[lavudyar]

Thanks a ton, that looks like it will fit the bill and it converts to Google easily. My actual sheet has 5 tabs, all formatted the same. When I moved the formula over to the live spreadtsheet, I changed the 'wk1' to match the correct tab name but it's not populating. I did use CTRL+SHIFT+ENTER.
On the test sheet I simply

[lavudyar]

Just realized that having the multiple tabs will make things much more complicated. Maybe easier to have a separate list tab for each week tab?

[CAntosh]

EDIT: I just saw post #4 - looks like we had the same idea!

5 tabs with 5 days per tab would require an enormous formula to solve. It would be structured like the ugly formula in post #2, but it would have 25 IFERROR clauses, so... too big. A simpler approach would be use the formula above to get a nice table of results for each of the weeks, then use a new formula to aggregate the five weekly tables. In the attachment, I've created space for the 5 weekly charts and included the formulas for week 2 (all you need to do is copy wk1's formulas and do a "replace all" for "wk1" with "wk2" -you can do the same for weeks 3 through 5). Then, in Z:AC, array-enter the following formula in Z2:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(A$2:A$100,SMALL(IF(A$2:A$100<>"",ROW(A$2:A$100)),ROW(1:1))-1),INDEX(F$2:F$100,SMALL(IF(F$2:F$100<>"",ROW(F$2:F$100)),ROW(1:1)-COUNT($C$2:$C$100))-1)),INDEX(K$2:K$100,SMALL(IF(K$2:K$100<>"",ROW(K$2:K$100)),ROW(1:1)-COUNT($C$2:$C$100)-COUNT($H$2:$H$100))-1)),INDEX(P$2:P$100,SMALL(IF(P$2:P$100<>"",ROW(P$2:P$100)),ROW(1:1)-COUNT($C$2:$C$100)-COUNT($H$2:$H$100)-COUNT($M$2:$M$100))-1)),INDEX(U$2:U$100,SMALL(IF(U$2:U$100<>"",ROW(U$2:U$100)),ROW(1:1)-COUNT($C$2:$C$100)-COUNT($H$2:$H$100)-COUNT($M$2:$M$100)-COUNT($R$2:$R$100))-1)),"")

Fill right through AC2, then fill down beyond what you'll need and you should be all set. Z:AC should provide your complete list of entries, and everything should automatically update as you change the data on your individual 'wk' sheets.

EDIT: Found typo, see post #12

[lavudyar]

That worked great, thanks so much! Now I just need to figure out how to move the events to Google calendar lol!

Ray

EDIT: rep added

[CAntosh]

Google Calendar isn't something I have much experience with, but hopefully it isn't too tricky to transfer Z:AC in. Good luck, and thanks for the rep!

[lavudyar]

Back from the Solved file, I have one more question/request. Would it be possible to have the aggregate list simply add new events to the bottom of the list instead of chronologically? I think the only way I can get this into Goggle sheets is through Zapier, and I'd need to add to the bottom for it to work. TIA

Ray

[CAntosh]

Given your current structure, I don't think your request is possible with formulas. A formula doesn't recognize "most recently entered". You would need to create a new column for each day in each weekly sheet for "date entered", then we would have to change the aggregation sheet to consider the entry date. Alternatively, VBA could be used to create a worksheet change event that adds a new entry to the calendar list, but I don't know if VBA is something you want to resort to. Thoughts? Maybe it would be easier to make the calendar software adapt to the changing list? Is there an Excel calendar template that would work, or is Google Calendar a requirement?

[lavudyar]

We're pretty set on using Google calendar for live sharing purposes, so I kind of need to use Google sheets. I'm not opposed to VBA, I would need to translate it into Google script which I'm not capable of doing, I'd just need to get help like I do here!

I don't have a good answer, my knowledge base doesn't the required technical aspects.

[CAntosh]

I'm not sure I'm understanding your proposed process. Part of that is my ignorance of Google Calendar, but maybe there are other elements I'm missing. Is the plan to put this workbook into Google Sheets, have users update it there, then have a Google Calendar that live-updates based on that Google Sheets workbook? Or will it remain in Excel, and Zapier is a program that uploads it to Google Sheets?

[CAntosh]

I found a typo in my solution in post #5 while looking at this again. In case you didn't catch it, some "C"s should be "H"s in the formula in F2. F2 should be (array-entered):

=IF($H2="","",INDEX('wk2'!$B$3:$BS$3,1,MIN(IF('wk2'!$D$5:$BI$54=$H2,COLUMN('wk2'!$D$5:$BI$54)))-4))

[lavudyar]

I found a typo in my solution in post #5 while looking at this again. In case you didn't catch it, some "C"s should be "H"s in the formula in F2. F2 should be (array-entered):

=IF($H2="","",INDEX('wk2'!$B$3:$BS$3,1,MIN(IF('wk2'!$D$5:$BI$54=$H2,COLUMN('wk2'!$D$5:$BI$54)))-4))

No worries, I did catch that.

I need to think about getting this to a calendar. We're working in the live Google sheet, and I'm trying to find a solution that will let us push changes from the sheet to the Google calendar automatically.

Zapier will take anything added to the end of the list and add a new event, will update an event that changes so long as it stays on the row, and delete an event that is deleted so long as that row is now blank. This is more complicated than I thought.

[CAntosh]

Are you required to use an outside calendar application, or would it suffice to use Excel/Google Sheets to mimic whatever functionality you're trying to get out of a calendar? In the attachment, I included two hastily made alternatives to a calendar. The first is essentially a basic event ledger created with the following formula in E2 (array-entered), filled right and down:

=IFERROR(INDEX(Sheet2!$AA$2:$AA$200,SMALL(IF(Sheet2!$Z$2:$Z$200=E$1,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1)&"-"&INDEX(Sheet2!$AB$2:$AB$200,SMALL(IF(Sheet2!$Z$2:$Z$200=E$1,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1)&"-"&INDEX(Sheet2!$AC$2:$AC$200,SMALL(IF(Sheet2!$Z$2:$Z$200=E$1,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1),"")

The second calendar sheet looks a bit more complex, but I adapted it from an earlier project I worked on. It maintains the look of a calendar and shows quarterly info. It uses the following formula in B6 (array-entered):

=IFERROR(INDEX(Sheet2!$AA$2:$AA$200,SMALL(IF(Sheet2!$Z$2:$Z$200=B$5,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1)&"-"&INDEX(Sheet2!$AB$2:$AB$200,SMALL(IF(Sheet2!$Z$2:$Z$200=B$5,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1)&"-"&INDEX(Sheet2!$AC$2:$AC$200,SMALL(IF(Sheet2!$Z$2:$Z$200=B$5,ROW(Sheet2!$Z$2:$Z$200)),ROW(1:1))-1),"")

Fill it right and down for the rest of the week. Adjust it for the other weeks, then copy paste each week over for the other months. You would probably need to expand the calendar to incorporate more than 10 entries per day, but that shouldn't be too tricky.

Both of these options are macro free, so they should carry over to Google Sheets pretty well if you prefer that medium. I don't know if either of these are possibilities for you; I'm just offering them as food for thought. There are also other "Event Calendar" templates in Excel that might be adapted to suit your needs more effectively than trying to force your data to suit a Calendar Application.

[lavudyar]

It's more about sharing the info in a timely manner, so having the ability to share the Google calendar seems the best/least onerous option for the people who need to see it. I'll play with the sheets Sunday and see what I can do with the. Thanks so much for your help!

[lavudyar]

Are you required to use an outside calendar application, or would it suffice to use Excel/Google Sheets to mimic whatever functionality you're trying to get out of a calendar? In the attachment, I included two hastily made alternatives to a calendar. The first is essentially a basic event ledger created with the following formula in E2 (array-entered), filled right and down...

The second calendar sheet looks a bit more complex, but I adapted it from an earlier project I worked on...

Both of these options are macro free, so they should carry over to Google Sheets pretty well if you prefer that medium. I don't know if either of these are possibilities for you; I'm just offering them as food for thought. There are also other "Event Calendar" templates in Excel that might be adapted to suit your needs more effectively than trying to force your data to suit a Calendar Application.

Thanks again for the time and effort.

Unfortunately neither option will work for what we think we need. The advantage of using Google sheets and calendar is that IF I can get the Zapier zaps working, I can point it at multiple sheets and it will keep the calendar updated automagically. We would then just need to maintain the sheets and only share the calendar.

I don't KNOW that it will work, but the guys from Zapier seem to think it will if I can get the data into list format (done), any event added is added to the end of the list, any updated event will update in place, and any deleted event leaves a blank row. It is complicated, though as you say VBA/macro might do the job. I'm starting to think I need some actual coding for this.

[CAntosh]

With a coded solution, do you want to automatically add an entry to the list every time an order # is entered? What happens if that entry is then deleted, should the next entry go on the same line of the list, or will that mess with Zapier? If new entries are added to the bottom of the list and deleted entries leave a blank row in the list, then the list will indefinitely grow longer. Is that the plan?

Or... would you prefer to operate with a button that, when pressed, refreshes the list based on the current data, removing any dead rows and adding any new ones?

It sounds like the first solution fits better with (my understanding of) Zapier, even if the second would provide a cleaner workbook. Or do you have a third idea in mind?

[lavudyar]

First solution with the indefinitely longer list is what I'm thinking is needed. We'll just have Zapier point at multiple sheets as we add months. So basically each event permanently reserves a row that can only be used by that event.

Thanks again for your time on this!

[CAntosh]

Take a look at the attachment to see if we're on the right track. It includes the following workbook change event:

Please Login or Register  to view this content.

The idea is that if you add an entry into a column that has "Ord #" in row 4, the procedure will take that value and place it in the next available row on the sheet called "list". It will also pull the appropriate date from row 3 of that sheet. I don't know if your user will be instructed to enter the C Code and the delivery city BEFORE entering the Ord #, so just to be safe, the procedure caries over formula references to the appropriate C Code and City cells into the list, that way the order doesn't matter. I don't know if that's ideal, but it seemed the safest way to get all of the information over.

I deleted the calendars and the other sheet in which we used formulas to compile a list, since they're no longer necessary. You should be able to add wk3, wk4, etc. sheets without issue and still have the code apply.

Try out the attachment to see what you like and dislike about it. I'm not entirely clear on your plan for removing entries. Deleting a row from the list sheet simply deletes it. Any new rows will be added to the bottom, regardless of spaces above. Currently, deleting info from a week sheet has no effect on the date and ord # in the list, though it will clear the CCode and city values that were linked to those cells on the week sheets. This doesn't seem perfect, but I don't want to overcomplicate things if there isn't an issue. Let me know how you want to proceed with deleting data from week sheets.

[lavudyar]

Apologies CA, it's been pretty crazy at work and I'm just now getting back to this project. I should have posted much sooner. Thanks again for all your work on this.

So I downloaded the sheet, and it works fine when I add an event. It does not pick up any existing events, which is fine, I can work around that. When I delete/move an event, it leaves behind the date and order number, and it does not pick up the new date if I copy/paste the event data.

Ray

[CAntosh]

The original code wasn't designed with copy/paste and cut/paste functionality in mind. The version below should (hopefully) be better. I created a UDF called FormText that returns the formula in a cell as text using the following:

Please Login or Register  to view this content.

I believe that Excel versions after excel 2010 have this function built in, called FORMULATEXT(), but I'm working with Excel 2010, so I had to create it. If you can use the natural formula, feel free to do so (though please note that there's one spot in the code below that uses 'FormText' as well). Regardless, using FormText(), I then used the following in A2 of the list sheet:

=IF(LEN($C2)<2,"",OFFSET(INDIRECT(MID(FormText($C2),2,FIND("$",FormText($C2),FIND("$",FormText($C2))+1)-2)&"3",TRUE),0,-3))

And in B2:
=IF(LEN($C2)<2,"",OFFSET(INDIRECT(MID(FormText($C2),2,100),TRUE),0,-1))

And D2:
=IF(LEN($C2)<2,"",OFFSET(INDIRECT(MID(FormText($C2),2,100),TRUE),0,9))

You can then fill all three formulas down as far as you think you'll need, and they should update automatically. I'm not thrilled about using both OFFSET and INDIRECT in each of these formulas, but there aren't many other formulas in your workbook so I'm optimistic that you won't experience any meaningful lag. The goal of using this setup is that it enables the code to focus on getting just the Ord# right, which made it easier to adapt to all of your different conditions. The event procedure used to do so is:

Please Login or Register  to view this content.

It should do the following:
- New Entry on wk sheet --> New line on list
- Change an entry --> the same line on list is updated with the changes
- delete an entry on wk sheet --> that line on list sheet appears cleared (it's actually a space, which acts as a placeholder). If you later enter new data in that same cell on the wk sheet, it should generate a new line.
- copy/paste an entry to another spot --> original entry on list sheet unchanged, new entry added on new line with new date
- cut/paste an entry to another spot --> the original entry on list sheet updates with the new date

I tried to enable the ability to copy/cut/delete multiple entries at once and it seems to be working, but this feature could use a bit more testing. Try toying with the attachment to see if it will do. I don't think it's my most efficient work, but it seems to be working for me. Give it a go:

[lavudyar]

This appears to work exactly as needed. We don't usually need to move multiple items at once, so that's not an issue. We also cannot cut/paste as that really screws up our conditional formatting, so we copy/paste special-values and that works, too.

Let me see how to get this converted to Google-speak if I can.

Thanks again for all your work on this!

Ray

[CAntosh]

Glad to help, fingers crossed on the Google-translation...