How to add formula calling up current workbook when sheet copied

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
 
D

Dave Peterson

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...
 
G

Guest

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...
 
D

Dave Peterson

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...
 
G

Guest

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...
 
D

Dave Peterson

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export just one sheet from a workbook 2
Variations on referencing a cell in another workbook - continued 4
How to Display 12.5 but not 25.0 (drop .0’s) 5
data to summary sheet for a dummy 0
Testing if data already imported 3
how to get a cell to only update when worksheet is opened? 1
Cell values to an array, then array transpose to another workbook 8
Linked cells lose data when updated HELP! 9

Top