Copying Formulas from one sheet to another without it taking the workbook reference

[bhujanga]

I have a number of Excel files that are all of the same general design with several sheets each.
I have added a number of formulas to one of them. These formulas reference cells within other sheets within that workbook.
I don't want to start over with all of the other workbooks so of course I just want to copy this group of formulas over to each of the other workbooks.
But when I copy these formulas, they not only include the sheet specification, but the workbook itself. It's nearly as cumbersome to go through and remove the workbook specification as it would be to re-enter all of the formulas from scratch. Is there a way to get it to copy without it wanting to point back to itself?
Thank you.

 

[SkipVought]

Hi,

I have a number of Excel files that are all of the same general design with several sheets each.

Why why why would you have all these similar workbooks? That's a huge red flag to me!

But the answer is to have a workbook TEMPLATE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bhujanga]

They're for collecting intersection data for thousands of intersections. A lot of different data is collected for each intersection. There are about 30 classes of intersections. An example of one class would be 6-lane mainline, 4-lane sideroad, unsignalized, undivided, 3-leg, urban. There might be 30,000 intersections of a single class. Aside from the fact that these files are already big enough to make Excel lock up intermittently, we want to keep all the classes in their own workbooks so different people can work on them. And so that various macros that process them can look at everything in the workbook without having to know anything about the classes. So when someone finishes verifying intersection properties that workbook can be processed and the results sent down the line.
Ultimately, a fully processed sheet is downloaded to a csv file, that is batch fed to another program that uses a huge accident database to ascertain accident history for each intersection and then determine how well it seems to be operating from a safety standpoint in comparison to other intersection of its class. This ultimately directs us to examine possible safety improvements for the more poorly performing intersections.

 

[SkipVought]

Well you got your answer.

They're collecting intersection data for thousands of intersections.

How is the data being collected and transmitted to you for your workbook analysis?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bhujanga]

Yes, I already have a template that I build these from, but I just found out that the download needs to change, so I have to modify some formulas within sheets that have already been built.
The data is selected from a federal database based on filters that determine the expected class. This is how I build out the template files for each class. Each intersection has coordinates. So, I build kmz files that are loaded into Google Earth and they go down the list and inspect the location and either pass it, re-classify it, or toss it.

Are saying that there is no way to copy formulas without them carrying the workbook reference? That's all I want to know.

 

[SkipVought]

Another possible solution is to

1) COPY the formula,

2) PASTE into a text editor, like Notepad or Word,

3) COPY the formula from the text editor and

4) PASTE into your new workbook IN THE SAME CELL REFERENCE AS THE ORIGINAL WORKBOOK.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Other options:
1) copy formulas anyway, replace workbook's name by empty string in formulas with Search&Replace,
2) VBA (here without error trapping), select source range and call the code (exact formula copies):
[pre] Sub CopyFormulas()
Dim v
v = Selection.Formula
Application.InputBox("Please select first cell of target range", "Target", , , , , , 8).Resize(UBound(v, 1), UBound(v, 2)).Formula = v
End Sub[/pre]


combo

 

[apstef]

ctrl+f the character "="
replace all with "#"
copy cells to new workbook
replace back "#" with "="

 

[kjv1611]

If this is a one-time fix, then VBA is definitely something you want to consider, assuming this is more than a few workbooks we're talking about.
If it's a process, then you need to fix the process. So you could build the VBA to fix the current files, but then if at all possible, definitely go the Template route.
Even if you used apstef's suggestion (which sounds pretty slick if it works), you could automate that with VBA using the FileSystemObject to loop through the workbooks.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

but I just found out that the download needs to change

What does that mean?

Is the download a text file?

How is this data getting into your new workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You have really left us in the dark by asking such a narrow question.

It appears that you periodically get a file, presumably a TEXT file, from which you construct a new workbook.

If you were to IMPORT the data from that text file into your workbook via Data > Get external data > From Text file... then all you need do is SAVE AS your workbook prior to importing new data. Everything would be in order, unless your import file changes structure.

But I'm just guessing at what you have. Still in the dark!

There's even a chance that if you were to leverage the Structured Table features of Imported data, you may not even need be bothered by some changes in table structure.

How's THAT!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]