Solved

# Problem with Offset, Row and Column combo

Posted on 2014-02-28
254 Views
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
Question by:Frank Freese
• 7
• 5
• 3

LVL 19

Assisted Solution

regmigrant earned 200 total points
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

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

LVL 43

Accepted Solution

Saqib Husain, Syed earned 300 total points
=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

Author Comment

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

Did you try my formula?
0

Author Comment

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

thank you both...
0

LVL 43

Expert Comment

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

good tip...and thank you
0

LVL 19

Expert Comment

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

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

LVL 19

Expert Comment

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

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

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

Author Comment

thanks
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateâ€¦
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.