Search function

[Graham Mackay]

Hi, I have a workbook that I am setting up to manage Permits. It has two active sheets. Register is the database and Index is the input function. Within Index I also have a search function which I want to search the register and display all the open Permits by searching column N and displaying any with Blank cells. I am struggling to get a macro to do this. Any help gratefully received

 

[C with no eyes]

Assuming your register can be filtered, you could filter for blanks, copy visible cells and paste values where you want it to be viewed.

Alternatively, instead of a macro, you could create a helper sheet to pull info from the register and organise as required. (Whenever I create a helper sheet I first pull raw data from the required sheet like index(Register!A:A,ROW(),COLUMN()). This means your helper sheet will always look at the cell you intend it to and does not cause errors when rows and columns are deleted or inserted from the data sheet.)

 

[Graham Mackay]

Thanks for responding. Could you explain what you mean by a helper sheet?

Thanks
Graham

 

[Fluff]

You could use a formula like
=INDEX(Register!$A$2:$A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2)))

This will return all values from col A where col N is blank.

 

[Graham Mackay]

You could use a formula like
=INDEX(Register!$A$2:$A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2)))

This will return all values from col A where col N is blank.

Thank you, I will try and let you know how it works.

Graham

 

[Fluff]

 

[Graham Mackay]

Hi Fluff, that worked perfectly. Just wondering a couple more things. How could I get other columns to be displayed with other info? Also where there are no entries in column A it is showing #NUM!. Thanks again. Graham

 

[Fluff]

You can hide the #NUM by wrapping the formula in an iferror. I f by other columns you mean you want to show col B,C etc then you can use
=IFERROR(INDEX(Register!A$2:A$1000,AGGREGATE(15,6,(ROW(Register!$A$2:$A$1000)-ROW(Register!$A$2)+1)/((Register!$A$2:$A$1000<>"")*(Register!$N$2:$N$1000="")),ROWS(A$2:A2))),"")

Drag down & across.

 

[Graham Mackay]

Hi there, I tried this and it works in column A but when I drag it just populates with 12345. The formula doesn't update to the column. Should I remove the $ from it. Thanks Graham

 

[Fluff]

Not sure I understand what you mean, can you post some sample data using the XL2BB add-in.