Calculating Row reference within Cell Reference

[PahiatuaBob]

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.
=VLOOKUP(3,$A$7:$D$628,2,FALSE)
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?
Thanks in advance for your assistance
Bob

 

[id107]

try: =VLOOKUP(3,INDIRECT("$A$"&(7*(ROW()-2))&":$D$628"),2,FALSE)

 

[T. Valko]

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.
=VLOOKUP(3,$A$7:$D$628,2,FALSE)
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?
Thanks in advance for your assistance
Bob

If you have a column of dates then all you need to do is look for the Monday dates.

Let's assume this is your data:

Book1
	A	B	C
1	1/3/2011	Mon	Data1
2	1/4/2011	Tue	Data2
3	1/5/2011	Wed	Data3
4	1/10/2011	Mon	Data4
5	1/11/2011	Tue	Data5
6	1/12/2011	Wed	Data6
7	1/17/2011	Mon	Data7
8	1/18/2011	Tue	Data8
9	1/19/2011	Wed	Data9
10	1/24/2011	Mon	Data10
11	1/25/2011	Tue	Data11
12	1/26/2011	Wed	Data12
Sheet1

You want to extract all the items from column C that correspond to Monday dates in column A.

In the formulas I use the following named ranges:

• Data
• Refers to: =$C:$C
• Dates
• Refers to: =$A$1:$A$12

Enter this formula in E1. This will return the count of Monday dates.

=SUMPRODUCT(--(WEEKDAY(Dates)=2))

Enter this array formula** in F1. This will extract the data from column C that corresponds to the Monday dates.

=IF(ROWS(F$1:F1)>E$1,"",INDEX(Data,SMALL(IF(WEEKDAY(Dates)=2,ROW(Dates)),ROWS(F$1:F1))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down until you get blanks.

 

[PahiatuaBob]

Thanks Biff, and id107, I'll try those...

 

[PahiatuaBob]

Craig/if107, thanks, that does exactly what I want, Had looked at INDIRECT, but didn't know how to use it properly...

Biff/T Valko, thanks for your reply, I haven't tried this solution, but will print it out and add it to my folder for future use/investigation.

Gentlemen, thanks, I appreciate the efforts you have made to assist me.
Bob