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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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........
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top