Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Tab references
Hi All
I have a workbook with many tabs in it. The front tab is a register of events with each row as a unique event record. Each event record also has it's own individual tab with more info stored in it [the layout of each individual tab is the same as each other] I need to link cells from the front register to the appropriate individual tab for each event record and visa versa. No problem with this - one by one, but I have many events and tabs to link together. Not only time consuming but prone to errors. Is there a way [there usually is] of copying a cell reference from an individual tab across to the other cells on the register but getting the reference to change to each new individual tabs EG My tab reference on front register cell K55 is ='F06'!$B$37 My tab refernece on front register cell K56 is ='F07'!$B$37 My tab refernece on front register cell K57 is ='F08'!$B$37 How can I change the tab reference quickly ???? Hope this makes sense :) -- Regards Swansea Jack |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Tab references
Have you thought about having a macro that would populate the register worksheet
with formulas -- either on demand (hitting a button on that worksheet) or automatically when activating that sheet? If that's ok, you may want to provide a few more details of what goes where--and how you'd like the formulas populated. Swansea Jack wrote: Hi All I have a workbook with many tabs in it. The front tab is a register of events with each row as a unique event record. Each event record also has it's own individual tab with more info stored in it [the layout of each individual tab is the same as each other] I need to link cells from the front register to the appropriate individual tab for each event record and visa versa. No problem with this - one by one, but I have many events and tabs to link together. Not only time consuming but prone to errors. Is there a way [there usually is] of copying a cell reference from an individual tab across to the other cells on the register but getting the reference to change to each new individual tabs EG My tab reference on front register cell K55 is ='F06'!$B$37 My tab refernece on front register cell K56 is ='F07'!$B$37 My tab refernece on front register cell K57 is ='F08'!$B$37 How can I change the tab reference quickly ???? Hope this makes sense :) -- Regards Swansea Jack -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Tab references
For the example you posted.
In register sheet in K55 enter =INDIRECT("'FO" & (ROW(6:6)) & "'!B37") Copy down to K57.............only the ROW(6:6) will increment. Gord Dibben MS Excel MVP On Thu, 11 Dec 2008 07:46:03 -0800, Swansea Jack wrote: Hi All I have a workbook with many tabs in it. The front tab is a register of events with each row as a unique event record. Each event record also has it's own individual tab with more info stored in it [the layout of each individual tab is the same as each other] I need to link cells from the front register to the appropriate individual tab for each event record and visa versa. No problem with this - one by one, but I have many events and tabs to link together. Not only time consuming but prone to errors. Is there a way [there usually is] of copying a cell reference from an individual tab across to the other cells on the register but getting the reference to change to each new individual tabs EG My tab reference on front register cell K55 is ='F06'!$B$37 My tab refernece on front register cell K56 is ='F07'!$B$37 My tab refernece on front register cell K57 is ='F08'!$B$37 How can I change the tab reference quickly ???? Hope this makes sense :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Tab references
Gord
Thanks ! this works fine ... now my next problem ... How do I copy from the register on the front tab all entries say in cells A1 to A10, into cell A1 on tab F01, then from A2 into cell A1 on tab F02, from cell A3 into A1 on tab F03, etc,etc .... ??? i.e. copying down the rows, into the same cell on each of the tabs ? -- Regards Swansea Jack "Gord Dibben" wrote: For the example you posted. In register sheet in K55 enter =INDIRECT("'FO" & (ROW(6:6)) & "'!B37") Copy down to K57.............only the ROW(6:6) will increment. Gord Dibben MS Excel MVP On Thu, 11 Dec 2008 07:46:03 -0800, Swansea Jack wrote: Hi All I have a workbook with many tabs in it. The front tab is a register of events with each row as a unique event record. Each event record also has it's own individual tab with more info stored in it [the layout of each individual tab is the same as each other] I need to link cells from the front register to the appropriate individual tab for each event record and visa versa. No problem with this - one by one, but I have many events and tabs to link together. Not only time consuming but prone to errors. Is there a way [there usually is] of copying a cell reference from an individual tab across to the other cells on the register but getting the reference to change to each new individual tabs EG My tab reference on front register cell K55 is ='F06'!$B$37 My tab refernece on front register cell K56 is ='F07'!$B$37 My tab refernece on front register cell K57 is ='F08'!$B$37 How can I change the tab reference quickly ???? Hope this makes sense :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Tab references
Assuming Register is first sheet in workbook and you have data in A1:A10
Also assumes you have 10 or fewer FOx sheets More sheets would require larger range in column A of Register sheet Sub Formula_Increment() Dim mytext As String Dim iCtr As Long mytext = "=Register!A" For iCtr = 2 To Worksheets.Count With Worksheets(iCtr).Range("A1") .Value = mytext & iCtr - 1 End With Next iCtr End Sub Gord On Fri, 12 Dec 2008 05:58:04 -0800, Swansea Jack wrote: Gord Thanks ! this works fine ... now my next problem ... How do I copy from the register on the front tab all entries say in cells A1 to A10, into cell A1 on tab F01, then from A2 into cell A1 on tab F02, from cell A3 into A1 on tab F03, etc,etc .... ??? i.e. copying down the rows, into the same cell on each of the tabs ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas with relative references | Excel Discussion (Misc queries) | |||
Copying vertical references horizontally | Excel Discussion (Misc queries) | |||
Copying formulas with mixed references | Excel Worksheet Functions | |||
copying formulas with different references | Excel Discussion (Misc queries) | |||
Copying linked cell references. | Excel Worksheet Functions |