I have a worksheet which has a bunch of formulas in rows (i.e. data entered
in columns C through F result in formulas results in columns G through
whatever).

All I want to do is to copy a row and move the formulas into another
worksheet while keeping the references to the original worksheet intact. So:

=COUNTIF(G$3:G301,G301)

becomes...

=COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.

I'm copying from row 300 in the original to Row 2, to make things a little
more complicated, but isn't there a way to do this?

Thanks in advance!

"Rothman" wrote:

I have a worksheet which has a bunch of formulas in rows (i.e. data entered
in columns C through F result in formulas results in columns G through
whatever).

All I want to do is to copy a row and move the formulas into another
worksheet while keeping the references to the original worksheet intact. So:

=COUNTIF(G$3:G301,G301)

becomes...

=COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.

I'm copying from row 300 in the original to Row 2, to make things a little
more complicated, but isn't there a way to do this?

Thanks in advance!

Any takers? This would be a big help!

Did you ever sort your problem?
One solution is to copy the formulas in their entirety into a new worksheet
then use Data Replace i.e. Find G$ Replace with Data!G$ - then a
second Find ,G Replace with ,Data!G
Hope this helps

"Rothman" wrote:



"Rothman" wrote:

I have a worksheet which has a bunch of formulas in rows (i.e. data entered
in columns C through F result in formulas results in columns G through
whatever).

All I want to do is to copy a row and move the formulas into another
worksheet while keeping the references to the original worksheet intact. So:

=COUNTIF(G$3:G301,G301)

becomes...

=COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.

I'm copying from row 300 in the original to Row 2, to make things a little
more complicated, but isn't there a way to do this?

Thanks in advance!

Any takers? This would be a big help!

Yes. I've found a couple of workarounds for what I need to do. Because I
really only need the formula results for the hypothetical data I'm entering
(to compare it to "real" data as it comes in), I'm just pumping hypothetical
data into my real data worksheet and then copying the values over to a new
worksheet (and reverting the real data to...real data). What makes it easy
for me is that my formulas won't change, so perhaps someone else who has that
need will need to solve that mystery.

Unfortunately, although it did cause me to have an "Eureka!" moment, your
fix would have been somewhat tedious; the example I gave was the simplest
formula I had. I would have had to replace a ton of values (G through DA; G$
through $DA) for that to have worked, I think.

Seems to me all solutions to this problem would have been tedious, though,
if my formulas were to change around in the future.

"Ron@Buy" wrote:

Did you ever sort your problem?
One solution is to copy the formulas in their entirety into a new worksheet
then use Data Replace i.e. Find G$ Replace with Data!G$ - then a
second Find ,G Replace with ,Data!G
Hope this helps

"Rothman" wrote:



"Rothman" wrote:

I have a worksheet which has a bunch of formulas in rows (i.e. data entered
in columns C through F result in formulas results in columns G through
whatever).

All I want to do is to copy a row and move the formulas into another
worksheet while keeping the references to the original worksheet intact. So:

=COUNTIF(G$3:G301,G301)

becomes...

=COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.

I'm copying from row 300 in the original to Row 2, to make things a little
more complicated, but isn't there a way to do this?

Thanks in advance!

Any takers? This would be a big help!