Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with Offset, Row and Column combo

Posted on 2014-02-28
15
Medium Priority
?
276 Views
Last Modified: 2014-03-02
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
0
Comment
Question by:Frank Freese
  • 7
  • 5
  • 3
15 Comments
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 800 total points
ID: 39896442
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
 

Author Comment

by:Frank Freese
ID: 39896474
With your change I now fill J3:L3 but only that range?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1200 total points
ID: 39896529
=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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Frank Freese
ID: 39896539
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39896565
Did you try my formula?
0
 

Author Comment

by:Frank Freese
ID: 39896588
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
 

Author Closing Comment

by:Frank Freese
ID: 39896590
thank you both...
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39896600
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
 

Author Comment

by:Frank Freese
ID: 39896604
good tip...and thank you
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39896849
interesting result and glad you got your answer but my original post worked fine - see attached
Copy-of-FilterEveryOtherRow.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39896887
Reg, that does not work if you create another table further ahead.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39896971
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
 

Author Comment

by:Frank Freese
ID: 39897304
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39897445
The range is probably formatted as text. Change it to general format.
0
 

Author Comment

by:Frank Freese
ID: 39898935
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question