Monday, September 26, 2016

Solution to Problem: Delete a column not affect a named ranges

Delete a column not affect a named ranges

Greetings, thank you for looking.  The situation is that there is a worksheet that contains 3 columns of rolling monthly data (Column A = Vendor, B = Current Month, C = Next Month).  The beginning of each month a user goes in and deletes column B (which
moves column C to the left) and then they start entering new information in Column C,
The need is to link to the data in a secondary workbook.  There is roughly 500 rows of data so I do not want to hard code the link by cell, especially because the number of rows can vary slightly every few months.  I tried using named ranges but when Column
B is deleted, the named range breaks.  I believe this must be some sort of referential integrity.  I have tried using $ to define a staic range of cells being used in the named range and not using $, but unfortunately neither is maintaing the cells in teh
named range. 
Is there a way to have a named range always equal the specified cell(s), regardless of cells, rows or columns being added or deleted?
Thank you in advance for any assistance you have for me.

Anwsers to the Problem Delete a column not affect a named ranges

Download Error Fixer for Free Now

Is there a way to have a named range always equal the specified cell(s), regardless of cells, rows or columns being added or deleted?

You can use the INDIRECT() function within the Name Manger to assign Current_Month to =INDIRECT("Sheet1!$B:$B").
In this way, an external reference can be made to Current_Month through a formula like,


and those references will hold when Test.xlsm's column B is deleted in favor of shifting the data left.


This method is a work-around of INDIRECT()'s restriction of not being used in external references.

Windows Recovery - Totally Fix All Errors including Delete a column not affect a named ranges

  • Click on the Start button on the Taskbar.
  • Type "System Restore" (without quotes) in the Search box and hit Enter or,
  • Click on System Restore (when you see System Restore pop up  in the list of search results).
  • Make sure the "Recommended restore" radio button is checked on the restore utility window.
  • Click on Next and follow the instructions given.

Note: There is the option on the restore utility to select "Choose a different restore point". Unless you have a specific reason to select a different date and time than what Windows recommends, just select the recommended option stated in the steps above. If you choose to select another one, follow these steps:

Click on that desired date and time, and Windows 7 will perform a scan for any affected programs in that restore point.

Once completed, click Next and follow the instructions given.

Once you have selected the restore point, your system will start the restoring process, after which you will have to restart the system.

Recommended Method to Repair the Problem: Delete a column not affect a named ranges:

How to Fix Delete a column not affect a named ranges with SmartPCFixer?

1. Download Error Fixer . Install it on your system.  Click Scan, and it will perform a scan for your computer. The errors will be shown in the list.

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

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating 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 -,Best Way to Update & Download SONY VGN-CR203E Conexant HDAUDIO SoftV92 Data Fax Modem with SmartCP Setup Program v. driver,Best Way to Download SONY VGN-FE790G/N Wireless LAN Driver v.,Method to Update & Download SONY VGN-NR31Z/S Firmware Extension Parser Device v.,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: Troubleshooting:DELEATING OLDEST E MAIL Error,Tech Support: Dell skin configuration-constantly advised that it needs to be configured. Missing shell? Also, cannot get Dell support online.,Fast Solution to Error: Data transfer rate from a USB flash drive suddenly dropped from 17mb/sec to 6.5 mb/sec,How Can I Fix - Dell Vostro laptop with Windows 7 won't install hotfixes for IE9?,Dell computer failed a spindle test. What can I do to fix it? [Anwsered]

No comments:

Post a Comment