#N/A error

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

I have a sheet which has a number of sumproduct calculations form data on
sheet1.

The columns on sheet1 are named and the names are used in the calculations.

The problem is that users do not drag down correctly and this will cause the
number of rows to be different, therefore causing the #N/A error (for
example list = A2:A555 but after dragging incorrectly can be A2:A559)

Is there any way to check all named columns on sheet1 against column C (name
= age and is hidden so cannot be wrong) and amend them if they do not match.

Hope I have explained my problem.

Thanks in advance.

Cheryl
 
If the references are static then you can define them like

Name: List
RefersTo: =Indirect("Sheet1!A2:A555")

and they won't be affected by changes to sheet1 since they reference is
actually a string.
 
Tom

Many thanks, just what I'm after. The names are entered when data is
imported into the file using the following macro, how can it be amended so
that INDIRECT will be displayed in the Refers To box or will something have
to be added?

Sub Insertnames()
Range("B2:B" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "list"
Range("D2:D" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "age"
Range("E2:E" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "lus"
Range("F2:F" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name =
"checked"
Range("H2:H" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "sex"
Range("I2:I" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "dob"
Range("J2:J" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "on"
Range("K2:K" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "off"
Range("L2:L" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "dead"
Range("N2:N" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "calf"
Range("O2:O" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "CorPE"
Range("P2:P" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discr1"
Range("Q2:Q" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discr2"
Range("R2:R" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discr3"
Range("S2:S" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discr4"
Range("T2:T" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discr5"
Range("P2:T" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "discrs"
Range("U2:U" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name =
"livedisc"
Range("X2:X" & Range("B65536").End(xlUp).Offset(200, 0).Row).Name = "SPS"
End Sub

Cheryl
 
You could use something like this:

Sub Insertnames()
Dim i As Long
Dim j As Long
Dim rng As Range
Dim rng1 As Range
varr = Array("list", _
"Dum", _
"age", _
"lus", _
"checked", _
"Dum", _
"sex", _
"dob", _
"on", _
"off", _
"dead", _
"Dum", _
"calf", _
"CorPE", _
"discr1", _
"discr2", _
"discr3", _
"discr4", _
"discr5", _
"livedisc", _
"Dum", _
"Dum", _
"SPS")
Set rng = Cells(Rows.Count, 2).End(xlUp).Offset(200, 0)
Set rng1 = Range(Range("B2"), rng)
j = 0
For i = LBound(varr) To UBound(varr)
If varr(i) <> "Dum" Then

ActiveWorkbook.Names.Add _
Name:=varr(i), _
RefersTo:="=INDIRECT(""" & _
"'" & ActiveSheet.Name & "'!" & _
rng1.Offset(, j).Address _
& """)"
End If
j = j + 1
Next
ActiveWorkbook.Names.Add _
Name:="discrs", _
RefersTo:="=INDIRECT(""" & _
"'" & ActiveSheet.Name & "'!" & _
rng1.Offset(0, 14).Resize(, 5) _
.Address & """)"
End Sub

I put in the name "Dum" for columns that you skipped. These names are not
created - just used to skip over that column so I don't need a lot of extra
coding. The multicolumn name "discrs" is done at the end.
 
Back
Top