Column Jumping Formula

SeaStar

New Member
Joined
Sep 15, 2006
Messages
14
Hi all,
looking for an answer to what may be a very simple question. I am trying to pick up formulas from every third column. Is there a way to do this ie, so that that i can just copy the formula across a whole spreadsheet rather than sourcing each column?

Appreciate any help i can get,
:biggrin:
Thanks
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, SeaStar
Welcome to the Board !!!!!

you didn't tell exactly what you want to do
take a look at the functions ROW (perhaps COLUMN) and INDEX

you can experiment with this

  A  B  C  D  E  F  G  
1 1  2  3  4  5  6  7  
2                      
3                      
4 4  4                 
5 7  7                 
6 0  0                 
7                      
8 1  4  7              
9 1  4  7              

Blad1

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
A4:A6 =INDEX($1:$1,(ROW()-3) * 3+1)
A8:C8 =INDEX($1:$1,(COLUMN()-1) * 3+1)
A9:C9 =INDEX($1:$1,(COLUMN()-COLUMN($A$9)) * 3+1)
B4:B6 =INDEX($1:$1,(ROW()-ROW($B$3)) * 3+1)

[Table-It] version 06 by Erik Van Geit

a4:a6 is simple formula
a8:c8 is robust against inserting rows (values will be "stable")

kind regards,
Erik
 

SeaStar

New Member
Joined
Sep 15, 2006
Messages
14
Formula Jumping

Sorry, as im new, should be have explained more.

13959 48% 10320 44% 8627 43%

Above shows a figure for April, with a %, then the same for May and the same for June, I want, on another sheet, to only pick up the % without going and actually sourcing the data, so in my new sheet i will have Apri, 48%, May 44% and June 53% without the figures and just be able to copy this formula to the end of the sheet.
At present if i just copy the formula on my new sheet, it would pick up Apirl figure, then the % then May figure and so on........
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
and how is the layout ?

did you experiment with the sample I gave you ?
1. forget for a moment your own project
2. reconstruct the example
3. play with the data
4. try to put in your data
5. time to get back to your own project

if you want to display a sample, you can use colo's HTMLmaker (link below) or Table-It (see my signature)

greetings from Belgium,
Erik
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,381
Maybe...

Assuming that on Sheet1, A2:F2 contains the data, try...

On Sheet2, A2 copied across:

=INDEX(Sheet1!$A2:$F2,COLUMNS($A2:A2)*2)

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,095
Messages
5,545,923
Members
410,713
Latest member
TaremyLunsil
Top