How do i use the same cell name accross multiple tab names in a wo
 

I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.






--
Thanks

Paul

How do i use the same cell name accross multiple tab names in a wo
 

Why not have one data sheet with a column for Account name and then use
a PivotTable?


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41401

How do i use the same cell name accross multiple tab names in awo
 

Sub d()

For Each ws In ActiveWorkbook.Worksheets
counter = counter + 1
Cells(counter, 2).Formula = "=" & ws.Name & "!A3"
Next ws

End Sub

press ALT+F11, Insert-Module, copypaste this code
then click Run, Run Sub

On 16 Gru, 12:24, Paul wrote:
I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.

--
Thanks

Paul

How do i use the same cell name accross multiple tab names in
 

Thanks Roy, but i am not used to pivottables.

must be an easy way to copy and paste and pick up "client1" tab, and client2
tab etc




--
Thanks

Paul


"royUK" wrote:


Why not have one data sheet with a column for Account name and then use
a PivotTable?


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41401

How do i use the same cell name accross multiple tab names in awo
 

sorry, should be:

Cells(counter, 2).Formula = "="ws.Name & "!A3"

On 16 Gru, 12:37, Jarek Kujawa wrote:
Sub d()

For Each ws In ActiveWorkbook.Worksheets
counter = counter + 1
Cells(counter, 2).Formula = "=" & ws.Name & "!A3"
Next ws

End Sub

press ALT+F11, Insert-Module, copypaste this code
then click Run, Run Sub

On 16 Gru, 12:24, Paul wrote:



I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.

--
Thanks

Paul- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

How do i use the same cell name accross multiple tab names in
 

excuse my ignorance is this within a macro?


--
Thanks

Paul


"Jarek Kujawa" wrote:

sorry, should be:

Cells(counter, 2).Formula = "="ws.Name & "!A3"

On 16 Gru, 12:37, Jarek Kujawa wrote:
Sub d()

For Each ws In ActiveWorkbook.Worksheets
counter = counter + 1
Cells(counter, 2).Formula = "=" & ws.Name & "!A3"
Next ws

End Sub

press ALT+F11, Insert-Module, copypaste this code
then click Run, Run Sub

On 16 Gru, 12:24, Paul wrote:



I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.

--
Thanks

Paul- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

How do i use the same cell name accross multiple tab names in awo
 

Put this formula in B1 of accounts sheet:

=INDIRECT("'"&A1&"'!A3")

then copy down to B40.

Hope this helps.

Pete

On Dec 16, 11:24*am, Paul wrote:
I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.

--
Thanks

Paul

How do i use the same cell name accross multiple tab names in a wo
 

You don't need VBA, use the formula approach from Pete, but I would
definitely recommend reading up on PivotTables

'Pivot Tables' (http://www.excel-it.com/pivot_tables.htm)

'Excel FAQ - Pivot Tables and Pivot Charts'
(http://www.contextures.com/xlfaqPivot.html)

'Macromedia Flash (SWF) Movie Created by Camtasia Studio 2'
(http://www.datapigtechnologies.com/f...es/pivot1.html)


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41401

How do i use the same cell name accross multiple tab names in a wo
 

I WORKED IT OUT

=INDIRECT($A2&"!$B$4")

note cell A2 has the tab names in it, and B4 has the data from that tab.




--
Thanks

Paul


"Paul" wrote:

I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.






--
Thanks

Paul

How do i use the same cell name accross multiple tab names in awo
 

Note that as the reference to B4 is in quotes, and therefore treated
as text, you do not need the $ symbols as it will not change when you
copy it to other cells.

Also, your cell references differ from what you first posted.

Hope this helps.

Pete

On Dec 16, 1:41*pm, Paul wrote:
I WORKED IT OUT

=INDIRECT($A2&"!$B$4")

note cell A2 has the tab names in it, and B4 has the data from that tab.

--
Thanks

Paul



"Paul" wrote:
I am to create a main worksheet as "accounts"
I have 40 "client" tabs (all the same)

i wish to list the same cell ref across all 40 sheets into a verticle listing

eg

"accounts" sheet cell b1 would have "client1" cell a3
"accounts" sheet cell b2 would have "client2" cell a3

i have listed "client1" name in accounts sheet cell a1, and client2 in a2 etc.

--
Thanks

Paul- Hide quoted text -

- Show quoted text -

How do i use the same cell name accross multiple tab names in
 

Roy

thanks i will, some xmas homework for me.



--
Thanks

Paul


"royUK" wrote:


You don't need VBA, use the formula approach from Pete, but I would
definitely recommend reading up on PivotTables

'Pivot Tables' (http://www.excel-it.com/pivot_tables.htm)

'Excel FAQ - Pivot Tables and Pivot Charts'
(http://www.contextures.com/xlfaqPivot.html)

'Macromedia Flash (SWF) Movie Created by Camtasia Studio 2'
(http://www.datapigtechnologies.com/f...es/pivot1.html)


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41401