Problem with Offset, Row and Column combo

Frank Freese
Frank Freese used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you corrected the anchor point (A3 moves to I3) but not the column offset - the position is relative to the anchor point so I has to become the new A by deducting the first 8 columns:-

=OFFSET($I$3,(ROW()-2)*(COLUMN()-8),0)

Author

Commented:
With your change I now fill J3:L3 but only that range?
=OFFSET($A$3,(ROW()-ROW($A$3)+1)*(COLUMN()-COLUMN($A$3)+1),0)

You will have to change all three instances of $A$3
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
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?
Did you try my formula?

Author

Commented:
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

Author

Commented:
thank you both...
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.

Author

Commented:
good tip...and thank you
interesting result and glad you got your answer but my original post worked fine - see attached
Copy-of-FilterEveryOtherRow.xlsx
Reg, that does not work if you create another table further ahead.
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!

Author

Commented:
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
The range is probably formatted as text. Change it to general format.

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial