Create a list on a new sheet excluding dates

**HelpWanted2020** · 03-25-2020, 10:28 AM

Hi There,

Attached is my sample workbook.

I am trying to create a list from my follow up tracker on a new sheet.

On the new sheet I want to list all Patients on 'Follow Up Tracker' sheet that do not have dates in column B.

The formula I have in Sheet 1, column A, doesn't seem to work because on 'Follow Up Tracker', the column B is formatted as a date.

I am looking for a workaround that keeps Column B formatted as a date but still lists all patients on the new sheet that do not have date values.

I hope I am clear enough, if you have any questions, please ask.

Any help is appreciated.

**Eastw00d** · 03-25-2020, 11:00 AM

Perhaps I don't understand the question, but is filtering an option? Excluding the dates?

Cheers
Erwin

**HelpWanted2020** · 03-25-2020, 11:06 AM

Hi,

Thanks for the suggestion but I don't want to filter.

On Sheet 1, I want to automatically list the patients that do not have dates in Column B on "Follow Up Tracker" sheet.

**Eastw00d** · 03-25-2020, 12:15 PM

Maybe this is a solution:
I made a pivot-table, checking on numbers (date is a number) and filtering them out, next to that pivot a VLOOKUP-function.
You only have to refresh the pivot <ALT-F5> when the Master is changed.

Cheers
Erwin

**HelpWanted2020** · 03-25-2020, 12:20 PM

Hi Erwin,

Your solution seems to work.

However, this is a workbook that I will be sharing across multiple users in my company and they will all be working in it.
To train them to refresh will be an extra task of its' own (People just don't listen sometimes).

I was really hoping I could automate everything for them.

I really would just like a working array formula.

I'm sorry to be such a picky person.

**Eastw00d** · 03-25-2020, 12:42 PM

I am not good at that, maybe somebody can help you with that,
but I can/have automate(d) the update of the pivot, on the background.

Cheers
Erwin

**HelpWanted2020** · 03-25-2020, 12:48 PM

Erwin, I appreciate your time and help.

Maybe someone else can help. Pivots don't seem like the right solution for the excel noobs that are my coworkers.
I was hoping to use an array formula and protect the sheet after so no formula changes can be made.

**jeffreybrown** · 03-25-2020, 02:18 PM

Hi HelpWanted2020,

Solutions can come in many different forms and Erwin's suggestion with a pivot table seems appropriate; however, we do understand it's sometimes hard to get your solution across to others.

Anyway, here's a solution with a helper column that might work for you. But in reality, if we use a helper column, why not just filter the new column to a 1.

Row 859 and 934 both have dates, but they also have text in the cell making them both text.

**Eastw00d** · 03-25-2020, 04:59 PM

Hi, just learned a few new things, thanks to this forum, better than an array-formula, and I came up with following:

Please Login or Register  to view this content.

the length of the list is now 947 lines but you can adapt the formula to your own needs.

Cheers
Erwin

**FlameRetired** · 03-25-2020, 05:44 PM

@ Erwin. I cannot upload your file. I get a forum error message. Can you try again?

Similar to Erwin's .... also eliminates blanks and strings which start with a date.

Formula:

Please Login or Register  to view this content.

**Eastw00d** · 03-26-2020, 12:35 AM

@Dave: Super!

Cheers
Erwin

**HelpWanted2020** · 04-23-2020, 03:08 PM

Hi Eastw00d

Love this solution. I have a follow up question.

In addition to only wanting columns without dates, I have another column I want to use.

This column contains number values only and I want this to be checked when making the list on Sheet 1

I would like the list in sheet 1 to only include people with no date in column B on Follow up Tracker sheet and the value column F on Follow up Tracker sheet to be above 3.

Pretty sure I have to throw an AND in there somewhere but not sure where.

**Eastw00d** · 04-23-2020, 03:56 PM

No "AND" this time, as you want it to be part of the condition:

Please Login or Register  to view this content.

to copy down in column A of Sheet1.
Cheers
Erwin

**HelpWanted2020** · 04-23-2020, 04:32 PM

It works great in Excel!

I tried it in Google Sheets and there must be a feature missing!

Guess I'll be switching my team to Excel sharing. lol

Create a list on a new sheet excluding dates

LinkBack

Thread Tools

Rate This Thread

Display

Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Re: Create a list on a new sheet excluding dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Networkdays - excluding weekend and custom list of dates

[SOLVED] Create combobox .list excluding some columns

VBA - List Dates excluding Weekends and Holidays

[SOLVED] Create a list excluding cells with a certain value

Create New Count List Excluding Duplicates

Excluding dates from a list

List Dates Excluding Holidays but Including weekends

Bookmarks

Bookmarks

Posting Permissions