+ Reply to Thread
Results 1 to 17 of 17

cant get a formula to have one part of my excel sheet reference data for different days.

  1. #1
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    cant get a formula to have one part of my excel sheet reference data for different days.

    I created a schedule and I wanted one part of the sheet show the schedule for the different days within one specific week. I've attached my worksheet to explain more.Copy of Soft Schedule2.xlsm

    does anyone know any formula that can get that done?

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Could you specify your outcome ?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    I want the outcome to be: If its Monday then the schedules for Monday will be in effect and will show in the white box, if its Tuesday then the schedule for Tuesday will be in effect and should show in the same white box. on so on.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    I have added formulas in column E.
    Try to fallow this logic in other columns.
    BTW I have deleted the extra(hidden) columns.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    I am not sure why but I am getting #N/A in the cells. The formula worked perfect until now. Do you have any clue why that would happen.=INDEX($D$4:$K$17,MATCH($D23,$D$4:$D$17,0),MATCH(DAY(TODAY()),DAY($E$3:$K$3),0)).

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    =INDEX($E$4:$I$17,MATCH($D23,$D$4:$D$17,0),MATCH(WEEKDAY(TODAY(),2),WEEKDAY($E$3:$I$3,2),0))
    Confirm Control+Shift+Enter

  7. #7
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Sweet but can it also work with the today's date and not just the day?. I need it to work with the date.

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Yes if you change dates in row 3
    Currently they are February days.

  9. #9
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    I tried changing the dates but it never pulls for today's date. The formula pulls the data from the 1st monday in the month. I tried tweaking the formula but same thing happens. Any Ideas.

  10. #10
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    If you add 8/13/2013 to let say E3 it will pull today's date.
    Post your workbook, state your desired outcome.

  11. #11
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Here is the work bookSupervisor Schedule2.xlsm. I need it to tell info for the specific date. The area highlighted in green is the area that shows the info for the specific date which will then update the sheet name live view.

  12. #12
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    What anser is expect under 14/08?
    Where the data are coming from?

  13. #13
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    In the workbook you will see a sheet name Schedule. In the sheet name schedule you will see some tables under headings(Floor Support, Job Assignment and Auditing) each table is labeled week 1,2,3 or 4. The date on Wednesday in week 2 under floor support will be 8/14/2013. One you put in the supervisors name in the Wednesday column of week 2. The name will appear in sheet name schedule week 1 under Floor Support 8/14/2013 Wednesday.

  14. #14
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    When I entered names in E34 in Table Week2 under "Floor suport schedule" I'm getting the values in Schedule Week 2 cell G5.
    Why this shoudl appear in Week 1?
    Do you need them to Dynamicly adjusted so the first week will be always the current week?

  15. #15
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Actually, I want everything display in one sheet so the Vlookup in the sheet name live view will work properly. I want the green area in Schedule week 1 display all the data for everyday of the month. Can it do that?.

    In the sheet name live view from B4 to B11 has a vlookup formula that pulls data from the green area at a certain time when someone is schedule to be on either floor support, Job Assignment or auditing.

    I mainly want the green area Schedule Week 1 to pull data for everyday in the month. the sheet name schedule week 1 will be the main sheet for the entire month.

  16. #16
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Actually, I want everything display in one sheet so the Vlookup in the sheet name live view will work properly. I want the green area in Schedule week 1 display all the data for everyday of the month. Can it do that?.

    In the sheet name live view from B4 to B11 has a vlookup formula that pulls data from the green area at a certain time when someone is schedule to be on either floor support, Job Assignment or auditing.

    I mainly want the green area Schedule Week 1 to pull data for everyday in the month. the sheet name schedule week 1 will be the main sheet for the entire month.

    I tried tweaking the formula again but still nothing. any other ideas?

  17. #17
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: cant get a formula to have one part of my excel sheet reference data for different day

    Dynamicly adjusting the weeks will be just right to. can you?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] A number of days into weeks and days-How do you convert the decimal part of 0.571429
    By Robert Christie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-25-2020, 09:37 PM
  2. Replies: 7
    Last Post: 02-03-2013, 06:25 PM
  3. using a cell reference as part of a formula
    By luca in forum Excel General
    Replies: 1
    Last Post: 10-07-2009, 06:23 AM
  4. Copy data from one part# on sheet 2 to the same Part# on sheet 1
    By sald in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-13-2009, 10:24 PM
  5. How to find sheet in formula by (part) cell reference. use Indirect ???
    By KrisVeen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-21-2006, 06:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1