Replies

Rory,
Thank you for the prompt response. So you would set up the conditions in a table that would interact with the B3:C11 table and set the markers to the series of the conditions table. I’ll play with what I have. I am assuming the values in table B3:C11 will constantly change and they will be plotted with the different marker that meets a set of conditions.

thanks
Amy

Gary,
1) I do not have any code other than what I could pull together with the Macro recorder (which is generally not useful)
ActiveSheet.Shapes(“Button 1″).Select
Selection.Find(What:=”!”, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets(“PWC EAC”).Select

So I need this from scratch.

2) For the purposes of highlighting the cells with formula arrays that reference another sheet (Data_Entry), I have highlighted the cells in yellow on the Monthly_Report worksheet. The actual workbook that I will be putting this code into does NOT have yellow highlighted cells. I was thinking it might be possible to write the VBA script such that is is first searching and selecting all cells of the selected worksheet that reference cells from another worksheet (the Find criteria would look for Data_Entry! in the Formula of each cell). I just want to copy those specific cells and paste special->values on the Exported copy of the Monthly_Report worksheet so that it doesn’t maintain links to the parent workbook.

The attached is a general mock-up workbook so I am not posting business sensitive information.

Thanks
Amy

Catharine,
My bad. Yes it is scroll bars. I have attached a couple on the new attached workbook. I can’t add labels for Start date and end date, but that is essentially what I am trying to do. I would need to somehow INDEX that dates on the Data_Preparation worksheet (C2:C91).

The duration between the start and end date is not constant. It should slide to show what ever desired duration.

The formulas for the INDEX reference for the start date and end date should be something like:

=Data_Preparation!$C$2 + INT((INDEX(Data_Preparation!$C$2:$C$91,COUNT(Data_Preparation!$C$3:$C$91))-Data_Preparation!$C$2*???/256)

=IF((Data_Preparation!$C$2 + INT((INDEX(Data_Preparation!$C$2:$C$91,COUNT(Data_Preparation!$C$3:$C$91))-Data_Preparation!$C$2)*???/256))>??,(Data_Preparation!$C$2 + INT((INDEX(Data_Preparation!$C$3:$C$91,COUNT(Data_Preparation!$C$2:$C$91))-Data_Preparation!$C$2)*??/256)),??)

I don’t know if the Data_Preparation worksheet which is set up for the Combo Box can have dual use for the scroll bars (hence my question marks in the above equations).

Please forgive the formula confusion. A friend used something like this on his workbook.

Thanks for the help.

Amy

Thanks, Steve. I’ll put the package together on the databases forum to see what the optimal solution should be. I don’t mind keeping them separate, but since the workbook will have to be updated on a weekly basis based upon new data exports, some problems lend themselves to MS Access, but this Excel solution with some added scripting might be ideal for the reporting front end.

Amy

Steve,
You nailed it. This works great. I was hoping it could be done without necessarily created another table, but I can see how that is unavoidable.

The SUMPRODUCT formulas accommodate multiple IF statements I guess. I was thinking of something like using COUNTIF the travel day is equal to or greater than the Start Date and equal to and less than the End Date. Since COUNTIF can’t use multiple parameters the SUMPRODUCT must be used. Is that right?

I created a third chart.that shows the combined totals.

Question: Can this be interfaced with MS Access and scripted for automation? I am working an MS Access solution that merges data exports from multiple travel databases. It does some of the transformation steps that you saw in the provided export, some data enrichment (where countries are grouped for regional reporting and employee type and travel costs are added to the export table) and record deduping. I receive the data exports on a weekly basis, so the deduping becomes necessary. Is it optimal to use the two solutions together–MS Access for the data management and then updated this MS Excel workbook for the charts? Your advice is greatly appreciated.

Thanks
Amy

Steve,
I think this is close to what I was thinking. However, I am confused about the y-axis scaling (they should be whole numbers) and surprised that it is a flat trend over the total travel period–Oct 3 (earliest start) to Dec 31 (latest end date).

I’d like to be able to show how many travelers are actually gone each day of the total travel period. The day after the end date for someone’s travel they shouldn’t be counted as gone.

It seems the pivot table is the way to go, but I need help tweaking it.

Thanks
Amy

Thanks, guys. I have noticed some discrepancies in the data, however, that prevent the exclusive use of the FIND(“, (“,A2) formula to do the parsing. Some records don’t have a Continent value. How can the formula arrays be expanded to accommodate this discrepancy?

I have uploaded a new example.

Thanks
Amy

Thanks, Steve.

I’ll see if the “sticky space” theory is true for the data exports this week. The example that I provided was just a mock-up of the data.

Amy

Steve,
This worked great. Thank you.

Amy

Peter,
That worked brilliantly. I think my pieces are coming together and I will be able to move this solution to the MS Access platform.

You guys are great.

Amy

Peter/Steve,
Thank you. I integrated some changes with what you provided. I am trying to separate the city out of the parenthesis as another transformation step. I got close, but take a look at column F in the attached workbook.

Thanks for you help.

Amy

Paul,
Thank you for the thorough explanation.

Amy

Paul,
Thanks. As I break this down, the SUBSTITUTE seems to be the primary formula that converts the text string to a number value that is recognized by MS Excel and renders it capable to be converted into a date/time value.

Then it looks like the CODE formula converts the Oct (or any three letter month) text string into the number month value. The CODE formula array returns the value 90 by itself irrespective of the month though.

Not sure what the TEXT formula is doing.

Also, what does the >64 function perform and why is are two zeroes preceding and proceeding the Z in the formula.

What is your +ve reference.

Thanks
Amy

Paul,
That worked. I attached a file.

=–SUBSTITUTE(LEFT(A1,2)&” “&RIGHT(A1,LEN(A1)-FIND(” “,A1))&” “&TEXT(–MID(LEFT(A1,FIND(” “,A1)-1-(CODE(RIGHT(LEFT(A1,FIND(” “,A1)-1)))>64)),3,4),”00Z00″),”Z”,”:”)

I don’t know what the hyphens are all about.

Rory,
The “Z” refers to Zulu or Greenwich Mean Time

Steve,
No dice as I attempted your solution. It is TRUE, though:)

You guys are great.

I can see the next step when I get around to it, will be working this into an MS Access solution. Would that even be possible?

Amy

I added MIN(TrendDataFFP) to the TREND formula in Report_1 and Report_2 and it increased the y-value of the trend line data points such that the trendline would begin at or near the top of the first data column. That didn’t work for Report_3. Is it possible to have a formula array that will add to the y-value such that the trend line always begins at the y-value of the first data column?