Hi,
I am not sure if this is possible in Excel but what I want to do here is reference cell and skip a fix amount cell... an example would be easier...
If I have A1 = 5, A5 = 10, A9 = 15, now in cell B1, i want it to = to A1, in cell B2, i want to = to A5, cell B3, I want to = to A9, so every single reference is exactly in a 4 cell interval. How could this be done? Please help!
Thanks
Dave
tRY:
=INDEX(A:A,4*(ROWS($A$1:A1)-1)+1) copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
WOW IT WORKED! Awesome! But can you explain a little bit on the index formula (like what exactly is doing?)
Thanks
Dave
The Index() function identifies an array and pulls the item in the row/column numbers identified in the 2 and/or 3rd arguments....See Index function in Excel Help for more...
The row number (2nd Argument) in this case is defined by the formula: 4*(ROWS($A$1:A1)-1)+1. When copied down, the Rows() is incremented by 1 and multiplied by 4 then one is subtracted from that to give the actual vertical position in the array A:A that is being referenced.
Thanks for your explaining! I got it now, what i actually need is reference from both ROW and column, but i guess that means i only need to use both part of the formula, i will try it out myself.
Thanks!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks