excel dynamic range - showing extra row issue

[shido]

Hi All,

I am using following formula to show dynamic range but for some reason, when i click on the Name ranges, it is showing extra two row as it can be seen in the picture below.

Wondering is there anything i am missing in this formula?

Please Login or Register  to view this content.

dynamic range.jpg

[FDibbins]

Hard to say, looking at a pic - you dont show row numbers or column letters.

Keep in mind that with OFFSET, you go dow X rows below the "starting cell", to you need to subtract (at least) 1

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[TMS]

Well, you've got at least one non-blank header row so that's going to skew your COUNTA.

Regards, TMS

[shido]

Hi All,

Please see attached sample spreadsheet for your reference

Should you require anything else, please let me know.

Kind Regards,

Offset.xlsx

[TMS]

Personally, I'd use INDEX rather than OFFSET as OFFSET is volatile and INDEX isn't. I also find it easier to understand the relationship with what I'm counting.

Please Login or Register  to view this content.

It's not a great idea, IMO, to have blank rows just so it looks pretty. It probably shouldn't be visible to the general public so it doesn't matter. The two non-blank cells above the list are what skews your OFFSET/COUNTA (as I suggested previously). And the two blank cells need to be catered for in the INDEX/COUNTA ... hence the +2.

If you just want to fix your DNR, use:

Please Login or Register  to view this content.

As I say, remove the blanks and you don't need to compensate in the INDEX/COUNTA. However, you will still need to include the -2 in in the OFFSET/COUNTA

Regards, TMS

[shido]

Hi TMS,

ITs really bit tricky and volatile.

some column showing extra two row and some column showing one extra row. If i add -2 or -1, its loose the purpose of actual formula.

Thanks for the shortcut

Regards

[TMS]

Not sure I understand what you are saying. Each DNR will depend on how many populated rows there are in the column.

You probably need to post a more detailed sample workbook if you need further clarification.

See the example.

Regards, TMS

[shido]

Hi TMS,

Its okay, i can use examples given in your workbook. It serve my need.

Thanks for your time and effort.

Kind Regards,

[TMS]

You're welcome. Thanks for the rep.