[mrl]

Hello,
I have a spread sheet that has in column A, a list of days that start on 1-Jan -99 and go to 31-Dec-2009. (over 4 thousand rows). The cells are formatted “dd-mmm-yy (14-Mar-01). If I want to see a particular month, I have to know ahead of time what cell it starts in to use the “goto” command. Say, I want to see all of Oct-01. Right now, I have to know that it starts in cell A 647, and use the “goto” command. I tried to use the advance filter, but it will only display one row, not the whole month. I’ll attach a sample of the spread sheet.

Mike

[midders]

If this is a spreadsheet that you use regularly then I would recommend that you convert it to a database (import it into Access) and use a select statement/report to get the information that you want. Access is better able to handle large datasets and is faster and more stable.

[mrl]

Hello Midders,
I don't have Acess. I'm wondering if there is a way to use advance filter that will go to the first line in the database that matches the criteria and show from there down. When I try to do an "in place" filter, it only shows the match and not the whole month. With "goto", I can at least scroll thru the month

Mike

[bomb #21]

See attached.

Select a date from Sheet1!K1.

Press F5 -- type mmm --- press Enter.

Is that any help?

[mrl]

Hello, bomb #21,
Yep, that's exactly what I'm looking for. It'll save me hundreds of hours, fingertips, and cuss words! I don't have to have hundreds of buttons with goto macros. A couple of questions, tho. I'm still learning excel. How do I set up K 1 on my sheet? I can set up L 1 and M 1 ok. Also, why does mmm send the cursor where it does? I'm using Excel 2007. Also, where is "the void AKA edge of the Fens? I'm out of Michigan in the U.S.of A.

Mike

[bomb #21]

Sheet2!A1:A133 is the 1st-of-the-months -- I did A1:A2 manually, then dragged the pair of them down.

B1:B132 are basic formulas.

Press CTRL+F3 to show the Names dialog box. Click on "Firsts" in the list of names, to show that the named range Sheet2!$A$1:$A$133 refers to those cells (so if you update that list, you'll need to update the "Refers to" too -- don't forget that).

Select Sheet1!K1, check Data > Validation to see that the named range "Firsts" is the validation source for that cell. Cancel the dialog.

The Sheet1!L1 formula gives you the row in column A where K1 value is. The Sheet1!M1 formula combines INDEX and MATCH to give number of days in the month according to K1.

Press CTRL+F3 again. The second named range is mmm. Its "Refers to" is:

=OFFSET(Sheet1!$A$1,Sheet1!$L$1-1,0,Sheet1!$M$1,1)

That's a dynamic named range, with 4 "parameters".

OFFSET(Sheet1!$A$1,Sheet1!$L$1-1,0 means the range starts at [Sheet1!$L$1-1] cells down and 0 cells to the right of A1. So when K1 = 1-Jan-99, then L1 = 2, thus the range starts at A1 offset by (2-1) cells down and 0 cells to the right -- i.e., A2.

Then you have Sheet1!$M$1,1 dictating how many rows down and across from the start cell the range extends. So for K1 = 1-Jan-99, M1 = 31, making the range from A2 down 31 rows total & 1 column across. Or A2:A32.

K1 = 1-Feb-99, L1 = 33, range starts at A33, M1 = 28, range extends over 28 rows & 1 column. And so on.

Don't worry if this is really confusing. It took me literally years to "get" dynamic named ranges. A true Excel genius could explain it way better than me.

ETA: about 60 miles north of London, England. Michigan's a state, right?

[mrl]

Thanks bomb #21
Everything works perfect. I was able to move the dynamic named range and the others to different cells. Michigan is the state that looks like a mitten. I'm about 5 miles west of Detroit.
Mike