Super cool named ranges with offset.

**Polymorpher** · 07-31-2014, 11:32 AM

I have named ranges : bd, pl, wh.

Can anyone make a function that calls up multiple cells from those ranges like so:

=FUNCTIONNAME(bd3,pl1,wh5)
That would return the same thing as =INDEX(bd,3)&INDEX(pl,1)&INDEX(wh,5)
(i can guarantee that the ranges would never have numbers in their names)

if you need another separator you could use space or "." or "-" or ";" (space would be awesome)

=FUNCTIONNAME(bd 3,pl 1,wh 5)
=FUNCTIONNAME(bd.3,pl.1,wh.5)
=FUNCTIONNAME(bd-3,pl-1,wh-5)
=FUNCTIONNAME(bd;3,pl;1,wh;5)

THREAD IS SOLVED with two cool solutions:
Anyone who uses named ranges could benefit from these.

EXAMPLE IN ATTACHMENT:

**Bernie Deitrick** · 07-31-2014, 12:35 PM

Create a UDF and pass it the functionname, the range and index values and do it all in VBA. Or are you doing this for a UDF?

**Polymorpher** · 07-31-2014, 01:02 PM

Would love to (: Except the only thing i know about VBA its that it stands for Visual Basic ... Something (;

**Bernie Deitrick** · 07-31-2014, 01:09 PM

What functions are you looking to use it with?

**Polymorpher** · 07-31-2014, 01:26 PM

OFFSET, is probably the only thing ill need for it.
So throwing =OFFSET(cellname,row0,col+1) with =FUNCTIONNAME(bd3,pl1,wh5) returns bd3,pl1 and wh5 offset by 1 column.

=OFFSET(cellname,row0,col+1) is applied identically to any reference I will make with this so, I don't need to offset bd,3 pl,1 and wh,5 individually with different offsets. I suppose that makes it easier.

**Bernie Deitrick** · 07-31-2014, 01:35 PM

Reply with cell addresses:

What is the range of bd?
And what cell would be bd3?
And what cell would =OFFSET(bd3,row0,col+1) be?

**Polymorpher** · 07-31-2014, 03:30 PM

If bd is A12:A53 then bd3 is the third of that range = A14 (a12 is 1, a13 is 2, a14 is 3)

Just like in a standard named range and using =INDEX(bd,3)

OFFSET(BD3,0,1)=B14

Also if
pl 1 = A33 and wh 5 = A176

=FUNCTIONNAME(bd 3,pl 1,wh 5) with that offset would return B14&B33&B176

**Bernie Deitrick** · 07-31-2014, 04:14 PM

This UDF will return a range of the indicated cells - put this into a standard codemodule in your workbook:

Please Login or Register  to view this content.

You can use it like so:

=SUM(MyOffset("bd 3","pl 1","wh 5",1))

**Polymorpher** · 07-31-2014, 05:09 PM

testing that out...

**Polymorpher** · 07-31-2014, 05:28 PM

Its an excellent function that: you should leave it unchanged here, no doubt some more people will find it and use it.

However I'm not calculating anything.

If the offseted:
bd 3="Joe "
pl 1 = "got "
wh 5 = "a dog"

doing MyOffset("bd 3","pl 1","wh 5",1)
should come up with "Joe got a dog."

or the offsetted:
bd 3="1"
pl 1 = "32"
wh 5 = " dogs chased me."

doing MyOffset("bd 3","pl 1","wh 5",1)
should come up with "132 dogs chased me."

Also I'm willing to sacrifice space as a separator if i can lose the quotation marks and do it MyOffset(bd;3,pl;1,wh;5,1)

Be sure to post the new function separately so people can use the old one - its pretty neat summing stuff like that.

**Bernie Deitrick** · 07-31-2014, 05:52 PM

If you have quotes, we can pass as many in one string as needed:

=StrOffset("bd 3 pl 1 wh 5",1)
=StrOffset("bd 3 pl 1 wh 5 bd 12 pl 4 wh 8",1)

Please Login or Register  to view this content.

I should probably re-write the first one too to take the same input string style.... nah

**Polymorpher** · 08-01-2014, 09:07 AM

Its perfect now, one set of quotes is no problem compared to having to quote each of them individually.

... About the first one.
You couldn't handle the additional upstream of karma that would come with rewriting it to go with one set of quotes (: Its just too much for one man (;

**Polymorpher** · 08-01-2014, 09:16 AM

Urf da fak, double posted again. Anyway - solved.

**Bernie Deitrick** · 08-01-2014, 09:21 AM

Oops - wrong place. I moved the text but I can't delete this message, so sorry.

**Bernie Deitrick** · 08-01-2014, 09:27 AM

=SUM(MyOffset("bd 3 pl 1 wh 5",1))

Please Login or Register  to view this content.

**Polymorpher** · 08-01-2014, 09:40 AM

For future reference of people like me stumbling around...

You can have "bd 3 pl 1 wh 5" in another cell say B5

and point to it this way

=StrOffset(B5,1)

Totally awesome...

**Polymorpher** · 08-02-2014, 09:11 AM

Also keep in mind that the offset function doesn't seem to refresh content upon changes. You can do that by re-inserting the formula with copy paste.

Super cool named ranges with offset.

LinkBack

Thread Tools

Rate This Thread

Display

Super cool named ranges with offset.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Looking for a small tweak on the use of named ranges.

Re: Super cool named ranges with offset.

Re: Super cool named ranges with offset.

Thread Information

Users Browsing this Thread

Similar Threads

Define only 2 named ranges from a list of named ranges...

Looping Mutliple Named Resized Ranges in next empty row below another named range

Can someone tweak a small vba code for me?

[SOLVED] Determining if the value of a cell can be a named range, then assigning named ranges after

Named ranges-is there a an easy way to reference a single value in a named range?

Bookmarks

Bookmarks

Posting Permissions