MS Excel 2010 - #REF error 2

[Knicks]

Okay, once again I find reason to really hate Excel over Access.

I have an already created highly programmed Excel workbook. The process was for the user to cut and paste data into the 'Item Tested' worksheet. They were running into difficulty with the column ordering so I decided to try using a an Access data source for the 'Item Tested' worksheet and get away from that error and messy prone process. 'Item Tested' is referenced in various places on other sheets. I just wanted to simply slip my information in and move away from cut & paste. Seemed semi simple.

But because Excel kind of sucks, I couldn't easily just set a data source for the 'Item Tested' worksheet, when I set the datasource it either wants me to pre-determine the cell range (huh, like I know the row count each time?) or put in a new worksheet. Okay, fine. I can always rename everybody. hunky dorry. But no, in the small amount of time that switch of renaming worksheets, the other worksheets that reference 'Item Tested' go crazy and decide to put an #REF into all the cells that reference it.

Why is Excel doing this? Does anyone know of a, oh god, I use this loosely, "Simple" way to get my datasource into the 'Item Tested' worksheet w/o making the rest of workbook freak out?

 

[mintjulep]

Turn off automatic recalculation.

 

[Knicks]

Dude,

I normally drink margaritas or caipirinha....but I'm drinking Mint Julips! You rock, they had a recalculate prior to saving so I just needed to uncheck that.

Thanx for making my weekend!

 

[Knicks]

something odd though is going on.

I checked the formula and when I try to rename the old sheet, now the formulas are being reset with the new sheet name.

So it goes from referencing 'item tested' to 'item tested2'

Can I turn that off until I have my new 'item tested' in place?

 

[Knicks]

Okay,
The universe is still aligned, the program is nightmarish. For whatever reason the formula is truly tied to the sheet, so if I change the sheet name the formula changes too. If I delete the sheet, #ref immediately appears.

My workaround was to simply delete the contents of the sheet and set the data source to the cleared sheet so renaming-recreating was not necessary. I had to hard code my cell range but so be it. Hopefully I won't have to look at this file by the time it goes belly up.

I can't believe IT continues to hate on Access when Excel files are the biggest nightmare EVER....and crickets. Why I couldn't easily recreate a sheet to satisfy a formula is beyond me. Every auto crud was set off to my knowledge.

 

[SkipVought]

Kind of seems as if you don't understand enough about Excel and it's capabilities.

Does Excel have its limitations? Of course, but Excel does not "suck." It is a tremendously versatile tool, in skillful hands.

I see no specifics in your posts. Nothing regarding the formula in question! Nothing about the structure of the sheet being referenced and the nature of the data.

Lets see something of substance that we can make some specific comments about.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

Well to a certain extent anyone posting here has a knowledge gap or else well they'd be somewhere else. If you would like an example here:

From the Vendor Data_Formulas worksheet:
=IF(OR(ISBLANK(VLOOKUP($C79,'item tested'!$B$2:$BE$878,55,FALSE)),ISNA(VLOOKUP($C79,'item tested'!$B$2:$BE$878,55,FALSE))),"",VLOOKUP($C79,'item tested'!$B$2:$BE$878,55,FALSE))

All I wanted to do is replace the 'item tested' worksheet. If I changed its name, the name would change in the formula above. If I deleted it the #ref would appear and would not right itself even after the new 'item tested' was created.

I did as MintJulip above mentioned so that calcs were set to Manual. I just simply wanted to replace the 'item tested'worksheet. Program is smart enough to kill a formula as soon as the link is broken but not quite smart enough to realize the link is back and fix itself. The last 2 places I've worked at people are using Excel as a database and getting themselves into all kinds of data integrity issues, having to use Vlookups and other formulas to compensate.

 

[mintjulep]

getting themselves into all kinds of data integrity issues

You mean like destroying references?

Once you delete the "item tested" sheet the reference is DESTROYED. Done, finished, gone, kaput, that's it.

How is Excel supposed "to know" that you will be creating a new sheet that just happens to have the same name? It's just not a reasonable expectation.

You need to do one of the following:

1) Create a NEW sheet with a NEW name and put your new data there. Change all of the references to "item tested" to the new sheet name. Then delete the "item tested" sheet. Finally, if you want to, change the name of the new sheet to "item tested".

2) Change the DATA on the "item tested" sheet, cell-by-cell.

 

[Knicks]

Thanx for the clarification MintJulip.

From a maintenance point of view thats pretty rough. I can delete a table in Access and all the references remain, as long as you don't call it no error will appear - and it certainly wouldn't change code. I can import another table with the same name and all is well. This goes for forms, queries, reports. Like nearly everything.

This file I inherited, I would never want to touch those formulas if possible, let alone LOOK in every cell for a reference. My experience with Excel is it is great for pivot tables / graphs, transferring data from 1 system to another, and basic end user noodling. If you are having to put Vlookups to other sheets, its time for a relational database.

 

[Knicks]

My reference to "data integrity" issues is more towards I've yet to run into one of these highly programmed Excel workbooks that actually bothered with Data Types.....so a column can go from mumeric to text to dates ~ all without ever bothering to use data validataion. I can see that is possible in Excel but a whole bunch of work. The same work that keeps people from using the appropriate program

 

[mintjulep]

When the only tool you know how to use is a hammer every problem looks like a nail.

Or alternately

Everything is done for a reason. But sometimes the reason isn't very good.

 

[SkipVought]

perhaps using the INDIRECT() function, where you would reference a cell containing the sheet name in question which can be changed at will.

You could also do a REPLACE ALL of the #REF! to the new sheet name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}