Sorting without spaces

[tagman]

Hi,
I have a set of data that I'd like to divide up onto individual worksheets using one criterion to select it. When I use this formula
=if(sheet1!a6="Criterion",b6,"")

I get a lot of spaces that I don't want. Is there a way to do it without the spaces?

Thanks

 

[Sektor]

Weird question. What namely do you want?

 

[tagman]

The list of data I have is
Place name
<TABLE style="WIDTH: 670pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=891 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" width=16><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><TBODY><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Nottingham</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>451131</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40718.795138888891">24/06/2011 19:05:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>3</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Washington</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450986</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40718.509027777778">24/06/2011 12:13:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>3</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Washington</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450980</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40718.464583333334">24/06/2011 11:09:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>3</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl41 height=15 width=94>SouthEast</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl48 width=54 colSpan=2 x:num>450843</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl41 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl42 width=64 x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl41 width=172 colSpan=2>Equipment Check</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl49 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl43 width=141 x:num="40718.079861111109">24/06/2011 01:55:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl44 width=131 x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #64ff64; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl45 width=46 x:num>3</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>SouthEast</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450679</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40717.419444444444">23/06/2011 10:04:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>4</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Washington</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450634</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40716.697916666664">22/06/2011 16:45:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>5</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Washington</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450572</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Install Further Attempt</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40716.616666666669">22/06/2011 14:48:00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>5</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 71pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 height=15 width=94>Washington</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 width=54 colSpan=2 x:num>450541</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 101pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl36 width=134>XXXXXXXXX</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 width=64 x:num>30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 129pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 width=172 colSpan=2>Monitor moved A/C-Phone</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 41pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl47 width=55 colSpan=2>No Crew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 106pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 width=141 x:num="40717.844942129632">23/06/2011 20:16:43 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 98pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 width=131 x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ff6464; WIDTH: 35pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 width=46 x:num>3</TD></TR></TBODY></TABLE>

I'd like tohave all the lines for Nottingham on one worksheet, Washington on another and so on. When I use the if formula above I get gaps where the place does not equal the one I want.

 

[VoG]

Take a look at Excel Explosion http://www.datapigtechnologies.com/freeware.htm

 

[tagman]

Thanks for that VoG but I need it to be as simple as possible because others with even less Excel skill than I will need to use it. Also the firewall at work would probably melt if I tried to download something from outside!

 

[Ruddles]

I'd like tohave all the lines for Nottingham on one worksheet, Washington on another and so on. When I use the if formula above I get gaps where the place does not equal the one I want.

Put a filter on column A of each sheet which shows just the values you're interested in for that sheet. That will automatically readjust itself as your data changes.

 

[tagman]

Put a filter on column A of each sheet which shows just the values you're interested in for that sheet. That will automatically readjust itself as your data changes.

I'm not with you. How do I filter data that's not there, if you know what I mean?

 

[Ruddles]

I don't really know what you mean. Maybe I misunderstood the requirement. Let me see if I've got it...

You have a master sheet with data for all cities in it. You have a Nottingham sheet with references to the master sheet on a 1:1 basis, but any non-Nottingham cells in that sheet are set to spaces, so only the Nottingham entries are visible. Rows which point to non-Nottingham entries in the master sheet show up as spaces in the Nottingham sheet.

Similarly rows in the Washington sheet which point to non-Washington entries in the master sheet show up as spaces in the Washington sheet.

You want to eliminate the spaces so all the rows which contain data are closed up next to each other.

Is that correct?

If so, set up a filter on Column A of your Nottingham sheet and make sure only Nottingham is checked. Repeat for each sheet, checking only the box for the appropriate city.

(Actually I would just keep the master sheet and set the filter up on that. The users could use the filter, they wouldn't have to switch worksheets and no-one would need to maintain the separate city sheets.)

 

[tagman]

Thanks Ruddles,
that worked fine. However is there any way to leave the filters in place so that when the data is taken out and put back in again, for example when the report is run on a daily basis, they don't give gibberish? I really want this to be as automated as possible so that anybody can use it.

 

[Ruddles]

I thought they'd be saved with the worksheet and that they'd refresh automatically when the workbook recalculates.

Is that not the case?