Calculating Row within a Cell Reference

Microsoft Excel 2003 (full product)
April 9, 2011 at 06:12:36
Specs: Windows XP
Within Excel, I need a way to calculate the row reference part of a Cell reference within the VLOOKUP Function...

What I am trying to do is return the second column, every 7th row within a range of cells. For example, in a list of daily weight measurements, I am trying to extract into another list the weight for every monday....

I am using VLOOKUP which is returning the correct data, but 7 times until it gets to the next Monday and returns that Monday's data 7 times, and so on. By reducing the size of the range of cells queried (by virtually removing the previous 7 rows each time) I can get the following formula to work, but I have to manually edit the row reference (eg $7) in the formula in each instance.


I would like to be able to replace the $7, $14, $21, etc with (7*(ROW()-2)) somehow. This calculation provides the right number for the Row reference, but I don't know how to get it to work WITHIN the cell reference part of the VLOOKUP function.

Does anyone have any ideas please?

See More: Calculating Row within a Cell Reference

April 10, 2011 at 10:03:24
Not completely sure what your trying to accomplish, if you can post an example of your data (Read This First:

I think something using the =INDEX and/or the =INDIRECT function might get you what your looking for.


Report •

April 10, 2011 at 14:56:03
Thanks Mike, I have received the answer I needed elsewhere, it was through the use of the =INDIRECT function that I was able to solve it..
Appreciate your response.


Report •
Related Solutions

Ask Question