I need another covulated forumla created again...
Here it is....every month a schedule comes out and I have to manually type in who is working from one scheudle to another so I would like a forumla to be created so I can paste the schedule in on one of the workbooks and have it look and if Sinine, Wayne, etc... is working it would put in AM or PM....can this be done???
Here is the schedule that gets created:
http://www.joshuadumas.com
Here is where I have to type in who is working:
http://www.joshuadumas.com
Hopefully someone can help, me because this is a pain in the (you know what)
duemes
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
sure here you go...
this schedule is the one that we first do at the begiing of the month to see whos is working:
http://www.joshuadumas.com
this is the schedule that we enter in AM or PM that is compared to the schedule above:
http://www.joshuadumas.com
Hopefully you can make something happen
You're going to need to make some changes.
First you'll have to reverse the logic, so you fill in the AM/PM on the schedule-goal file, and have the schedule file reading from that.
Next you'll have to put the names also at the bottom of the list, i.e. row 33 of the goal list file.
Now you can put hlookup formulas in each of the cells in the schedule that needs a name. So for example:
Cell A6 (which is Monday 1st AM) the formula reads:
=HLOOKUP("AM",Sheet1!B2:G3
That's the basic formula. Now if you replace "Sheet1!" in the above formula with the link to your own sheet, and make sure the number 30 is the correct number of cells to count down to return the name in row 33, that will give your correct starting formula. Notice I have not put $ signs in there, this is so that if you copy this formula into a blank sheet, and drag it down 31 cells, you will automatically have the correct cell references for each day of the month. All that remains is for you to change the counting number which will be "31" in all of them. This number has to go down by 1 unit each time. You'll have to do this manually. Now you have 31 formulas that you can drop into the 31 days of your schedule which will automatically pull the names you entered in the goal list file. However you only have the AM cells. To get the PM cells, take your 31 finished formulas, find & replace the AM with PM, and you have your other 31 unique formulas.
Finally, you will want to make this more flexible so that you don't have to redo this every month. So instead of directly pulling from the file "Goal Sheet March 2004" you need to (Find &) replace that part of the formula as follows:
Replace '[Goal Sheet March 2004.xls]Schedule'!
With Indirect(concatenate("'[Go
This assumes that the value in Cell A1 is the exact textual equivalent of the file name you are using. In this way every time you change month, you just change the value in cell A1 and save the two files under the new month names and run through the goal sheet reallocating the AM and PMs and there you go.
Any questions or problems, email me the files and I'll sort out the fine tuning.
Business Accounts
Answer for Membership
by: footuckPosted on 2004-02-24 at 20:01:36ID: 10447535
To make this faster, would you make the workbook available you made the images from?