Check inputdate, then retrieve data to fill in chart from dated co

ok hope I can explain this.. 
I have a sheet with  dated columns and the first row is employee names
each dated column is filled with letters that correspond to a duty a-g.  so 
for the next 3 months I can look across the top for a date then down the 
column to see where each person is supposed to be.   Now I also have a form I 
print up daily to post for people to see where they are supposed to be.. I 
fill it manually at this point
I wonder if there is any formulat to help automate this..  I am thinking two 
hopeful ideas..   a drop down list on the form, pulling the list of names... 
but it seems I cant do that unless they employee list is on the same page?? 
It would be really helpful if after pulling a name of that list.. it took 
that name off the possibilities.. so I dont accidently have joe blow working 
two places.   But what would be most helpfull is if I could input the date on 
the form page and it takes that date, checks it from the first sheet, and 
based on the letters  puts in the corresponding name.  the form is sectioned 
by duty.. so if all the employees that are supposed to be doing 'A' get 
dropped into the 6 corresponding spots for 'A' on the form.. wow in my head 
its so clear  lol.. any help at all would be great.  Sorry for the run-on 
paragraph. 
0
Utf
2/9/2010 2:49:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
847 Views

Similar Articles

[PageSpeed] 40

Perhaps this will help
SHEET 1
DATE    | 12/1 | 12/2  | 12/3
tom a   |  A     |    C    |    E
fred b   |  C     |    A    |    F
mary c  |  A     |    E    |    G
bill d     |  B     |    C    |    E

Would like the names retrieved and 
dropped into the form  {below} based on 
assigned letter and by date I input

DUTIES  DATE _12/1__

A      |   B     |    D     |    F    |

A      |   B     |     E    |    F    |

C      |   B     |     G    |    D    |

Hope this makes more sense..
0
Utf
2/9/2010 3:51:01 AM
Not clear how you have arranged your data in Sheet2..Try the below test

In Sheet2 cell F1 enter the query date 
In Sheet2 cell F2 enter the 'assigned letter'

Try the below formula in Sheet2....
=INDEX(Sheet1!A:A,MATCH(F2,OFFSET(Sheet1!A:A,0,
MATCH(F1,Sheet1!1:1)-1),0))

-- 
Jacob


"Kozmik" wrote:

> Perhaps this will help
> SHEET 1
> DATE    | 12/1 | 12/2  | 12/3
> tom a   |  A     |    C    |    E
> fred b   |  C     |    A    |    F
> mary c  |  A     |    E    |    G
> bill d     |  B     |    C    |    E
> 
> Would like the names retrieved and 
> dropped into the form  {below} based on 
> assigned letter and by date I input
> 
> DUTIES  DATE _12/1__
> 
> A      |   B     |    D     |    F    |
> 
> A      |   B     |     E    |    F    |
> 
> C      |   B     |     G    |    D    |
> 
> Hope this makes more sense..
0
Utf
2/9/2010 5:15:01 AM
Reply:

Similar Artilces:

Is is me? Or is Excel 2007 Charting Lame?
Maybe I'm missing something. If so, let me know. The process of creating a chart is unintuitive and cumbersome. A big step backwards from 2003. The manual says to just select your data and insert the chart. What I find is that 95% of the time, I have to re-do the data selection. If I highlight two columns and insert the graph, Excel thinks both columns are two different series instead of x and y axis. I create two columns, label one of them "X", one of them "Y". Put some numbers below that. Now insert an XY chart. One would think that it could figure out "...

100% Stacked Bar chart, two bars, want percent as well as real value
I am attempting to chart the following: Series 1 - Values over the prior 30 days Series 2 - Values over the prior 12 months I would like to show both data series as 100% bar charts, so users of the information can compare percentage of the last 30 days with percentage of the same piece of data over the last 12 months. So, for example the two data series might look like this: Series 1 20 25 45 65 Series 2 211 324 403 822 I want to display both the actual value, and the percentage of the total, for each series, in the labels. For some reason, showing percentage is not an option. Why is th...

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Gathering and adding data from different rows
Hi all, I run a report at work every day that tells me how many shares of a company have been sold and from what year these shares were granted to the participant. The year and the amount of shares are on different lines. For instance, the year would be on D5 and the share amount would be on D10. This is a daily function and the data changes every day. I am required to manually scan these reports and add up the shares for each year separately. I have to separate the amounts for 2003, 1997 and 1996. I want to create a spreadsheeet that I can dump this data into and have the spreadsheet find...

