Hello,
I am trying to reference cells within the same worksheet. I am referencing the cells in R10:R144. The data I need is in B2493:B9400. The values I am trying to extract are in increments of 47. When I do the cell refence in R10, R11, R12 and try to autofill it does not increment by 47.
i.e.
R10 =B2493
R11 =B2540
R12 =B2587
But R12 =B2496
Excel did not pick up on my increment pattern of 47. I have tried using the index function, however I am having difficulties with it. Any solutions?
Excel doesn't pick up the patterns of cell references, all it does is increment/decrement the row/column as you drag.
What did you try for INDEX?
This appeared to work for me, though I'm sure there's a better formula
Formula:
Please Login or Register to view this content.
If posting code please use code tags, see here.
The code you suggested gave some values but they weren't correct for some reason. Could you explain what the (ROW()-1)*47 does?
Please Login or Register to view this content.
Why do you have ROW($B$2822) in the formula?
(ROW()-1)*47 in the formula I posted.
If you mean (ROW()-10)*47 it increments the row reference for the INDEX formula, the 10 is there because we are starting in row 10, ie R10.
After playing with it some I got it too work. That was a starting cell and I was trying to increment 47 from thereon. You have saved me hours of time. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks