Offset formula - Error

[Eric Tsang]

Dear all

Have no idea i tried to define a "dynamic range"with Offset formular but invaid

Appreciate your help


=OFFSET(Sheet1!$A$5,,,COUNTA(Sheet1!$A$5:$A$8))

Also if you can enlight me how to make a range with few column?!

Thanks in advance


See attached file

Eric

[joeu2004]

First, in the attached Excel file, there is a space before the formula "=". That makes the "formula" text, not a bona fide formula. I presume that is not part of the problem; merely your way of displaying the formula. In the future, attach Excel files that demonstrate the original problem. Obviously, we can see the formula(s) by selecting the cell(s).

The formula translates into OFFSET(A5,,,4). That specifies an array of 4 elements, starting with A5. As such, the formula should be array-entered: press ctrl+shift+Enter instead of just Enter. And as a standalone expression (not passed to a function), you would normally select 4 cells and array-enter the formula in order to see the entire array.

If your intent is to select the 4th cell after A5, use OFFSET(Sheet1!$A$5,COUNTA(Sheet1!$A$5:$A$8),0).

But that is a "volatile" formula, which is recalculated every time any cell in the workbook is edited.

Better: INDEX($A$5:$A$9,COUNTA($A$5:$A$9),1).

[protonLeah]

For your dynamic data validation formula, use:

Please Login or Register  to view this content.

or:

Please Login or Register  to view this content.

For Index/match or VLookup with two columns use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Eric Tsang]

Dear all guru

I have no idea why it does not work.
See attached

I want to make a dynamic range for 2 columns and apply this range to the Advance filter - list range
=OFFSET(Sheet1!$A$5,1,0,COUNTA(Sheet1!$A:$A)-1,2)

Can you help me out

Thanks
Eric

[JeteMc]

Looking at the file it would seem that in cell G17 you want to be able to choose a last name and have H17 automatically display the corresponding first name.
However that's not what either the description in post #1 or post #4 states.
I feel as if it would be better if you could describe, or better yet give us an example, of what outcome you expect.
Let us know if you have any questions.