Named Ranges created by search returns

  • Thread starter Thread starter penc
  • Start date Start date
P

penc

I have a workbook that contains general site informaiton
on the first worksheet, followed by specific detailed
information on the next two worksheets.

Using named ranges and hyperlinks to point to them, I have
been able to take the user from one set of data to the
next. This is exactly what I wanted however I now realize
that without dynamic setting of the named ranges, I'm in
for a lot of updating as the second workbook changes.

My question is: Has anybody figured out how to return a
named range dynamically? This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.

Thanks.
 
Penc,

Creating a named range with a RefersTo value of say
=A$1:OFFSET(A$1,COUNTA(A:A)-1,0)
will setup adynamic named range for column A
 
Pls read:
http://www.cpearson.com/excel/newposte.htm

http://www.mvps.org/dmcritchie/excel/posting.htm


Please don't post everywhere and not bother to read the responses
everywhere.
Please stay in the original thread instead of asking the same question every
few minutes.
"Patience is a virtue"


This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.
Click to expand...

POSTED IN MISC.

try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
mystr = ""
With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & c.Offset(, 1) & " "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
'MsgBox mystr
cel.Offset(, 2) = mystr
End With
Next cel
End Sub
 
Good Friday afternoon to you too.
1. The original post was in this newsgroup. I provided
reference for trackability.
2. I was experiencing network problems and was not able
to see the postings until way after and was under the
impression that they had not committed.
3. I read all responses and others.
4. Thanks for the information.
5. Have a good weekend, take some time off from the
control issues.
-----Original Message-----
Pls read:
http://www.cpearson.com/excel/newposte.htm

http://www.mvps.org/dmcritchie/excel/posting.htm


Please don't post everywhere and not bother to read the responses
everywhere.
Please stay in the original thread instead of asking the same question every
few minutes.
"Patience is a virtue"


This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.
Click to expand...

POSTED IN MISC.

try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
mystr = ""
With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & c.Offset(, 1) & " "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
'MsgBox mystr
cel.Offset(, 2) = mystr
End With
Next cel
End Sub


--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)
penc said:
I have a workbook that contains general site informaiton
on the first worksheet, followed by specific detailed
information on the next two worksheets.

Using named ranges and hyperlinks to point to them, I have
been able to take the user from one set of data to the
next. This is exactly what I wanted however I now realize
that without dynamic setting of the named ranges, I'm in
for a lot of updating as the second workbook changes.

My question is: Has anybody figured out how to return a
named range dynamically? This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.

Thanks.
Click to expand...


.
Click to expand...
 
5. Have a good weekend, take some time off from the
control issues.
Click to expand...
Just trying to help you to understand how we do things here. If you don't
want help, that's OK.

--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)
penc said:
Good Friday afternoon to you too.
1. The original post was in this newsgroup. I provided
reference for trackability.
2. I was experiencing network problems and was not able
to see the postings until way after and was under the
impression that they had not committed.
3. I read all responses and others.
4. Thanks for the information.
5. Have a good weekend, take some time off from the
control issues.
-----Original Message-----
Pls read:
http://www.cpearson.com/excel/newposte.htm

http://www.mvps.org/dmcritchie/excel/posting.htm


Please don't post everywhere and not bother to read the responses
everywhere.
Please stay in the original thread instead of asking the same question every
few minutes.
"Patience is a virtue"


This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.
Click to expand...

POSTED IN MISC.

try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
mystr = ""
With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & c.Offset(, 1) & " "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
'MsgBox mystr
cel.Offset(, 2) = mystr
End With
Next cel
End Sub


--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)
penc said:
I have a workbook that contains general site informaiton
on the first worksheet, followed by specific detailed
information on the next two worksheets.

Using named ranges and hyperlinks to point to them, I have
been able to take the user from one set of data to the
next. This is exactly what I wanted however I now realize
that without dynamic setting of the named ranges, I'm in
for a lot of updating as the second workbook changes.

My question is: Has anybody figured out how to return a
named range dynamically? This question comes off my
failure to get the information from a previous post into
working order. See VLOOKUP with multiple rows under my
name.

Thanks.
Click to expand...


.
Click to expand...
Click to expand...
 
Harlan,

A bit of semantics perhaps, but I don't think your version is any easier to
maintain as OFFSET is somewhat obtuse, you need to properly understand it to
use it, so by doing so the maintenance is evened out. However, I do agree
your version is more elegant looking, so I think I will adopt it from now
on - always knew the for just needed prompting.

On a more important note, both of us missed a probably crucial element to
the OP. By using relative references, it might be fine if defined in column
B, but if then used in column C it would refer to a completely different
range. This may be what the OP wanted, but I doubt it. So for his benefit,
this is an anchored dynamic range name (for want of a better phrase)

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
 
Back
Top