ADDRESS formula, sheet no

In my "CalcSheet" I need to get the 'L' column from all the other sheets; each sheet in its proper column. For this I have the following formulae:
CalcSheet!A1: =INDIRECT(ADDRESS(ROW(),12,,,"DME magnetic coil"))
CalcSheet!B1: =INDIRECT(ADDRESS(ROW(),12,,,"Paterson formula"))
etc.

It is quite impractical to have to refer to the names of the different sheets as this easily leads to spelling errors and it is very time consuming to tap.
Do the sheets have some kind of number, which can be used for reference in stead of their name?


NSV

You could always scroll over column 'L' and use the REPLACE
It would be repace
CalcSheet!
OtherSheet!
then you can if you scrolled over and highlighted the cells that need
replaced choose REPLACE ALL
or you can do them individually by FIND NEXT > REPLACE
this will go through the entire sheet to find the criteria whereas if you
scroll over the array you need changed, it will only look and replace within
those cells.
HTH


"nsv" wrote:

>
> In my "CalcSheet" I need to get the 'L' column from all the other
> sheets; each sheet in its proper column. For this I have the following
> formulae:
> CalcSheet!A1: =INDIRECT(ADDRESS(ROW(),12,,,"DME magnetic coil"))
> CalcSheet!B1: =INDIRECT(ADDRESS(ROW(),12,,,"Paterson formula"))
> etc.
>
> It is quite impractical to have to refer to the names of the different
> sheets as this easily leads to spelling errors and it is very time
> consuming to tap.
> Do the sheets have some kind of number, which can be used for reference
> in stead of their name?
>
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=502400
>
>

Hi NSV,
I can't see a way around this problem using worksheet functions.
The following macro copies column L of every other worksheet into your
CalcSheet starting in column A. If you go to the VBA Editor (Alt + F11
gets you there quickly) then go Insert>Module and paste the code into
that blank Module. Then back to the worksheets (Alt + F11 again) where
the code can be run by going Tools>Macro>Macros... (GatherLs should
already be selected) then click run.
Try it out out on a backup copy of your data first.

Public Sub GatherLs()
Application.ScreenUpdating = False
Dim I As Long
Dim Sht As Object
Dim rngColumnL As Range
Dim CalcSheet As Object
Set CalcSheet = Sheets("CalcSheet")
On Error Resume Next
For Each Sht In Sheets
If Sht.Name <> "CalcSheet" Then
I = I + 1
Set rngColumnL = Sht.Range("L:L")
rngColumnL.Copy Destination:=CalcSheet.Cells(1, I)
End If
Next Sht
On Error GoTo 0
End Sub

Ken Johnson

I was hoping macros weren't necessary, but OK - thanks for the help.

NSV