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

[Paul]

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.

 

[royUK]

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

 

[Jarek Kujawa]

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

 

[Paul]

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

 

[Jarek Kujawa]

sorry, should be:

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

 

[Paul]

excuse my ignorance is this within a macro?

 

[Pete_UK]

Put this formula in B1 of accounts sheet:

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

then copy down to B40.

Hope this helps.

Pete

 

[royUK]

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

'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/flashfiles/pivot1.html

 

[Paul]

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.

 

[Pete_UK]

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

 

[Paul]

Roy

thanks i will, some xmas homework for me.