| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Cells accross Workbooks using Column Names | Excel Discussion (Misc queries) | |||
| COUNTIF: unique names and values accross several columns. | Excel Worksheet Functions | |||
| Vlookup accross multiple worksheets | Excel Worksheet Functions | |||
| Countif accross multiple questions. | Excel Discussion (Misc queries) | |||
| Sumproduct accross multiple sheets | Excel Discussion (Misc queries) | |||
Linear Mode
