I have no idea if what I want is possible. For the attached workbook I want to enter a formula in Cell K13. What I need it to do is search M7:AQ20 for the Values in Cell B27 and B30, and if they find it put the date of the column. So for K13 I want it to return the Value of Cell AF6.
For some reason, after N6 and cells to the right, your dates switch to 1900. Also, don't leave gaps between months. this is an excel nightmare. That has been fixed up to the end of March.
Fix that and use this in K7, copied down:
=IFERROR(INDEX($M$6:$NW$6,MATCH("*P*",$M7:$NW7,0)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
You might want to consider a redesign of this spreadsheet. I know it seems easy to enter data in a huge matrix like this, but working with that data and doing analysis on it is difficult. Keep the part that looks like the picture. But you may want to consider normalizing the rest of the data. (See attachment).
In this case:
Date | Employee Number | Type | Hours
In a normalized format, the data can easily be manipulated by a pivot table or with formulas. The big matrix data is nearly impossible to work with.
Just a thought.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks