Link to same cell in different sheets
 

I have a document withone sheet for every project. I have one sheet where i
summarize important infor from each project-sheets like this:
A1: Project name
B1: Hours worked
C1: Invoice ammount
D1: Invoice date
etc. etc.

What i would like is when i add a new sheet with a new project, i would just
like to Copy/Paste the last row to the next, and it would refer to the next
sheet. When i do this now, it still refers to the same sheet, but adds 1 to
the cell number.
I give the tabs names according to the project name. but is there some
analogy to cell reference. Like: $C$4sheet1 and when i copy this it turns to
$C$4sheet2?

Mybe if i can get the sheet-name in one column, then the data in the other
columns can use this as a reference to the correct sheet?
A1= ProjectName(Name from sheet1-tab) B1=A1!B5 C1=A1!F7
A2=ProjectName(Name from sheet2-tab) B2=A2!B5 C2=A2!F7
etc..
In that way i only have to copy/paste the correct tab-name of the sheet into
the A-column.

Link to same cell in different sheets
 

Hi,

After you insert and name a new sheet, on your summary sheet enter the
project name is say A15 and then copy all the formulas from row 14 down one
row. To get this to work you will need to modify the formulas in columns B:N
or what ever to read

=INDIRECT(A15&"!B4")

This assumes you want the value in B4 and that the sheet name is in A15.


If this helps, please click the Yes button.

cheers,
Shane Devenshire

"MildJoe" wrote:

I have a document withone sheet for every project. I have one sheet where i
summarize important infor from each project-sheets like this:
A1: Project name
B1: Hours worked
C1: Invoice ammount
D1: Invoice date
etc. etc.

What i would like is when i add a new sheet with a new project, i would just
like to Copy/Paste the last row to the next, and it would refer to the next
sheet. When i do this now, it still refers to the same sheet, but adds 1 to
the cell number.
I give the tabs names according to the project name. but is there some
analogy to cell reference. Like: $C$4sheet1 and when i copy this it turns to
$C$4sheet2?

Mybe if i can get the sheet-name in one column, then the data in the other
columns can use this as a reference to the correct sheet?
A1= ProjectName(Name from sheet1-tab) B1=A1!B5 C1=A1!F7
A2=ProjectName(Name from sheet2-tab) B2=A2!B5 C2=A2!F7
etc..
In that way i only have to copy/paste the correct tab-name of the sheet into
the A-column.

Link to same cell in different sheets
 

That was a fast reply!
And thanks, it worked, but it seems its only when the project name is one
word. When i have a space or something like this: "Project 1: Christmas" it
returnes #REF!
Any idea how to fix this?



"Shane Devenshire" wrote:

Hi,

After you insert and name a new sheet, on your summary sheet enter the
project name is say A15 and then copy all the formulas from row 14 down one
row. To get this to work you will need to modify the formulas in columns B:N
or what ever to read

=INDIRECT(A15&"!B4")

This assumes you want the value in B4 and that the sheet name is in A15.


If this helps, please click the Yes button.

cheers,
Shane Devenshire

"MildJoe" wrote:

I have a document withone sheet for every project. I have one sheet where i
summarize important infor from each project-sheets like this:
A1: Project name
B1: Hours worked
C1: Invoice ammount
D1: Invoice date
etc. etc.

What i would like is when i add a new sheet with a new project, i would just
like to Copy/Paste the last row to the next, and it would refer to the next
sheet. When i do this now, it still refers to the same sheet, but adds 1 to
the cell number.
I give the tabs names according to the project name. but is there some
analogy to cell reference. Like: $C$4sheet1 and when i copy this it turns to
$C$4sheet2?

Mybe if i can get the sheet-name in one column, then the data in the other
columns can use this as a reference to the correct sheet?
A1= ProjectName(Name from sheet1-tab) B1=A1!B5 C1=A1!F7
A2=ProjectName(Name from sheet2-tab) B2=A2!B5 C2=A2!F7
etc..
In that way i only have to copy/paste the correct tab-name of the sheet into
the A-column.

Link to same cell in different sheets
 

=INDIRECT(A15&"!B4")

Try it like this:

=INDIRECT("'"&A15&"'!B4")

For clarity:

" ' " & A15 & " ' ! B4 "



--
Biff
Microsoft Excel MVP


"MildJoe" wrote in message
...
That was a fast reply!
And thanks, it worked, but it seems its only when the project name is one
word. When i have a space or something like this: "Project 1: Christmas"
it
returnes #REF!
Any idea how to fix this?



"Shane Devenshire" wrote:

Hi,

After you insert and name a new sheet, on your summary sheet enter the
project name is say A15 and then copy all the formulas from row 14 down
one
row. To get this to work you will need to modify the formulas in columns
B:N
or what ever to read

=INDIRECT(A15&"!B4")

This assumes you want the value in B4 and that the sheet name is in A15.


If this helps, please click the Yes button.

cheers,
Shane Devenshire

"MildJoe" wrote:

I have a document withone sheet for every project. I have one sheet
where i
summarize important infor from each project-sheets like this:
A1: Project name
B1: Hours worked
C1: Invoice ammount
D1: Invoice date
etc. etc.

What i would like is when i add a new sheet with a new project, i would
just
like to Copy/Paste the last row to the next, and it would refer to the
next
sheet. When i do this now, it still refers to the same sheet, but adds
1 to
the cell number.
I give the tabs names according to the project name. but is there some
analogy to cell reference. Like: $C$4sheet1 and when i copy this it
turns to
$C$4sheet2?

Mybe if i can get the sheet-name in one column, then the data in the
other
columns can use this as a reference to the correct sheet?
A1= ProjectName(Name from sheet1-tab) B1=A1!B5 C1=A1!F7
A2=ProjectName(Name from sheet2-tab) B2=A2!B5 C2=A2!F7
etc..
In that way i only have to copy/paste the correct tab-name of the sheet
into
the A-column.

Link to same cell in different sheets
 

=INDIRECT("'"&A15&"'!B4")
--
David Biddulph


"MildJoe" wrote in message
...
That was a fast reply!
And thanks, it worked, but it seems its only when the project name is one
word. When i have a space or something like this: "Project 1: Christmas"
it
returnes #REF!
Any idea how to fix this?



"Shane Devenshire" wrote:

Hi,

After you insert and name a new sheet, on your summary sheet enter the
project name is say A15 and then copy all the formulas from row 14 down
one
row. To get this to work you will need to modify the formulas in columns
B:N
or what ever to read

=INDIRECT(A15&"!B4")

This assumes you want the value in B4 and that the sheet name is in A15.


If this helps, please click the Yes button.

cheers,
Shane Devenshire

"MildJoe" wrote:

I have a document withone sheet for every project. I have one sheet
where i
summarize important infor from each project-sheets like this:
A1: Project name
B1: Hours worked
C1: Invoice ammount
D1: Invoice date
etc. etc.

What i would like is when i add a new sheet with a new project, i would
just
like to Copy/Paste the last row to the next, and it would refer to the
next
sheet. When i do this now, it still refers to the same sheet, but adds
1 to
the cell number.
I give the tabs names according to the project name. but is there some
analogy to cell reference. Like: $C$4sheet1 and when i copy this it
turns to
$C$4sheet2?

Mybe if i can get the sheet-name in one column, then the data in the
other
columns can use this as a reference to the correct sheet?
A1= ProjectName(Name from sheet1-tab) B1=A1!B5 C1=A1!F7
A2=ProjectName(Name from sheet2-tab) B2=A2!B5 C2=A2!F7
etc..
In that way i only have to copy/paste the correct tab-name of the sheet
into
the A-column.