Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
Hello..thanks for your help in this..
I have a workbook with many tabs (worksheets). The last is a spreadsheet (let's call it Tab#10) with each row drawing data from different places in each of the worksheets...so that row 1 will draw data from tab#1, row two from tab #2...etc. I would like to manually link the cells in row one (in Tab#10) to tab#1, and then drag down to fill the rest of the rows (in Tab#10). I was then going to use the "search & replace" feature to replace the reference to tab#1 by the correct tab name for row 2 onwards. QUESTION: There must be a smarter/quicker/more efficient way of doing this, and I know one of you Excel whizzes knows it!! p.s. I am not a macro expert..but I just copied a macro code from this site for automatically naming tabs and it WORKED - I am SO happy.. so I'm encouraged. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
Try using the INDIRECT function. In tab 10, list the tab names
starting in cell A1 through A9. In cell B1 enter the formula: =INDIRECT(A1&"!a1"). Copy this down and you'll pull in the value of cell A1 from each of the named tabs. Change the references to suit your sheet layout. Not that the first A1 in the formula refers to the cell on Tab10 that list the first tab name. The second A1 should be changed to whatever cell contains the data your are retrieving. - John www.JohnMichl.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
Stilla
If Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet. =INDIRECT("'Sheet" & (ROW() & "'!$A$1") assuming A1 on each sheet is your referenced cell. Drag/copy down column A. If sheets have unique names, enter the sheet names in a column and use this formula. =INDIRECT(B1 & "!$A$1") If spaces in sheetnames use =INDIRECT("'" & B1 & "'!$A$1") Assume sheet names were in B1:B10 you would copy down 10 cells. Your ranges may differ, so adjust to suit. Gord Dibben Excel MVP On Tue, 6 Dec 2005 11:10:03 -0800, Stilla wrote: Hello..thanks for your help in this.. I have a workbook with many tabs (worksheets). The last is a spreadsheet (let's call it Tab#10) with each row drawing data from different places in each of the worksheets...so that row 1 will draw data from tab#1, row two from tab #2...etc. I would like to manually link the cells in row one (in Tab#10) to tab#1, and then drag down to fill the rest of the rows (in Tab#10). I was then going to use the "search & replace" feature to replace the reference to tab#1 by the correct tab name for row 2 onwards. QUESTION: There must be a smarter/quicker/more efficient way of doing this, and I know one of you Excel whizzes knows it!! p.s. I am not a macro expert..but I just copied a macro code from this site for automatically naming tabs and it WORKED - I am SO happy.. so I'm encouraged. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
OH MY STARS!! OH GEEEEE WHIIZZZZZ! I'm JUMPING UP AND DOWN!!! I'M
HYPERVENTILATING!!! THIS IS @@@@@W O N D E R F U L@@@@ You just saved me at least half a day's work!!! I'm going to have about a hundred of these files to do! Now I can just create one prototype ! THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU "John Michl" wrote: Try using the INDIRECT function. In tab 10, list the tab names starting in cell A1 through A9. In cell B1 enter the formula: =INDIRECT(A1&"!a1"). Copy this down and you'll pull in the value of cell A1 from each of the named tabs. Change the references to suit your sheet layout. Not that the first A1 in the formula refers to the cell on Tab10 that list the first tab name. The second A1 should be changed to whatever cell contains the data your are retrieving. - John www.JohnMichl.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
Thank you too Gord! You guys are simply terrific!
"Gord Dibben" wrote: Stilla If Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet. =INDIRECT("'Sheet" & (ROW() & "'!$A$1") assuming A1 on each sheet is your referenced cell. Drag/copy down column A. If sheets have unique names, enter the sheet names in a column and use this formula. =INDIRECT(B1 & "!$A$1") If spaces in sheetnames use =INDIRECT("'" & B1 & "'!$A$1") Assume sheet names were in B1:B10 you would copy down 10 cells. Your ranges may differ, so adjust to suit. Gord Dibben Excel MVP On Tue, 6 Dec 2005 11:10:03 -0800, Stilla wrote: Hello..thanks for your help in this.. I have a workbook with many tabs (worksheets). The last is a spreadsheet (let's call it Tab#10) with each row drawing data from different places in each of the worksheets...so that row 1 will draw data from tab#1, row two from tab #2...etc. I would like to manually link the cells in row one (in Tab#10) to tab#1, and then drag down to fill the rest of the rows (in Tab#10). I was then going to use the "search & replace" feature to replace the reference to tab#1 by the correct tab name for row 2 onwards. QUESTION: There must be a smarter/quicker/more efficient way of doing this, and I know one of you Excel whizzes knows it!! p.s. I am not a macro expert..but I just copied a macro code from this site for automatically naming tabs and it WORKED - I am SO happy.. so I'm encouraged. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shortcut for referring to tab names in formulas
Gee, I feel all warm and fuzzy now. Glad I could help.
- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Formulas for counting multiple conditions | Excel Worksheet Functions | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
Hidding Macro names and coding | Excel Discussion (Misc queries) | |||
Sheet names used in formulas | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) |