Is there an auto fill ability on the calendar
We use the calendar for scheduling reaccuring patients, having auto fill ability would save us tons of time by not having to retype a clients entire name over and over. Thanks No, best you'll get is auto resolution - type part of the name and it picks the name from the contacts. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEW...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto another sheet. Any simple macros? Thanks. Amy How about Sub Test() Source_Sheet = "Sheet1" Target_Sheet = "Sheet2" n = 5000 ' your last line of data on Sheet1 Target_Row = 1 Sheets(Target_Sheet).Select For nCount = 1 To n Step 60 Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy Worksheets(Target_Sheet).Cells(Target_Row, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ F...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

Category filled in on downloads
I noticed this a week or two ago and like it. Can someone tell me how it chooses ( guesses) the categories as it's a bit scary that "they " ( the retailers, Microsoft - and now you ! ) know that I went to Best Buy this weekend and bought movies i.e. the category was filled in as hobby-leisure and when I clicked the drop-down movies was an option for me. Are the categories ( a lot more now than a few weeks ago) a feature of the online download or did they "update" the Money database on my PC to include these new categories ? One more thing ... reporting -wise wil...

Numbers Chart
I need a 10x10 chart of numbers 1-100 with the numbers vertical. I'm sure there is a quickie way to do it. thanks, CB -- C and A Bredt Top left corner of chart: =ROW(A1)+10*(COLUMN(A1)-1) Copy to a 10x10 area. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C and A Bredt" wrote: > I need a 10x10 chart of numbers 1-100 with the numbers vertical. > I'm sure there is a quickie way to do it. > thanks, CB > -- > C and A Bredt > Highlight the cells, format > Cells > Alignment and make it 90 degrees. "Luke...

Some Accounts Hanging When Checking
Microsoft XP SP 2.0 Outlook 2003 (11.6359.6360) Before I had 6 email accounts I checked with Outlook 2003. I recently "imported 5 other ones from OLE 6.0 into Outlook 2003. I currently have 11 email accounts that I check with Outlook 2003. Since the import, Outlook checks my email for awhile perfectly and all accounts receive just fine, but then after a few hours...2-3 random ones "freeze" or time-out from receiving. I have even removed the troubled accounts and re-created them, but the problem still exists through random accounts. Any ideas? -Dennis > Are you ru...

Text Boxes added to Chart Series
How do I keep text boxes that I've added to a data series with the column they belong on? If I change the size of the chart in anyway, I have to move the text boxes back to their respective spots on the chart. Text boxes are not added to a series, but to the chart. This prevents their sticking to any particular points. Could you add them as data labels? If you use a built-in position (i.e., don't drag them around) they will stick with their associated points - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://P...

money 2006 freezes when starting up and checking for software upda
I had a problem before in which i was getting an error message every time i tried ti start msmoney 2006 and it would shut itself down. Now, after reinstalling SP2, every time i try to open msmoney is freezes with the windows where is says: "Please wait while Microsoft Money 2006 check for software updates. This might take several minutes." I left it sitting there for a couple hours and nothing happens. I can't even restart the computer, since after a while it freezes the whole thing. Thanks in advance, Juan ...

Can't See Word Check Boxes in Outlook
I have a Word Document that I wish to send via the File->Send To->Mail Recipient once it is filled in. The document has questions that require the User to select a check box for the different selections (Excellent, Good, Poor, Very Poor). When I send the document, the check boxes show up in Outlook, however when the email arrives the check boxes are no longer there. Is there a way to have the check boxes show up in the email, or, are they somehow being stripped? ...

Bluetooth: After pair without using a code, CreateFile asks for co
I am trying to use a Bluetooth device in Vista Home Premium. The device does not have a pairing code or any way to enter one. I use "Add Wireless device" in the Bluetooth Devices control panel. My device is found and I choose "pair without using a code". The device pairs OK and its properties show under Services, Serial port ( SPP) 'AT Serial' COM4 But when I call CreateFile to open COM4, Vista shows a "click here" box informing me that a device is trying to connect. When I click the box, I get a dialog asking for the pairing code. How ca...

date formulas #5
Please help! I have given an example of part of the spreadsheet i am creating to outline my queery: Report in / Reply due / 1 Sept 2005 / 1 Sept 05 4 Sept 2005 / 18 Sept 05 / 14 Jan 00 / etc, / etc, I have entered the following formula in the 'reply due' column: B1 = A1 + 14 which calculates the date two weeks after the date in the 'report in' column. When i drag the formula down so it applies the entire 'reply due' column the cells aut...

Date formatting in Excel #2
How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I type 10-4, Excel returns Oct 05. Hi When no year is typed, excel would assume it is the current year. You would ahve to type the full date (including 2004) for it to display as Oct 2004. Use Format - cells - date and then select the particular format you want from the options there. >-----Original Message----- >How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I >type 10-4, Excel returns Oct 05. >. > ...

Can't see text box fill colour
I have recently reloaded Office Small Business following a hard drive crash. I made some changes to display in Control Panel Accessibility this time that weren't there before. I've changed back to default but the problem continues. Previously I could fill or border text boxes and see the results in both normal view and print preview. Now the fill/border colour is only visible in print preview. Borders appear as black and fills as white in normal view. Text colours are unaffected. This is making life difficult as white text in a black fill just disappears until I go to pr...

Retrieving a File
I need to set up a procedure to automatically retrieve a file(s) and send to a printer at a specified time (6:45) everyday. I'm not that familiar with VBA and have no idea where to start. Can anyone help me or point me in the right direction to start this. Thanks! Tammy for starters try this Sub PrintFile() Workbooks.Open FileName:="C:\Temp\Test FILE.xls" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.OnTime TimeValue("06:45:00"), "PrintFile", TimeValue("06:45:10"), True End Sub --- Message posted from http://www...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

Date Last Sold
Date last sold should be tracked in the item quantities file and the item master. This is a common query request (show me all items we haven't sold in the past two years). Having to go to the sales line item history makes for a slow, complicated solution. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click...

Auto-fill
Add auto-fill as a standard feature when selecting vendors, account numbers/names, checkbook names, etc. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=a4a433af-e0c1-47f4-...

Side bar chart chinging size
I'm trying to update a side bar chart but every time I open the chart the size of the chart changes in length. Can anyone help me to correct this problem. I have made sure that I sized the chart to 100%, clicked off the auto scale, but nothing has helped. ...