How to add formula calling up current workbook when sheet copied
 

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA


--
Smudge

How to add formula calling up current workbook when sheet copied
 

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson

How to add formula calling up current workbook when sheet copi
 

Hi Dave,

So there's no magic way of it doing this without amending the cell each time
I copy over? :-(
--
Smudge


"Dave Peterson" wrote:

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson

How to add formula calling up current workbook when sheet copi
 

The second suggestion seems pretty easy.

Smudge wrote:

Hi Dave,

So there's no magic way of it doing this without amending the cell each time
I copy over? :-(
--
Smudge

"Dave Peterson" wrote:

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson


--

Dave Peterson

How to add formula calling up current workbook when sheet copi
 

Hi Dave,

Didn't mean to sound ungrateful ! I'm using 2nd option between messages :-)
but imagined that there was an automatic way of doing it that I was missing -
learning all the time.

Mant thanks again
--
Smudge


"Dave Peterson" wrote:

The second suggestion seems pretty easy.

Smudge wrote:

Hi Dave,

So there's no magic way of it doing this without amending the cell each time
I copy over? :-(
--
Smudge

"Dave Peterson" wrote:

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson


--

Dave Peterson

How to add formula calling up current workbook when sheet copi
 

Maybe you could use some sort of macro that would make it more automatic.



Smudge wrote:

Hi Dave,

Didn't mean to sound ungrateful ! I'm using 2nd option between messages :-)
but imagined that there was an automatic way of doing it that I was missing -
learning all the time.

Mant thanks again
--
Smudge

"Dave Peterson" wrote:

The second suggestion seems pretty easy.

Smudge wrote:

Hi Dave,

So there's no magic way of it doing this without amending the cell each time
I copy over? :-(
--
Smudge

"Dave Peterson" wrote:

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How to add formula calling up current workbook when sheet copi
 

Thanks Dave

Will give it a go

--
Smudge


"Dave Peterson" wrote:

Maybe you could use some sort of macro that would make it more automatic.



Smudge wrote:

Hi Dave,

Didn't mean to sound ungrateful ! I'm using 2nd option between messages :-)
but imagined that there was an automatic way of doing it that I was missing -
learning all the time.

Mant thanks again
--
Smudge

"Dave Peterson" wrote:

The second suggestion seems pretty easy.

Smudge wrote:

Hi Dave,

So there's no magic way of it doing this without amending the cell each time
I copy over? :-(
--
Smudge

"Dave Peterson" wrote:

You wouldn't need the workbook name [depot 1.xls] in the formula.

I'd use:
$$$$$="Number of Units " & 'dumpers'!h1

Yep, it's text--not a formula.
Copy and paste into the new workbook.
then remove the $$$$$ to change it into a formula.

Or you could copy the formula:
="Number of Units " & 'dumpers'!h1
from the formulabar and paste it into the formulabar.

Smudge wrote:

Hi everyone,

I'm trying to make life easier for me by putting the following into a cell
="Number of Units " & '[Depot 1.xls]dumpers'!H1

This should let me copy this worksheet into a number of workbooks and pick
up the depot number from cell H1 in an existing sheet in each workbook.

How do I do this and make excel look for h1 in the book I'm copying into?

TIA

--
Smudge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson