# Index and match ...

1. ## 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. ## Re: Index and match ...

Hi Gary,

Welcome to the forum.

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

3. ## 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

4. ## 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. ## 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. ## 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

7. ## 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. ## 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?

Gary

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

#### 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