[longuriel]

Hello All,

First Off, although If need be can be done in excel 07 (which Im liking more and more) it would be best if it could be used in both 03 and 07 (company hasnt updated all computers to 07)

So Im trying to design a tracking system for my work and I have made good headway with Excel however I want to go a bit more advanced and Im having a problem coming up with how to script it.
<<<EDIT For better explanation >>>
OK Situation is:



I need to pull information from from numerous docs named something like 12345f.xls (numbering being employee's HO login)

The document pulling information will be Manageroverview.xls, Regionoverview.xls, Distractoverview.xls exc.



The first variable will be the Market number, example 390

2nd Variable will be the Store number, example 3901

3rd variable The Month, Example October

4th variable cell c3, where the mng will enter the employee's number, Ex 12345f.



How we use this information, in say, cell d5, it will pull from p:\somelongreduntantaddress\variable1\variable2\tracker\variable3\variable4 .doc, then specific cell from specific page(its important the extension for excel docs be added after variable 4)



End result should produce the equvalant to =SUM('p:\somelongreduntantaddress\390\3901\tracker\october\[12345.xls]Monthly Totals'!$F$13)



(Monthly totals is the name of the page in the excel doc, f 13 is the specific cell in that page)
<<<END EDIT>>>





Here's the situation:
I want to pull info from empolyeedocA, Cell A1 into ManagerdocA cell A2, (easy enough)
However, In managerdocA, there is then EmployeedocB, EmployeedocC, exc.
Again, could all do this by hand and save time. Here's the catch.

I want ManagerdocA to look for the file in the location (example) P:/generalroughtthere/Market/Store/tracker/month/employee.doc

With Market, Store, and Employee being values input into the managerdocA.
So For example
a1=Market, A2=Store, A3=Tracker, b1=Employee (tracker is not variable, although if easier to build the location could easly be entered in as say f30=tracker or some such)

The reason I want to use variables like this is this is a document that will be duplicated at other locations. The employee's will save there doc in the store folder under there employee id ex: 12345f.doc
The manager in the managerdoc will imput there market, store, and then in seperate locations the employee numbers.
Latter, I plan to make a Marketdoc, and potentially one even higher and so need to learn how to use variables in this manner.


Thanks.

On a side note, I cant remember how to Hide Row if specific cell in row is left empty. If anyone can help with that I could use that as well.

[The Villan]

Well here is a simple example to start with, to see if this is the sort of thing you are trying to do.

So my example is this

='C:\Users\User\Workbook consolidation\Store1\[Employee1.xls]Sheet1'!$A$1+'C:\Users\User\Workbook consolidation\Store1\[Employee2.xls]Sheet1'!$A$1

Where there is a file called Summary in the Workbook consolidation folder and this is pulling the data from cell A1 in Sheet1 from workbooks Employee1 and Employee2 in the Store1 folder, which is under the Workbook consolidation folder.

How you store files in specific folders is from my memory critical as calculation can be lengthy. In fact if you load the Summary Folder, you are better setting the Calculation to manual, so that you can load it quickly and do the calculation update when you want to.

Is this anywhere in the radar beam :-)

[longuriel]

OK, so Im not trying to add cell from one book to another, just display it withought having to open each individual book. And this cell needs to be changeable withought the users knowledge of excel forumulas. Thus why Im trying to find a formula that can input differant peices for the destination based on input in other locations of the document.

So the example I would say best fits it is:

Cell a1 ='C:\user\User\Workbookconsolidation\Variable1\Variable2\variable3\[variable4.xls]'!$A$1'
Cell a2 Variable1
Cell a2 Variable 2
Cell a3 Variable 3
Cell A4 Variable 4


Then the next row would be
Cell b1 ='C:\user\User\Workbookconsolidation\Variable1\Variable2\variable3\[variable5.xls]'!$A$1'
Cell b4 Variable 5

Then the next row would be
Cell b1 ='C:\user\User\Workbookconsolidation\Variable1\Variable2\variable3\[variable6.xls]'!$A$1'
Cell b4 Variable 6


This is simplified and the overall spreadsheet will encompas alot more however if someone can give me a way to do whats mentioned above I can go from there


Does that make sense? Can you picture how this would Result and why I am unable to edit and build one for each specific store and input each coding for every employee?

[The Villan]

I can see where you are coming from.
This needs to be set up as a macro driven project. I think.
I know the macro geniuses would be able to help here.

[longuriel]

Posted via Mobile Device

[longuriel]

i had a feeling it was going to go the macro rought. any macro guru's got any recomendations?

Posted via Mobile Device

[Zack Barresse]

Hi there,

You can do this all via code, sure. I'm not too sure on the entire process yet though, but I do have a rough understanding. So let's get some Q&A out of the way here first.

First of all, the larger scope needs to be identified. You said you're going to have this 'master' document available to multiple users. Are you just planning on copying the file to their local machine? Having one centrally accessed workbook everyone opens? Have you ever thought about keeping this as an add-in, so you only have one place to edit your source code and can make everything the way you want? Now, the downside to that last option is using multiple versions, more specifically 2003 and 2007 (as opposed to 2002, aka XP, and 2003, would be much simpler as they employ the same user interface, which means the same coding principles, and 2007 is much different in that regard). Of course you could make two separate add-ins and have a 2003 interface (old menus) and a 2007 version with the new UI (aka Ribbin).

Once you get it figured out the scope of the file you are going to use we can move on. Some of the information we're going to want to know is what data exactly are you going to want to pull back for the users? Is it going to be the same cell addresses everytime? Will there be any 'thinking' involved (i.e. needing to use logic to find data in the file)? Are you wanting to display the workbook to the user? What do you want to do if the file is not found? What do you want to do if the file is in use by another user? If you want multiple pieces of data from multiple workbooks pulled (I think is what I understood from your second post above) how will you define that? Is there any sensitive information that certain users will not be allowed to see, or is it all fair game to everyone who accesses this 'master' file?

