Excel: Worksheet formula equivalent of VBA 'EntireRow' function

Situation

I have a set of multiple rows (1 to 10) and columns (A to G) which is has the named range 'AllData'
In one column I have a formula that gets the address of the range for all cells in column E for all rows BEFORE each row. The formula is below
=Address(Row(AllData),Column(AllData)) & ":" & Address(Row()-1,Column(AllDAta))

Open in new window

I then use 'Indirect' to convert this address into a range object for use in my functions e.g. "Indirect(E6)" will return the range reference 'E1:E5'
Whilst this is fine for most formulas one needs to return same range of rows by from column 'B' - to give 'B1:B5'

Question

I could of course use another 'helper' column to give me the 'B rows until now' range but I don't want to do that (for various reasons).

In VBA I would use the 'EntireRow' property of the range object for 'E1:F5' to give a range object of '1:5' and then I could use this to, via an intersection, to create a new range object 'B1:B5'

How can I achieve the same 'EntireRow' functionality in a worksheet function ?

Now of course I could use 'Offset' but I want a formula that is dynamic and does not have any hard-coded values (like the column offset number would be) and that is why I used named ranges. Named ranges guarantee an absolute range and can be easily extended as needed.
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

=OFFSET(INDIRECT(ADDRESS(ROW(AllData),COLUMN(AllData)) & ":" & ADDRESS(ROW()-1,COLUMN(AllData))),0,-COLUMN(AllData)+1,ROWS(INDIRECT(ADDRESS(ROW(AllData),COLUMN(AllData)) & ":" & ADDRESS(ROW()-1,COLUMN(AllData)))),16384)

Open in new window

Regards
0
AL_XResearchAuthor Commented:
Rgonzo1971: Can you please add some comment & step-by-step explanation of that formula since it doesn't appear to make sense to me and when I tested it errors?

What are you actually trying to achieve in using Offset ?
0
AL_XResearchAuthor Commented:
After a step back from the issue and a little lateral thinking I came up with the below formula which does give the range address for the row on which 'AllData' resides:
=ADDRESS(ROW(AllData),1)&":"&ADDRESS(ROW(AllData),COLUMNS(1:1))

Open in new window

You can then use this to, for example. add up all values on that row as follows:
=SUM(INDIRECT(ADDRESS(ROW(AllData),1)&":"&ADDRESS(ROW(AllData),COLUMNS(1:1))))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

AL_XResearchAuthor Commented:
The following formula will get the entire row address of all rows covered by 'AllData':
=ADDRESS(ROW(AllData),1)&":"&ADDRESS(ROW(AllData)+ROWS(AllData)-1,COLUMNS(1:1))

Open in new window


and the following will sum up all values in those rows:
=SUM(INDIRECT(ADDRESS(ROW(AllData),1)&":"&ADDRESS(ROW(AllData)+ROWS(AllData)-1,COLUMNS(1:1))))

Open in new window

0
Rgonzo1971Commented:
Don't know why I went into overdrive mode: Simpler is better ;-)
0
AL_XResearchAuthor Commented:
This was the only working solution and the simplest.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.