Match or Lookup type function needed

[erikasn]

Hi all,

I am new to this forum, and appreciate help and guidance. I have created a list of events (events in row 1, 1 event per column) with the dates on which they occur listed in the rows below each event. All of the dates in column B on are dependent on the date in cell A2. I would like to create a calendar on a separate sheet that automatically populates with the events on their associated dates. If a calendar is not possible, a chart in sequential date order would work too- the goal is to easily locate the events occurring on a particular day. The actual dates need to update automatically if the date in cell A2 changes. I've looked into using a I greatly appreciate any ideas. I have fairly limited Excel experience. My data is attached.

Thank you so much!Reporting Dates.xlsx

[jaslake]

Hi erikasn

Welcome to the Forum!

Well, you've a bit of work to do. You'll need to build the Calendar (that is, show us what you're looking for as an end result) and perhaps we can build it with code. We can't even guess at that. Show us what you expect as an end result.

[erikasn]

Thanks so much for the quick reply! In the simplest form, (and I think a form that would be sufficient) it would be a table, such as the one attached. Not all of the cells would be filled, since some days have a greater number of events than other days, but it would be great I think the greatest number of events on a single day is probably about 15.

I would love to be able to use a calendar form where an additional month or year can easily be added, and the names of the events populate the calendar day boxes, but I don't know how to even build the outline of the calendar. I think the table attached would work, if it is useable from a code standpoint, but if there is a simpler or more elegant way to set it up such that all events on a particular day appear together, I am open.


Thanks again!

table example.xlsx

[erikasn]

I've been doing some research, and it seems like what I need is kind of the opposite of HLOOKUP (not VLOOKUP). I need a function that looks down all of the collumns and returns the value in the first row of each collumn that contains the lookup value. So if columns A,C,andE contained the date 5.12.2012, the function would return the values in A1, C1, and E1. Any ideas on how I can achieve this?

[erikasn]

I need a function that kind of the opposite of HLOOKUP (not VLOOKUP). I need it to look down multiple columns of varying length and returns the value in the first row of each column that contains the lookup value. So if columns A,C,and E contained the date 5.12.2012, the function would return the values in A1, C1, and E1. Any ideas on how I can achieve this?

Thanks so much!!

[DGagnon]

could you provide a sample sheet?

[haru]

I think you are looking to use a IF function.

Assuming lookup value is in A1, enter this into B1 and copy across the rows:

Please Login or Register  to view this content.

wasn't sure how to search the whole column without looking in B1, so where *** is put the max length of all the columns.

[DGagnon]

you cna search the whole column by using this

=IF(COUNTIF(B:B,$A$1)>0,$A$1,"")

but that will only search one column, i think the OP is looking for multipul matches in multipul columns.

[haru]

Hi DGagnon,

I was trying to search B2 to bottom, the search function will be in B1, so you can't search B:B, it will have a circular error. The OP is looking to:

the function would return the values in A1, C1, and E1

So I believe the OP is trying to just search for matches in each column.

[jaslake]

Hi erikasn

You'll need to educate me (or us)...I don't know what an "Event" is...so, start there.
What's an "Event"? How many "Events" do you have in Book Reporting Dates.xlsx...50? (Column AW is empty)
Your Calendar appears it will expand to 10/2016 (I believe that's the latest date I see). Is this what you have in mind?
Your table example.xlsx has one(1) "Event" listed (whatever an "Event" is). Fill table example.xlsx with MANY "Events".

I'm not "seeing" the picture. I don't have a clue what you're after.

[ConneXionLost]

Threads merged.

[erikasn]

Thank you so much for all of the help. The "events" (dates in the columns) are actually report due dates. Each column represents an independent report, the first cell of the column lists the report name, and the subsequent cells list the due dates (until 2016, but if you pull down the equations, you can get dates as far in advance as desired). Each report has its own schedule of due dates. For example, in Column B, the report, named in B1 ("BC-DR Plan or attestation of no change") is due every year on April 1, starting after the "Contract Execution Date" (cell A2). On the other hand, the report in column D (named in D1-community representative report) is due 45 days after the end of each quarter, starting after the Contract Execution Date (A2). For each report, the first due date (the date in row 2 of each column) is dependent on the date in Cell A2 (the Contract Execution Date), and the subsequent report due dates in each column are dependent on the previous report due date. My ultimate goal is to create a table (preferably in the form of a calendar, but a table with a list of dates in the first column is probably easiest, and is sufficient) that lists every report due on a certain day. For example, someone interested in what reports are due on March 3, 2014 can look at the table and see a list of every report is due that day. Currently the dates only go to 2016, but I would like the table to be easily (or better yet, automatically) expandable, so that the client can look as far ahead as he or she would like.

I have reattached the report due date file and the sample table file for reference.

I am not very good at describing what I need, especially in Excel, so please do not hesitate to let me know if I am unclear.

Thank you again! I am so impressed by the knowledge, expertise, and extreme willingness to help I have seen in the members of this forum. I hope I can return the favor as I gain more experience!

table example.xlsxReporting Dates.xlsx

[erikasn]

Oh, I forgot- there are a total 48 reports (I apologize for the annoyance, ignore row AX- it should be deleted and I left it in by accident).

[jaslake]

Hi Erika
I don't know if this does as you require...try it...let me know.

PS: I see a bit of a flaw...easily fixed. Get back to me.

[erikasn]

Thank you so much John. Sorry it's taken me so long to get back to you. This code comes really close, but I'm wondering if it can do one more thing. When I press "run" it populates a list of the dates on which the "events" take place, but the event names do not get populated in the adjacent columns. Any suggestions?

Thanks again!

[erikasn]

FYI, in case it makes a difference, I am using a Mac with Excel for Mac 2011.

[jaslake]

Hi Erika
Yes, I'd assume this makes a difference

I am using a Mac with Excel for Mac 2011

I also use a Mac...in fact the code was developed on a Mac except in Virtual Machine Mode using VMWare. The code works as designed on my platform. I don't have Excel for Mac so can't test it. The best I can suggest is try running the code on a PC based machine...if it does as required, there are several Mac experts on this Forum. One of them can help debug the Mac issue.
It may serve you better to start a new Thread for that such as "Modify Code to run in Mac Excel 2011".
In the meantime, I'm attaching a file that resolves this

I see a bit of a flaw

The flaw was that the procedure left blank dates...these have been eliminated.

[erikasn]

Thank you again!!

[erikasn]

(very silly question, where is the "PREFIX" box under "Go Advanced"?)

[jaslake]

Hi Erika
To mark your thread solved do the following:
- Go to the first post (your very 1st post) <---------------
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix. <--------------
- Change to Solve
- Click Save

I should add, after the passage of some time, this feature becomes unavailable in which case you'll need to PM a Forum Moderator and ask them to perform this exercise.