I'm sure we'll have more questions as we work things out and progress, but this should be a good starting point.

[longuriel]

Morning all,

So first off, security is not a risk, anyone who can find where the files are saved are welcome to look at them.

2nd, the source cells will be the same location every time.

The document will be per individual, so if 30 people are using it, there will be 30 docs that are being pulled from into the master (thus the need for the variables).

When I get home tonight Ill take a couple screen shots of the docs already designed and post them to get a more visual example going that might make more sense.

The cells both being pulling from, and the destination will be visible.

The purpose of the tracker is for a sales employee to enter a tally in each category for what they have sold for the day or in the case of one field a monatary value. The master tracker then pulls from all of that managers employee's giving him both a overal picture as well as a breakdown of employee by Month and day.

Its very simple value's that are being pulled and displayed and I could have long ago codded it the normal way, however I want to implement it in a way that it will minimize employee error/accidental deletion of parts of it, as well as manager ease of use. Hopefully this will be used company wide to assist us in tracking sales and comparing to the current tracking process which looses about 20% of the sales made in a month (and as a sales employee you can imagin how frustrating it is knowing your commision check is %20 short...)

[longuriel]

So for the Variables would be Store Number, Month, and employee number (10554f)
This would lead to the source p://3901/october/tracker/10554f.xls

[longuriel]

Hey Guys, so apparently here no one is able to give me a solution to my problem, do you guys know a good place I could go w/o having to spend money I cant afford to get some help?

[The Villan]

I think you need to be patient. Zack is probably working on your issues, so at least wait until he comes back.

[Zack Barresse]

Sorry, have been busy with internship and school. I'm catching up on reading posts now.

[Zack Barresse]

Can you upload the workbooks? If we can work with your files it will save loads of time and headaches of adaptation(s). If the data is sensitive, please replace with dummy data, but the structure is what is important. The closer we have to the original the better off we all are. We don't want to make a solution and then have you say, "oh, so how do I adapt that to my actual file." That happens quite a bit, we want your originals, or as close to it as you possibly can.

In your Manager Overview workbook, where you start listing your data, columns B and C have merged cells containing the employee number, that would need to be unmerged. Merged cells are the bane of VBA, and I wish it was never invented. Will it be a different set of employee numbers every time? Should the program (code) be smart enough to look at what has been filled out and populate accordingly?

As for the employee file, is it always going to be the same sheet? Will the sheet name always be "Monthly Totals"? What about looking for other months? Where is that data stored? Do you have to change something on that sheet? Are the values populated by formulas? If so, what are the formulas and how is the data populated (where the original data is housed)?

And just so I understand correctly, there are multiple user files (one for each employee), but only ONE manager file to pull data into, correct?? And the code should be kept inside of this workbook? And we don't need an add-in (which might be better if we were going to use code on multiple workbooks - manager workbooks that is)?

[longuriel]

Thanks for your answers guys, Life's been hecktic, Ill get the answer to all these questions and upload the excel files when I get off work toda.

[longuriel]

Attached are the actual excel docs although they will both be renamed depending on the location there being used in.

Can you upload the workbooks? If we can work with your files it will save loads of time and headaches of adaptation(s). If the data is sensitive, please replace with dummy data, but the structure is what is important. The closer we have to the original the better off we all are. We don't want to make a solution and then have you say, "oh, so how do I adapt that to my actual file." That happens quite a bit, we want your originals, or as close to it as you possibly can.

In your Manager Overview workbook, where you start listing your data, columns B and C have merged cells containing the employee number, that would need to be unmerged. Can be unmerged, no big deal thereMerged cells are the bane of VBA, and I wish it was never invented. Will it be a different set of employee numbers every time? Employee numbers will change frequently depending on location and lay offs, hires, exc, but on a month to month basis in one location they will frequently similerShould the program (code) be smart enough to look at what has been filled out and populate accordingly?The code will need to be smart enough to use the value of cell in B thats currently merged to use that to look for the file in a lengthy source location

As for the employee file, is it always going to be the same sheet? Will the sheet name always be "Monthly Totals"? The Sheet name will always be the sameWhat about looking for other months?The manager overview will always be looking at one month. The Novemeber Manager overview will be looking at novemeber, exc. The file name of novemeber overview will change as the month changes. For ex Nov Mng Overview, Oct Mng Overview, exc Where is that data stored? I am unable to give the exact address but it is a: saved on a network drive that will be labeled P: and the end of the location will be store/tracker/month with Store and month being variables that will change depending on the store using it, and the month we're in Do you have to change something on that sheet? On the manager overview sheet the only information changing will be the variables of Store, Month, Employee Numbers, Goals for both part time and full time, as well as the field marked X for whether its a part time employee or not Are the values populated by formulas? If so, what are the formulas and how is the data populated (where the original data is housed)? Most fields are not formulas and simple =sum(other workbook) and thats the chunk Im needing to figure out how to do.

And just so I understand correctly, there are multiple user files (one for each employee), but only ONE manager file to pull data into, correct?? Mostly Correct. The file currently named Master Tracker will be renamed by each employee as there employee number. The mng overview will be accessed by several parties but there will be only one copy per store, pulling from on average 5 seperate Master trackersAnd the code should be kept inside of this workbook?Yes And we don't need an add-in (which might be better if we were going to use code on multiple workbooks - manager workbooks that is)?Not sure about add in, but due to corporate security I doubt a addin could be used outside of the workbook.