Vlookup question

[tapsmiled]

If column A:A contains dates and B:M contain data, could I use a Vlookup formula on 12 separate sheets to find all entries from each month and fill A:M on the corresponding monthly sheet?

I tried this and was just going to copy it to each corresponding sheet, but it did not work.

Please Login or Register  to view this content.

I also don't know how to apply that code (other than copying it down to infinity) so that no matter how many entries exist for each month, they will be copied onto the worksheet for that month.

Can anyone help? Thanks in advance

[FDibbins]

Hi again

If all you are doing is pulling summaries for each month, unless you want to be able to see each month all the time, why not just have 1 summary sheet and get it to pull a month specified by you? (and while you are thinking about that, can you do the usual - upload a sample whowing what you have abd what you want lol)

[tapsmiled]

That's exactly what i want to do. Standby for sample. I was trying to see if I could do it outside of VBA, but I don't possess the proper skills yet.I would like to be able to separate the monthly values so that I can print them if necessary.

[Tony Valko]

One way...

If Sheet1 contains the data...

In column N enter a formula to return the month number:

=MONTH(A5)

Copy down as needed.

Then, apply autofilter and filter on column N = some month number.

Copy the visible cells and paste into the monthly sheets.

[tapsmiled]

@Tony: thanks for the response. I originally posted this in the VBA forum because it's based around a UserForm and the data generated from entries via that form are hidden on Sheet1. There will be multiple users, and I figured i would create a Command Button to prompt an entry from a ComboBox: "Which month do you want to print?". I have to have it is a simplified action....not forcing the user to search or filter. Since I couldn't figure it out in VBA, I figured I could try the long way and have 1 master list (Sheet1) then 12 other Sheets (1 for each month) and by using some type of lookup, I could achieve roughly the same result.

[Tony Valko]

How much data do you have?

It could be done with formulas but a lot of them (100s or 1000s) will slow down your file.

If you're already using VBA for other things why not use the VBA to do this? (that's easy for me to say as I'm not a programmer!!!)

[tapsmiled]

There will likely be hundreds of entries for each day with columns A:M.

I'm just getting started with VBA, so my knowledge is quite limited. I'm exploring every possible option.

[FDibbins]

Apart from maybe being able to change the date in A1, isnt that pretty much what you already have?
(change D9 to =COUNTIFS(Sheet1!C:C,TRUE,Sheet1!A:A,$A$1)

[Tony Valko]

There will likely be hundreds of entries for each day with columns A:M.

That sounds like too much to use formulas.

You can try to adapt the technique demonstrated at the link and see if the file performance is acceptable.

Lookup with multiple instances of the lookup value

[tapsmiled]

Sorry, I'm referring to Sheet3 (blank at this point), not Sheet 2. Sheet 2 is just the running totals. Sheet1 is the actual queries generated by the UserForm for all months. At some point, I may need to print some of those queries, and the easiest way to do it is to be able to print an entire month rather than the whole sheet (which will probably be massively long). So, I thought of having a separate sheet for each month generated by the data entered onto Sheet1, and the user would be able to just print that month.

[tapsmiled]

@Tony...wow. That's quite lengthy. I'll see what I can accomplish unless anyone else has a suggestion or can adapt it for me to copy.

Oh, and yes I'm being lazy. My brain is Excel mush these days because of another project that is stumping me.

And if someone has a VBA solution, I'd take that as well.

[Tony Valko]

I put together a small sample file.

This formula entered in A2 on Sheet2:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,1/(TEXT(Sheet1!$A$2:$A$15,"mmmm")=$A$1)*ROW(Sheet1!A$2:A$15),ROWS(A$2:A2))),"")

Copy across to M2 then down until you get blanks. Format column A as Date.

[tapsmiled]

@Tony: I tried to add that code, and it gave me a circular reference.

[Tony Valko]

@Tony: I tried to add that code, and it gave me a circular reference.

I downloaded your file from post #3.

What exactly do you want to do with this?

[tapsmiled]

Right now, I have sample data added onto Sheet1 Rows 2-7. Each row's data contains a date....in this case the dates are from January to July. I would like, for example, only the rows containing July to be present on Sheet3, where I put your code. Then, on 11 other yet to be created sheets, I would like to have each individual month's data to go onto their respective month's sheet. When I applied your formula, it gave me a circular reference warning, and every column is filled with a zero.

I hope that explanation is clearer.

[Tony Valko]

Will all the dates be within the same year? Can we just lookup the month name or do we need to lookup a month in a specific year?

[tapsmiled]

If all goes well, this will be several years.

[Tony Valko]

Try this...

On Sheet3...

Enter the 1st of the month date in cell A1 for the month/year that you want the data filtered by.

For example, A1 = 7/1/2014

Let's assume you have column headers in A3:M3.

Enter this formula in A4:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,1/(TEXT(Sheet1!$A$2:$A$15,"mmmyyyy")=TEXT($A$1,"mmmyyyy"))*ROW(Sheet1!A$2:A$15),ROWS(A$2:A2))),"")

Copy across to M4.

Format A4 as Date

Empty cells from Sheet1 will be returned as 0. You can set that display of 0 values be suppressed.

Select the range A4:M4 and copy down until you get blanks.

It sounds like this will end up being a massive file. I'm thinking all these formulas will slow things down at some point.

[tapsmiled]

That did it!!!

Instead of copyingthe formula down to infinity, is there a way to simply force entries to the next line...perhaps when the workbook opens? I apologize if this is a silly question, but a bogged-down workbook does very little to facilitate use.

OR.....

Is there a way (through VBA perhaps) to force a printing action at the end of the month?

[Tony Valko]

Sorry, not a programmer!

[tapsmiled]

Okay, no problem. Neither am I........yet.