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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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 ?
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

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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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

Rgonzo1971Commented:
Don't know why I went into overdrive mode: Simpler is better ;-)
AL_XResearchAuthor Commented:
This was the only working solution and the simplest.
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.