Tweak of syntax on Named Range

C

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Hello all, just need a hand tweaking the syntax on a names range.

Range 1
Rich (BB code): 
=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$C:$C,ROW(LRow)-1)

LRow: Is a named active row for the last row. [say row 10]

This named range only selects ONE Column

How can I get it to select TWO columns [ie C & D] as in the example below does. It does it by the last number 2, goes two columns wide?

Range 2
Rich (BB code): 
=OFFSET(Sheet2!$C$5,0,0,COUNTA(Sheet2!$C:$C),2)

I can't figure out how to express that in the Range 1?

thanks
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Needs a small tweak:

=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)

A note. Although INDEX is a non-volatile function, the expression itself
is volatile, due to the semi-colon between two reference yielding INDEX calls.

Another note. In

=OFFSET(Sheet1!$C$5,0,0,COUNTA(Sheet1!$C:$C),2)

the COUNTA call might yield wrong range specs when the range in question
interspersed with empty cells.
 
Upvote 0
Thanks Aladin, you been very kind and helpful of late,

I open to rephrasing the named range if you think it can be more stable?

I'm trying to get a dynamic range that will expand with any new data row inserted above LRow, but I need to make the range two columns wide to work for this expression, where column C is company names and column D is their address.

=IF(ISERROR(VLOOKUP($C$3,MyData,2,FALSE)),"",VLOOKUP($C$3,MyData,2,FALSE))

Any Ideas how I can make this:

MyData=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)

more workable and make it two columns wide?
 
Upvote 0
Aladin, Sorry for the late reply, the page went down yesterday and as you can see some of the post is missing.....

This is what I wrote.... [if it makes sense]

The reason that I can't limit the range to C5:D10 is that its a dynamic range that will have rows added to it over time. The rows added will always be above LRow, as LRow-1 will be the last row in the range.

Yes one of the reason I am trying to avoid the COUNTA is that there may be empty cells that will cause errors

If I am using this correctly: =OFFSET(INDEX(Sheet2!$C:$C,5),0,0,(Lrow-5)+1,2), seems to select only Rows 2:5 but does do it in Column C:D,

cheers

PS I did try =LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP($C$3,MyData,2,0)))
and that seems a great alternative

POST ACTION I reread your 1st reply about semicolon and made me think... I changed it to
=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)

This select from row 5, column C & D down to LRow-1

Do you think this is ok or a better way to express it. If its ok then thanks for sparking my brain up, it doesn't happen too often!
 
Upvote 0
Thanks Mike, I had a look at the posts.

The CountA is great except I stops once it sees a blank Row, I have to allow that users will leave empty Rows and so am trying to get the range to go down to LRow [which is the last row] and new item will be add above this.

I tried this
=OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C:$C,ROW(LRow)-1),2)

it came close but if you had two or more empty rows it wouldn't select the whole range.

any thoughts on this as a range?
=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)
It works but is it reliable does it slow things down?
 
Upvote 0
cadence said:
Aladin, Sorry for the late reply, the page went down yesterday and as you can see some of the post is missing.....

This is what I wrote.... [if it makes sense]

The reason that I can't limit the range to C5:D10 is that its a dynamic range that will have rows added to it over time. The rows added will always be above LRow, as LRow-1 will be the last row in the range.

Yes one of the reason I am trying to avoid the COUNTA is that there may be empty cells that will cause errors

If I am using this correctly: =OFFSET(INDEX(Sheet2!$C:$C,5),0,0,(Lrow-5)+1,2), seems to select only Rows 2:5 but does do it in Column C:D,

cheers

PS I did try =LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP($C$3,MyData,2,0)))
and that seems a great alternative

POST ACTION I reread your 1st reply about semicolon and made me think... I changed it to
=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)

This select from row 5, column C & D down to LRow-1

Do you think this is ok or a better way to express it. If its ok then thanks for sparking my brain up, it doesn't happen too often!
Click to expand...

I think the board DB is misbehaving...

=OFFSET(INDEX(Sheet2!$C:$C,5),0,0,(Lrow-5)+1,2)

picks up the active range in C:D and is equivalent to the two INDEX construction with a semi-colon in between:

=INDEX(Sheet2!$C:$C,5):INDEX(Sheet2!$D:$D,ROW(LRow)-1)

I'm not sure though which one would be faster (or less volatile)
 
Upvote 0
Hi Caedence,

I'm sure Aladin's post works perfectly, but just to mention my reference was to my post regarding Non-Volatile Named ranges again using Index.
 
Upvote 0
Aladin, sorry this is probably a dumb question but regards:

Aladin Akyurek said:
A note. Although INDEX is a non-volatile function, the expression itself
is volatile, due to the semi-colon between two reference yielding INDEX calls.
Click to expand...

I've thought about this recently and wondered as to why INDIRECT is seeming used in preference to INDEX when looking at scenarios such as the following:

A1 = 1234
result to = 10
(1 + 2 + 3 + 4 -- ie iterate each character)

Common approach:
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

I've wondered why we don't use:
=SUMPRODUCT(--MID(A1,ROW(A1:INDEX(A:A,LEN(A1),0)),1))

Is the above INDEX approach deemed volatile ?
I didn't know about the INDEX:INDEX = Volatile but wasn't sure if the some holds true in the above scenario and figured you would definitely know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
177
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
278
LazyBug
LazyBug
K
  • Question
how to copy above cell values down
Replies
5
Views
123
kamranyd
K
Keebler
  • Solved
clean code
5 6 7
Replies
66
Views
1K
Keebler
Keebler
R
  • Question
Replace cell reference
Replies
1
Views
108
LazyBug
LazyBug

Forum statistics

Threads
1,215,906
Messages
6,127,665
Members
449,397
Latest member
Bastbog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top