How to Fix a Kernel32.DLL Error? : Troubleshooting:Data validation question. Maintaining and updating named ranges. Error

Monday, September 26, 2016

Troubleshooting:Data validation question. Maintaining and updating named ranges. Error

Data validation question. Maintaining and updating named ranges.

Have a list of product ID's in column A.
I'd like to reference this list from another worksheet.
Would also like to restrict the input values on this other sheet to only what is included in
the column A list.
Data validation works fine in accomplishing this, but by necessity, there are many duplicates present.
If I use column A on that sheet as a named range to restrict the entry on the other cell, DV
will bring in all the duplicates from column A and into the dropdown list. Duplicated items in the
dropdown would be confusing to the end users.
All's not lost.
I can use the advanced filter feature to extract unique values from the column A list and
assign a named range based on it to reference in the DV cell.
The downside to this, is that this will create 2 lists to maintain; in the event that new items
are added to column A on the source data worksheet. Will have to update the DV named range
too.
Not to complain, but I'm resigned to accept that that's just part of working with Excel.
Is there any way around this?
Pete

Solutions to the Problem Data validation question. Maintaining and updating named ranges.

Download SmartPCFixer to Fix It (Free)

It's complicated but straightforward.
First, you need a helper column of formulas on your first sheet.
So, in an otherwise blank column (for our purposes, I will assume column B) in row 2, enter the formula:
=IF(COUNTIF($A$1:A2,A2)=1,MAX($B$1:B1)+1,"")
and copy down well beyond your data area - more than you will even need.
Change the B references as needed.
Then, in another blank column, enter the formula (Let's say cell E2):
=IF(ISERROR(MATCH(ROW(A1),B:B,FALSE)),"",INDEX(A:A,MATCH(ROW(A1),B:B,FALSE)))
and copy down until you get a lot of "" values.
(Change the B:B reference to the column where your entered the first formula)
Then create a name, say, ValidList, and use this formula to define that name:
=OFFSET(Sheet1!$E$2,0,0,COUNTIF(Sheet1!$E:$E,">"""),1)
Change $E$2 to the cell with the first of the IF(ISERROR....
formulas, and the $E:$E to the column with those formulas.

Then select the cells on the other sheet, and use data validation, with the list option, using
=ValidList
as the data source.
And you're done.
The list will change as your entries on the first sheet column A change.
Just make sure you have enough formulas to cover all the entries.

If you failed to fix it, another option is to call Microsoft and walk through the process with a technician. To do this, dial "1-800-936-5700". Explain the error that you received and the steps in the update process during which it was displayed. The technician will be able to walk through the steps from getting a new product key to finishing the installation.

Another Safe way to Repair the Problem: Data validation question. Maintaining and updating named ranges.:

How to Fix Data validation question. Maintaining and updating named ranges. with SmartPCFixer?

1. Download SmartPCFixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in this software. Like dll downloading, junk file cleaning and print spooler error repair.

How to Fix a Kernel32.DLL Error?