Link cells to worksheets
 

A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?

Link cells to worksheets
 

YOu can include workbook name in the reference of an address for a cell.
One solution is that, put "=" in B, C, D and then click the required cell
from the workbook you need. In this solution you should not change the
filename or move it to another folder.
This solution doesn't use the name of workbook stored in A.
HTH
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Sparky13" wrote:

A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?

Link cells to worksheets
 

Yes, I could use the"=" but that will require I keep going back and forth
between the worksheets. I need a template to copy to many workbooks all with
differant tab names. If I can just type in the tab name it will be faster and
more virsatal

"Khoshravan" wrote:

YOu can include workbook name in the reference of an address for a cell.
One solution is that, put "=" in B, C, D and then click the required cell
from the workbook you need. In this solution you should not change the
filename or move it to another folder.
This solution doesn't use the name of workbook stored in A.
HTH
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Sparky13" wrote:

A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?

Link cells to worksheets
 

Hi Sparky,

if my understanding is ok use indirect and address to do it

=indirect(address(LINE_NUM,COLUMN_NUM,1,1,A1))

when you change the name of the sheet on A1 the cells will be actualized.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Sparky13" escreveu:

Yes, I could use the"=" but that will require I keep going back and forth
between the worksheets. I need a template to copy to many workbooks all with
differant tab names. If I can just type in the tab name it will be faster and
more virsatal

"Khoshravan" wrote:

YOu can include workbook name in the reference of an address for a cell.
One solution is that, put "=" in B, C, D and then click the required cell
from the workbook you need. In this solution you should not change the
filename or move it to another folder.
This solution doesn't use the name of workbook stored in A.
HTH
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Sparky13" wrote:

A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?

Link cells to worksheets
 

In B1:

=INDIRECT("'"&$A$1&"'!"&ADDRESS(1,COLUMN()))

HTH
Kostis Vezerides

Sparky13 wrote:
A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?

Link cells to worksheets
 

Hi,

If B1:D1 get their values from the corresponging cells in the target
sheet,

=INDIRECT("'"&$A$1&"'!"&ADDRESS(1,COLUMN()))

If you want a specific cell in B1 (say K24), then:

=INDIRECT("'"&$A$1&"'!K24")

HTH
Kostis Vezerides

Link cells to worksheets
 

Maybe a little further explaintion:
The workbook contains many worksheets (Tabs) such as Company A, Company B,
etc. Each worksheet will always contain material data in cell Z1 (for
example). Normally the formula for B1 would be ='Company A'!Z1. What I need
is a template that I can copy to any workbook, that will retrive the data
from Z1 for what ever name I type in A1. Basically I need a formula that will
substitute whatever is typed into A1 for "Company A" in the above formula.

"vezerid" wrote:

In B1:

=INDIRECT("'"&$A$1&"'!"&ADDRESS(1,COLUMN()))

HTH
Kostis Vezerides

Sparky13 wrote:
A B C D
1 [Worksheet / Tab name] Material Labor Overhead
2

I need to make a template which I can copy into differant workbooks. In the
above example I would like to input the worksheet (or tab) name into A1 and
have excel fill in the information for B, C, & D. The worksheet (tab) names
will always be different but the cells in the worksheets which contain the
data for B,C,D will always be the same. I could have as many as 50+
worksheets in the workbook. I know I may get errors in B,C,D if A is blank
but I can deal with that.

Question: What is the formula for cells B,C,D to:
1)look at the name in A
2)find that worksheet and
3)copy the date from that worksheet to cells B, C, D?