+ Reply to Thread
Results 1 to 8 of 8

Index and match ...

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Essen Germany
    MS-Off Ver
    Excel mac
    Posts
    13

    Index and match ...

    Hi Guys
    Having tried to avoid Excel for the last few years, I'm now having to build a somewhat (to me at least) complicated spreadsheet
    to calculate my teaching hours.
    I need to fetch the date when I've actually worked - the idea is to select the class form a pick list on the left hand side, input the amount of hours under the date.
    On my Work-Hrs sheet I need to show first, the Date - Class - Hours... Simple really. I can find the row contenting the class but I'm unable to find the hours in the row so that can get the corresponding date.
    I've tried Index and match to no avail.

    screen grab.jpg
    I would be soooo grateful for any ideas or tips

    Gary

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index and match ...

    Hi Gary,

    Welcome to the forum.
    Instead of an image, please upload a sample workbook alongwith your expected results. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    Essen Germany
    MS-Off Ver
    Excel mac
    Posts
    13

    Re: Index and match ...

    So I'll try that again...
    I've attached a copy of the spreadsheet where I'm having the problem (challenge).
    I can figure out how to do monthly totals for each class - no problem. what I need is to list the classes on a daily basis ie : I taught on the 13.03.2013 - This class - and I was there for x hours. Simple.
    My Problem is that I can't retrive the date from the Summary... Grrr

    Date



    Class Hrs

    Maybe it's all the blank cells? using INDEX MATCH I can find the class but I can't for the life of me figure iue what column the hrs are in therefore I cant get the date... I do hope I'm making myself clear... I don't do computers

    Any help would be much appreciated

    Gary
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index and match ...

    I taught on the 13.03.2013 - This class - and I was there for x hours. Simple.
    My Problem is that I can't retrive the date from the Summary...
    Where can I find this in your workbook ?



    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    03-13-2013
    Location
    Essen Germany
    MS-Off Ver
    Excel mac
    Posts
    13

    Re: Index and match ...

    The Dates are in H5:M5 - H28:M28 - H51:M51 - H74:M74 - H97:M97 - H120:M120

    These dates only have to be copied the the invoice in there the class has taken place ie. a number in the cell below.
    I need the class- the hrs and the date for the Invoice sheet.
    ...and I thought it would be soooo easy

    Cheers

    Gary

  6. #6
    Registered User
    Join Date
    03-13-2013
    Location
    Essen Germany
    MS-Off Ver
    Excel mac
    Posts
    13

    Re: Index and match ...

    The Dates are in H5:M5 - H28:M28 - H51:M51 - H74:M74 - H97:M97 - H120:M120

    These dates only have to be copied the the invoice in there the class has taken place ie. a number in the cell below.
    I need the class- the hrs and the date for the Invoice sheet.
    ...and I thought it would be soooo easy

    Cheers

    Gary

    Sorry for posting twice
    Last edited by Gazza41; 03-14-2013 at 08:34 AM.

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index and match ...

    Okay.. I found H5:M5 in the sheet "Summary-March" sheet.. now what you need here and from where?

    I noticed that first you need to structure the data sheets correctly as it all messed up

    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    03-13-2013
    Location
    Essen Germany
    MS-Off Ver
    Excel mac
    Posts
    13

    Re: Index and match ...

    Great.
    If you look on the first sheet - Invoices March - you'll find the invoices "Rechnung". In the first column named "datum" B43- needs to go the date of the first occurrence of any class that belongs to "school1" (-A6:A12 summary- that's who the invoice is addressed too.)
    and I've worked.
    So, for example, the first occurrence of me taking the class "IT-E-EN-G-531"(G34) was on the 04.03.13 (H28) and I did 4 hrs (I34).
    The values would then be put on the invoice for "school1". H28 would be in B43 - G34 would be in C43 - I34 would be in D43.

    And the is should go find the data for B45:D45 ect for school1

    Maybe I'm using the wrong format or program. I was using an old workbook from another project I did in 2006 and it was only designed to total all classes! That's probable the mess you're talking about.
    Do you think I should start again and build a new version?

    Thanks again for your patience

    Gary

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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