ADDRESS formula, sheet no

[nsv]

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

 

[Guest]

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

 

[Ken Johnson]

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

 

[nsv]

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

NSV