MTS to AVI Converter: Solution to Problem: Delete a column not affect a named ranges

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.

-John

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,

=INDEX(Test.xlsm!Current_Month,MATCH(A2,Test.xlsm!Vendor,0))

...
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.