Solved

Concatenate fields into calendar date

Posted on 2013-05-29
17
275 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
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 33

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
 

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 33

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 33

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 33

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search for a value in Column? 5 21
VBA Array, write each column's start position into an array 17 36
Boolean help 6 28
Export Query data to excel file 14 37
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

21 Experts available now in Live!

Get 1:1 Help Now