Selecting and using an indeterminate range

[jomili]

I'm working on an automated project to be used throughout the state to create projections for our fiscal year. Part of the process involves working out monthly projections of how many employess (FTEs) we'll have. Multiple columns are created to work out the projections, and at the end of the process, far to the right, we have 1 or more and less than 13 columns filled with our FTE projections. They're titled along the lines of ""Final Projection February", etc. (see the attached example).

These columns will be used for other projections. For instance, if want to determine our salary expenses, we'd need to multiply the avg salary by the number of FTEs. So, this specific set of columns would need to be preserved/set aside for future consideration.

My question is how do I call these columns for future operations, since I don't know exactly where in the spreadsheet they are, nor what months may be included? I assume I could name the whole range, and use that named range in my calculations, but need help in determining that range.

Thanks for your interest and any help you can provide.

[StephenR]

I would use the Find method to search for the column headings, and then since it returns a Range object you can exploit its Column property.

[MarvinP]

Hi,
Your data looks like a big Pivot Table to me, half baked. A lot of these problems look like Pivot Tables to me.
If your data had a single column where you could have one of the words, PayEnd, BaseProjected, MethodologyAdjustment, RBARecommended or FinalProjection, it would look more like a tabel. Also a single column with the DATE would allow a Pivot Table where grouping, sorting and filtering to help with projections would help.

Polya said when a problem is too hard, make a easier one that is similar.

Look to Pivot Tables and a simpler data table is my suggestion. The reason for this is it will expand depending on how many months/dates you have included in the data.
http://www.contextures.com/excelpivottableselect.html for some ideas.

[jomili]

StephenR,
Thanks for the "Find" suggestion. I was thinking along the same lines, so will see what I can make happen with that.

MarvinP,
Though it may look like a big pivot table, it's far from it. The process begins with creating a pivot table of historical information (staffing levels for the months in the previous fiscal year). From that we calculate the average monthly change by position. Next we determine how many columns need to be made to project for the rest of the fiscal year. We first run through a straight methodology to create projections for the remaining months ("Base Projected"), adding the average average monthly change to the remaining months. Next we determine how far that projection is from their allowed levels ("ESS Allocated FTEs"), then use a standard methodology to adjust our totals up or down ("Methodology Adjustment"), noting where that adjustment exceeds the allowable. Then our users get to provide their own input, based on their knowledge of local circumstances ("RBA Recommended Adjustments"), and an explanation of why they're changing the projections ("Justification for Change"), and then the final projection, plus a column noting where that final projection is over the allowable limit.

All of that is merely the first step in creating our projections. So my final projections are going to be used over and over again.

[MarvinP]

Hi,

Certainly you know your data, how it was accumulated and how it was to be used much better than my 10 minute reading of the first post.

My statement is that Excel deals with Tables and Pivot Tables best. From your above description with projected numbers, I see trend lines that are easy to build with data in the structure needed to draw graphs. After dealing with great quantities of data in my past projects, I've found Pivot Table Filters, Sorts and Groupings have allowed wonderful analysis of the data. My question was to see if you might get your data closer to a Table which would allow Excel to deal with it easier.

That's my two cents. Also let me say that many times Access or Outlook are better tools for analysing data depending on the data and reports or needs from the user. I'm still of the ilk that if Excel is the tool, then adherence to rules in Tables allows it to do more with it.

[jomili]

MarvinP,
I think I understand what you're driving at, but don't see how it really helps in this situation. Most of my fields are calculated fields, and data is pulled from various workbooks to calculate those fields. I don't think a single pivot table would be able to bring in all of that information and do the calculations without a lot of work in the construction, which is already being done in the linear fashion of the spreadsheet.

Since my desired data, moving forward, is the "Final Projection" columns only, whether I copy them directly from the spreadsheet or construct a pivot table I still need to be able to determine which columns are my "Final Projection" columns programmatically. They're going to start with the first column after the "justification for change" , and go until the second-to-last column. I'm trying to get them through using "Find", but am having trouble finding them as a group so I can copy/paste them as a group. I can do it singly, but am at a loss for the syntex to paste the columns side-by-side.

[MarvinP]

Hi,

There are advancements in Pivot Tables, given out for free, that are discribed at: http://www.powerpivot.com/ . It just seems to me that Excel is moving in this direction.
This might address the "from various workbooks" concern.

[romperstomper]

Marvin,
I am intrigued by the suggestion that Outlook might ever be a better tool for analysing data than Excel - can you give an example?

[MarvinP]

Hey Romper,

We frequently see spreadsheets where users want to keep a schedules for employee vacations. They have employee names down the rows and dates across the column heads. The question is normally something like how to I tell if two empolyees want to take vacation at the same time. Outlook's Calendar is my tool of choice for this problem. Outlook Calendar has focused on group scheduleing to the point it's great in the latest release.

See http://blogs.office.com/b/microsoft-...px?PageIndex=2 or http://kb.iu.edu/data/azql.html.

After looking at 2010 group calendar view, I realized there is a tool to show this calendar vacttion overlap easier than in Excel.

You bait me by saying "a better tool for analysing data" in your post. If analysing data is to see when two schedules overlap, then outlook may be a better tool. Also if the data is stored in a bunch of different files then Power Pivots add-on may be a better tool. If you need to allow group entry of the data then Access may be a better tool. The point was that Excel is great if the data is arranged in Tables/List that allows analysing it easily. If it isn't aranged like Excel likes it may be easier using another tool to analyse it.

Some of my background. I was Manager of Tech Support for Egghead software corporate before The Internet. Part of our job was to find and/or support software that gave customers answers. Excel has survived because it is versitile and a standard, but there are ever-changing needs that may outgrow it's abilities.

OK - I'll get off my soapbox and suggest that Excel is a GREAT tool for analysing data but not always the best given unusual requirements.

My suggestion in this specific post, after looking at the data structure was to get it closer to a table that Excel could use more wizely, and you know (if you've read some of my posts) that Microsoft should hire me to sell Pivot Tables (my answer of choice).

[jomili]

Marvin,

Thanks for the info on using Outlook, and for your valuable input on pivot tables. I use them whenever I can. If this were a project to analyze data I would agree that a pivot would be the way to go. However, this project is to create projections, not analyze existing data. The various workbooks I use are not under my control; I pull in information via various Lookups and combine it with only the final projected columns to create various projections. So, my problem is still, how do I call these columns for future operations?

[jomili]

Okay, my problem is solved. Assuming I have two columns on Sheet1, one titled "First", then one titled "Last", my problem is that I want to select all the columns inbetween and paste them into Sheet4. Here's the solution to my problem, supplied by SHG4421:

Please Login or Register  to view this content.

Thanks for all the help.

[romperstomper]

Or shg as he's known here.

Thanks for letting us know about the cross-post...