How to add formula calling up current workbook when sheet copied

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
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
Click to expand...
 
Hi Dave,

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


Dave Peterson said:
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.
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
Click to expand...
Click to expand...
 
The second suggestion seems pretty easy.
Hi Dave,

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

Dave Peterson said:
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.
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
The second suggestion seems pretty easy.
Hi Dave,

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

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
Maybe you could use some sort of macro that would make it more automatic.


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 said:
The second suggestion seems pretty easy.
Hi Dave,

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave

Will give it a go

--
Smudge


Dave Peterson said:
Maybe you could use some sort of macro that would make it more automatic.


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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top