Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Concatenate fields into calendar date

Posted on 2013-05-29
17
Medium Priority
?
287 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 35

Expert Comment

by:Norie
ID: 39206717
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
ID: 39206729
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 35

Expert Comment

by:Norie
ID: 39206779
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Southern_Gentleman
ID: 39206794
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 35

Expert Comment

by:Norie
ID: 39207157
Cheers, was getting a bit confused where to post.

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

Expert Comment

by:Norie
ID: 39207547
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
ID: 39208388
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 35

Expert Comment

by:Norie
ID: 39208731
Ah, right instead of just the ID (column A) you want to show the other columns on the calendar.
0
 

Author Comment

by:Southern_Gentleman
ID: 39208919
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
ID: 39253740
I've believe the attached file will create the calendar with the fields concatenated as requested.
CalendarConcatenate.xlsm
0
 

Author Comment

by:Southern_Gentleman
ID: 39255160
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
ID: 39257332
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
ID: 39257359
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 2000 total points
ID: 39260817
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
ID: 39264571
Pretty darn good stuff buttersk. I added some changes but all in all an awesome answer. Much respect.
0
 

Author Comment

by:Southern_Gentleman
ID: 39269985
Made in error
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

916 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