Referncing a Cell using a formula

**martin ridley** · 08-24-2007, 11:29 AM

Hi,

I want to be able to enter a formula that will reference a cell based on the cell reference of another cell, i.e. every 7th cell contains the value I require, i want to show these in consecutive cells in a seperate table without directly referencing each of the cell individually, so the result will be

b20 = b2
c20 = I2
d20 = P2
e20 = w2

and so on and so forth......

Thanks,

Martin

**NBVC** · 08-24-2007, 11:36 AM

Try:

=INDEX($B$2:$W$2,7*(COLUMNS($A$1:A1)-1)+1)

Where $B$2:$W$2 is your entire range to pull from...adjust as necessary and then copy formula across to extract every 7th value.

**martin ridley** · 09-17-2007, 10:51 AM

Hi - I have been trying for a couple of weeks to work this out, but I don't understand the forumla well enough to make this adjustment.

Isn't there a simple way to reference a cell 7-columns to the right of the cell that is currently being referenced by an adjacent cell.

Maybe this is the simple way and I just haven't worked it out. Thanks for your help so far.

Martin

**NBVC** · 09-17-2007, 10:55 AM

In the formula I posted, the only thing you should have to adjust is the B2:W2. This is the entire range that would contain the extractable values.

Perhaps I just misunderstood your initial requirements?

Can you post a zipped Excel sheet showing what you want to have happen?

**martin ridley** · 09-17-2007, 12:05 PM

Hi file attached,

You can see there is a larger table and I am trying to pull the totals for each month into a summary table below.

I think you are pretty much spot on, but I am not sure how to adjust the forumal properly.

Thanks,

Martin