Link data in two spread sheets but remove the blank cells from the second spread sheet

[rustybenson]

Hi
I have a spread sheet that searches and lists information from another spread sheet based on 2 types of criteria and records which are vehicles at different locations . I want to put that vehicle information into another spread sheet under the respective location the vehicle works from.
However i don't want blank cells in between the vehicle list in the final spread sheet.
Is there a way I can automatically move the data to its correct location and remove the blank cells in the last sheet.
Regards
Russell

 

[NiMip]

Can't give you a tailor-made solution without having the spreadsheets to play around with, but I might be able to give you an idea. There's some clever stuff you can do with combining SMALL and ROW. I've given a simple example below, pressing CONTROL+SHIFT+ENTER as it's an array formula


[TABLE="class: grid, width: 578"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Name List[/TD]
[TD]Include?[/TD]
[TD]Shortlist[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Jeff[/TD]
[TD]FALSE[/TD]
[TD]Fred[/TD]
[TD]<< Formula in D3 is
=IFERROR(INDEX($B$3:$B$11,SMALL(IF($C$3:$C$11,ROW($B$3:$B$11)-ROW($B$3)+1),ROWS(B$3:B3))),"")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Stacy[/TD]
[TD]FALSE[/TD]
[TD]George[/TD]
[TD]| copied full way down[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Fred[/TD]
[TD]TRUE[/TD]
[TD]Camilla[/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Anne[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]George[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Glen[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]|[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Camilla[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD]v[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Let me know if you need some explanation as to how it works, but following through the calculation with "Evaluate Formula" will probably do a better job than I can in words.