|Even though I am not clear on the desired output (based on your example data) perhaps this will work for you. We're going to use a "Helper Column"|
Start with the data you posted in Response #4.
Use the formula you posted in your OP to create the extracted list - with the blanks - in another sheet. This is your "Helper Column". You can put the list anywhere you want.
Next, name the column where your placed the list, using the Name "BlanksRange". Set the scope of the Name to the sheet where the list exists, not to the workbook. If you set the scope of the Named Range to a single worksheet, then you can use the same Name (and therefore the same formula) in each sheet. In other words, there is no need to use a different name in each sheet, you just have to create the named Range in each sheet and set it's scope to just that sheet.
Now you can hide the Helper Column (the Named Range) if you want.
Finally, use Chip Pearson's array formula on the Helper Column (the Named Range) to create a list without the blank cells. Do this in each sheet.
Because the name "BlanksRange" will only refer to the Named Range in the sheet in which you created the "worksheet scope" Named Range, each sheet will display the correct list based on that sheet's Helper Column.
Keep in mind that an Array Formula must be entered with Ctrl-Shift-Enter for it to work. You can Ctrl-Shift-Enter the formula in the first cell and then drag it down as far as you need. Excel will know to create an array formula in each cell as you drag.
I start with this in Sheet1
1 Safety Op Mgr
2 Abrasive Wheels
3 Additive Bags Disposal x
5 Asbestos Awareness x
6 ATEX Awareness x
7 Behavioural Safety Training x
8 BOAS - Cat 2 Steam Boilers
9 BOAS Boiler Operator x
10 Confined Space Entry x
11 Permit to Work x
In Sheet2, your ISTEXT formula creates this list. Column A is Named
BlanksRange using the worksheet scope.
3 Additive Bags Disposal
5 Asbestos Awareness
6 ATEX Awareness
7 Behavioural Safety Training
9 BOAS Boiler Operator
10 Confined Space Entry
11 Permit to Work
Finally, using Chip Pearson's formula on that Named Range, I get:
2 Additive Bags Disposal
3 Asbestos Awareness
4 ATEX Awareness
5 Behavioural Safety Training
6 BOAS Boiler Operator
7 Confined Space Entry
8 Permit to Work
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03