asked on

Problem with Offset, Row and Column combo

Folks,
In the attached workbook I am using a combination of Offset, Row and Column to filter out every other row.
It works fine until I try to duplicate the formula to the Practice section of the worksheet.
I simply don't see why it is not working in the practice section.
FilterEveryOtherRow.xlsx

SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER

With your change I now fill J3:L3 but only that range?

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER

Saqib,
When I select B2:D8 and enter the following formula
=OFFSET($A$2,(ROW()-2)*COLUMN(),0) and the press <Ctrl+Enter> it works

My problem lies in the second section, name Practice. I select cells J2:L8 and enter the following formula
=OFFSET($I$2,(ROW()-2)*COLUMN()-8,0) and then press <Ctrl+Enter> only J2:L2 are filled

Maybe I can only do this once is a worksheet?

Saqib Husain

Did you try my formula?

Frank Freese

ASKER

I entered your formula but had to change $A$3 to $I$3 for the second section (range J3:L9), but it worked. It really doesn't make sense to me because in the first range of B3:D9 this worked for me:
Select cells B3:B9 and then entered the following formula:
=OFFSET($A$2,(ROW()-2)*COLUMN(),0) and the press <Ctrl+Enter>
The formula offered by: regmigrant
=OFFSET($I$3,(ROW()-2)*(COLUMN()-8),0) worked but only on J3:L3?
Why? I just don't know.
Allow me to split the points 200/300.
Thank you very much

Frank Freese

ASKER

thank you both...

Saqib Husain

Select the formula cell and go to

Formulas > Evaluate formula

and step through the formula and you will see why a formula works or otherwise.

Frank Freese

ASKER

good tip...and thank you

regmigrant

interesting result and glad you got your answer but my original post worked fine - see attached
Copy-of-FilterEveryOtherRow.xlsx

Saqib Husain

Reg, that does not work if you create another table further ahead.

regmigrant

Ok, I missed that requirement in the question and it explains why your formula has the extra column() - to correct the offset as the table moves right, thanks!

Frank Freese

ASKER

Saqib,
I did have a slight problem with your code though. It worked ok on the first three items but later it returned the formula. Just an FYI

Saqib Husain

The range is probably formatted as text. Change it to general format.

Frank Freese

ASKER

thanks