Link to same cell in different sheets

[MildJoe]

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.

 

[Shane Devenshire]

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]

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?

 

[T. Valko]

=INDIRECT(A15&"!B4")

Try it like this:

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

For clarity:

" ' " & A15 & " ' ! B4 "

 

[David Biddulph]

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

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?