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
Frank FreeseAsked:
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.

regmigrantCommented:
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)
0
Frank FreeseAuthor Commented:
With your change I now fill J3:L3 but only that range?
0
Saqib Husain, SyedEngineerCommented:
=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
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
OWASP: Forgery and Phishing

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

Frank FreeseAuthor 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?
0
Saqib Husain, SyedEngineerCommented:
Did you try my formula?
0
Frank FreeseAuthor 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
0
Frank FreeseAuthor Commented:
thank you both...
0
Saqib Husain, SyedEngineerCommented:
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.
0
Frank FreeseAuthor Commented:
good tip...and thank you
0
regmigrantCommented:
interesting result and glad you got your answer but my original post worked fine - see attached
Copy-of-FilterEveryOtherRow.xlsx
0
Saqib Husain, SyedEngineerCommented:
Reg, that does not work if you create another table further ahead.
0
regmigrantCommented:
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!
0
Frank FreeseAuthor 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
0
Saqib Husain, SyedEngineerCommented:
The range is probably formatted as text. Change it to general format.
0
Frank FreeseAuthor Commented:
thanks
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.