Thread: Choose a target cell in a table (XP & 2003)

1. Re: hoose a target cell in a table (XP & 2003)

See attached version. I added fomulas in column I of OT hours that concatenate the month number from column A with the File# from column B. This enables us to match on month and File# in one go. You can use this formula in E3, and copy it to the other cells where you need formulas:

=IF(ISNA(MATCH(E\$2&\$B3,'OT HRS'!\$I\$3:\$I\$25,0)),"",INDEX('OT HRS'!\$E\$3:\$H\$25,MATCH(E\$2&\$B3,'OT HRS'!\$I\$3:\$I\$25,0),MATCH(E\$2,'OT HRS'!\$E\$2:\$H\$2,0)))

or

=IF(ISNA(MATCH(E\$2&\$B3,'OT HRS'!\$I\$3:\$I\$25,0)),0,INDEX('OT HRS'!\$E\$3:\$H\$25,MATCH(E\$2&\$B3,'OT HRS'!\$I\$3:\$I\$25,0),MATCH(E\$2,'OT HRS'!\$E\$2:\$H\$2,0)))

depending on whether you want a blank cell or a zero if there is no match. The first part checks if there is a match, and if not, returns either a blank string "" or 0; the second part looks up the correct value in E3:H25.

2. Choose a target cell in a table (XP & 2003)

I want to record the o/t hours from the "OT HRS" worksheet onto the correct file # and month # on the "TOTAL HRS" work sheet. If there are no OT hours then a zero or nothing should be recorded in that cell. I included in the last worksheet what the "TOTAL HRS" should look like after the correct formula is applied. I used the "INDEX" with 2 'MATCH' functions. This formula did not yield the correct result. see July OT cells in "TOTAL HRS" worksheet.

One more simple question, If this can be fixed could you simply explain how the "Index" coupled with the "Match" function works together.

Thanks so very much.

3. Re: hoose a target cell in a table (XP & 2003)

Thanks for the quick response. If time permits, could you tell why isn't my fomula working.

Thanks Again!!

Martin

4. Re: hoose a target cell in a table (XP & 2003)

There can be several records with the same File# in column B. So a simple match on column B will not locate the correct instance. You must somehow match on the combination of month and File#, that's why I created an extra column that concatenates them, and used that in the MATCH formula.

Posting Permissions

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