Solved

Concatenate fields into calendar date

Posted on 2013-05-29
17
272 Views
Last Modified: 2013-06-23
First, when you go to the 'Project-Calendar New' tab i'm trying to figure out how to do a simple concatenate of what's already showing on the calendar and from the 'Details' tab the "Business Name"+"QTY"+"Estimated Ship Date". This will have a space in between each concatenated item.  The error that comes up is when a person clicks on an item on a specific date i'm running into compile error which I can't seem to figure out.

Thank you again. I'll post my second question which shouldn't effect this question. Here is the attachment.
Q-28141375.xlsm
0
Comment
Question by:Southern_Gentleman
  • 7
  • 5
  • 4
17 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
I can't find any code in the workbook for concatenating the data you mention.

Where should I be looking?
0
 

Author Comment

by:Southern_Gentleman
Comment Utility
I apologize for the confusion, the error that was coming up was fixed because of a compile error but the file that I uploaded is not pulling the correct data on the calendar from the 'Details' tab. I would somehow like to have the information on the calendar be from multiple columns instead of just the first column. Hope this helps. Thanks again.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
You do know that the dates aren't being populated correctly?

It appears to be a one or two of days out here and there.

I borrowed your sheet and came up with the attached.

I've tested it for 2013 and a few months in 2014 and everything seems to be right.
WorksheetCalendar.xlsm
0
 

Author Comment

by:Southern_Gentleman
Comment Utility
Thanks again for the correction on the calendar, i'm really trying to get all my data from my detail page to the calendar and its both incorrect and I would really like to concatenate from multiple columns. I can give you credit for my other question relating to the calendar:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142462.html#a39206774
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Cheers, was getting a bit confused where to post.

So you still need help with filling out the details in the calendar?
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
What's to be done with this concatenation?

Has it to be displayed somewhere?

What if there is more then one business with the same Cal Type ID on the selected date?
0
 

Author Comment

by:Southern_Gentleman
Comment Utility
Yes it has to be displayed on the calendar with the coinciding dates from column B in Details tab. I have column A already displayed on the calendar.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Ah, right instead of just the ID (column A) you want to show the other columns on the calendar.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Southern_Gentleman
Comment Utility
Yes that is correct. I realized that in column A i had to put a unique identifier in order to show the information correctly in the calendar. It was evidently just showing the first or second row and not looking down the entire spreadsheet. So you are correct. I want to show the other columns in the calendar. Headings to be selected are "Business Name"+"QTY"+"Estimated Ship Date". Thanks

I uploaded an updated file which I'm using.
CalendarConcatenate.xlsm
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
I've believe the attached file will create the calendar with the fields concatenated as requested.
CalendarConcatenate.xlsm
0
 

Author Comment

by:Southern_Gentleman
Comment Utility
Thanks buttersk. It looks good, I think I can figure out a way to have the ampersand and index number to be invisible but I noticed that the popup form does not work anymore.
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
The pop up quit working because we changed what is displayed in the calendar.

If you look at the code for Sheet4 (Project-Calendar New),  there is an event triggered call "Worksheet_SelectionChange".

Whenever you select a different item on the calendar this function / event is fired.   One of the first things this subroutine does is to take the contents of the cell and checks to see if it can find a match for the contents of the calendar cell, in the Detail worksheet.

Since the calendar now contains something like "AMG&4 1300 04/17/2013"... there is no match for that string on the detail page.

I've changed your search routine in the Project Calendar new sheet.

It takes the string  value from the selected calendar cell ... "AMG&4 1300 04/17/2013" for example and splits it at the blanks... and assign to an array called subWord so that you end up with this:

subWord(1) = "AMG&4"
subWord(2) = "1300"
subWord(3) = "04/17/2013"

With those values the search routine will start a the top of the Detail sheet and examine each row.

If
Column1 = subword(0) and
Column6 =  subword(1) and
Column12 = subword(2) then

We will consider that a "Found" row on the pop up.

Currently If and only if you find a matching row within the Detail sheet will the pop up be displayed with the data from the matching detail row.
CalendarConcatenate--2-.xlsm
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
After displaying the comboBox... noticed that there were some error's that needed to be cleaned up if you pick a blank cell in the calendar or if date doesn't exist on the detail sheet.

The attached version should correct those errors as well.
CalendarConcatenate--2-.xlsm
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
Comment Utility
Additional changes to sync Userform / Calendar regardless of which is changed... userform or calendar.

This was complicated because of the comboBox in the userform, Had to add enough information to uniquely identify a row in the detail sheet.

This was accomplished by making the comboBox a multiColumn comboBox, and adding the row number associated to the detail sheet as part of that data.  If you don't want to see the row number in the comboBox, then change the first column size to zero.
CalendarConcatenate--2-.xlsm
0
 

Author Closing Comment

by:Southern_Gentleman
Comment Utility
Pretty darn good stuff buttersk. I added some changes but all in all an awesome answer. Much respect.
0
 

Author Comment

by:Southern_Gentleman
Comment Utility
Made in error
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now