|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?