Data Validation Ignore Zero Length Strings. 1

[grnzbra]

I have a table of frequencies when jobs are performed ie Monthly, Annually, Weekly. Each one of these can have several records with a job number. So I could have several annual jobs, several weekly jobs, etc. To the right of these two columns, I have columns corresponding to Monthly, Annually, Weekly etc with the cells set to =IF($A319="Monthly",$C319,""). All of these have named range. On another sheet, I need to provide a validation list that selects from the corresponding column in the first sheet. Unfortunately, the Ignore Blanks checkbox in the validation design form doesn't ignore zero length strings. I've tried removing the "" from the formula, but then when I get FALSE in the cell. I've also tried to use a pivot table but only got counts of how many job numbers each frequency had.

The basic problem is starting with the two columns, Frequency and JobNumber, I need to provide a validation list of job numbers consisting of only those job numbers whose frequency corresponds to the name of the column, Monthly, Anuually, Weekly etc, with no blanks in the list.

Any suggestions would be greatly appreciated.

 

[SkipVought]

Hi,

Please post your workbook.

Your explanation is not at all clear.

Furthermore your state, "I need to provide a validation list of job numbers consisting of only those job numbers whose frequency corresponds to the name of the column, Monthly, Anuually, Weekly etc, with no blanks in the list."

This can easily be done via an AutoFilter to display only those values not in...
[tt]
Monthly, Annually, Weekly, etc
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well now I'm getting a better picture of your requirement.

This can be done using two Data/Validation LIST: one referencing a hard list of your frequency values, and the other as array formula using the selection from the first DV Drop Down.

Or you can use Microsoft Query to generate a list for a given frequency value.

Both approaches would use a DV Drop Down. The difference is in the way the list of jobs is obtained.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[grnzbra]

This is the worksheet that needs the validation using a list on the monthly, annual etc columns. Below is the sheet that is the source of the list information. I thought that instead of using "" as the FALSE result of an IF statment on the second sheet columns, I would try to use an empty column. However that just resulted in a 0 when the result was false. Is there any way to force a NULL into a cell?

I'm not sure how I would use the two Data/Validation LIST method.

I tried the MicrosoftQuery and it seems like it would work very nicely except that it wants to save the query as a separate file. I need to have this within the one .xlsm file. Is there a way to do that?

Each of the "frequency" columns below is a named range - _Weekly, _BiWeekly, _Monthly etc.
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/image/upload/v1449237395/tips/ExcelDropDown_jc6eoi.bmp[/url]

Each "frequency" column below is a named range - Weekly, BiWeekly, Monthly etc.

 

[SkipVought]

I have used Microsoft Query for nearly 20 years and have never once saved a query in a separate file as you suggest. The query is an object in a worksheet, and gets saved with the workbook.

I am still unsure how you want to use this information: even more unsure after reading your last post. I had hoped that you would post you workbook, rather that a worthless picture: well not completely worthless as it did inform me that my assumptions were incorrect.

How do you intend to use a job list for a given frequency?

Maybe a better question might be, what is the intent of this workbook application. You might be trying to use a screwdriver for a purpose that a chisel would function better.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[grnzbra]

I'm not allowed to post the workbook. I also don't know the details of how this is to be used; I only have a spec sheet.

This workbook is being used for entering information about pieces of equipment.

This as an interim sheet for Preventive Maintenance Checklists

On the first sheet:
In the first column, the user will enter a PM Name. The second he will enter the appropriate shop code. In the frequency columns he will select from a list of checklist codes. The validation list should only show codes for that particular frequency - Weekly, Monthly, Annually etc. The codes come from a table that lists the various Checklist Codes for equipment types for each frequency. Since there is a different code for each Equipment Type/Frequency combination, there are multiple records for each frequency.

I'll give the Microsoft Query a try again and see if I can figure out how to save it to the workbook.

 

[SkipVought]

General instructions:

Each time you add a query table to a sheet, it's there, even if you do not "see" it. So be careful about the process. Once a QT is added to the sheet, it can be EDITED but selecting in a cell of the resultset and EDITING by right-click and select Edit Query.

LEAVE AN EMPTY COLUMN BETWEEN EACH QT!

If recommend adding these to a brand new sheet.

Each QT has a Table Name that you can over-write. If nam each of you QT table results like tWeekly, tAnnually etc. These are Structured Tabkes.

Just add each QT, structure the query by example grid to return a distinct list of the jobs for the given frequency in the WHERE clause, like
[tt]
SELECT JOB as [Weekly]
FROM [Sheet1$]
WHERE FREQUENCY = 'Weekly'
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[grnzbra]

Thank you. I do believe I've got it now.