Formula to Exclude lines that dont match criteria

**chrismike** · 06-26-2013, 04:02 PM

Hi,

I have a spread sheet that has a master visit planner on sheet 1 and then monthly sheets. I have entered formulas to extract the monthly data but cannot figure out how to exclude lines that do not meet the criteria.

I've attached a sample sheet which hoperfully makes things a little clearer.

Thanks in anticipation of your help

Chris

**GregTheSquarePeg** · 06-26-2013, 04:17 PM

Hi Chris,

Had a quick look at your sheet. Could you please clarify what you mean by 'exclude lines'

Gregg

**chrismike** · 06-26-2013, 04:31 PM

Hi Gregg,

I would like each month to list just those lines that belong to it...ie no gaps..

Thanks

Chris

**GregTheSquarePeg** · 06-26-2013, 05:29 PM

I'm a VBA novice, but this recorded macro works.
I've added a button you can click to run.

If you want to add further sheets, with other month names. You'll need to expand the macro.

Gregg

**FDibbins** · 06-26-2013, 05:52 PM

Hi and welcome to the forum

Here are other options that dont use VBA...
1. On the month sheets, apply filters and filter out all blank rows

2. in L:N (expand as needed for extra months), copy this down...
=IF(I2="",MAX($L$1:L1),MAX($L$1:L1)+1)

Then in Jul - Aug sheets use this, copied across and down...
=IFERROR(INDEX('Visit Schedules'!$A:$H,MATCH(ROW(A1),'Visit Schedules'!$L:$L,0),MATCH(Jul!B$1,'Visit Schedules'!$A$1:$H$1,0)),"")

Note: All headings have to be identical on each sheet for this to work (I dont see "Whs-code" on the 1st sheet?)

**chrismike** · 06-27-2013, 06:43 PM

Hi

Thanks Greg, work a treat and I appreciate your time and effort

very many thanks.

FDibbins, thanks for your efforts also, had to make a couple of modifications to make your formulas work as I wanted but wouldn't have got far without your input also.

Thanks to both again, top notch stuff

Chris

**FDibbins** · 06-27-2013, 07:18 PM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.