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.


Related: How to Update & Download NVidia GeForce 6100/nForce 420 WHQL Certified driver v.178.13,How to Update & Download NVidia GeForce 7600 GS Video Driver v.295.75 Certified,How Can I Update & Download NVidia GeForce 820M Driver v.344.48 WHQL,[Solved] Download NVidia GeForce GTX 560M VGA Driver v.296.17 Certified,Way to Update & Herunterladen NVidia GeForce GT 130M Video Treiber v.295.75 Certified,How to Update & Download SONY SVE14A1X1RH Realtek Ethernet Driver,Where to Download SONY SVS13A2W9ES Bluetooth Driver (Intel) v.2.6 - 2.6.23.40059,Best Way to Update & Download SONY VGN-CR203E Conexant HDAUDIO SoftV92 Data Fax Modem with SmartCP Setup Program v.7.62.0.50 driver,Best Way to Download SONY VGN-FE790G/N Wireless LAN Driver v.10.6.0.29,Method to Update & Download SONY VGN-NR31Z/S Firmware Extension Parser Device v.8.0.2.3,How to Fix Error 0x0000c1f5 SoluciĆ³n?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error
Read More: Desktop and Store tiles disappeared and 'change pc setting' isnt working,Deleting Printer\/Driver in Windows 7 Home Premium Tech Support,Deprecated DLL Components,How Can I Fix - Defrag and Backup questions.?,Troubleshooting:cyber buddy Error

No comments:

Post a Comment