list data from every 5th column in a row.

[jl2509]

Hi

Can anyone assist by providing a Formula to list data from sheet 1 on sheet 2

Sheet 1 data is in Row E and specifically cells E11, K11, Q11 etc across the row

I need this data to be shown on sheet 2 cell B3, C3, D3, E3, F3, etc

This also need to be dynamic as data on sheet 1 changes.

Thoughts?

 

[AlanY]

try this on B3, and copy across

=INDEX(Sheet1!11:11,6*(COLUMN(C1)-COLUMN($B$1))-1)

 

[jl2509]

Hi many rthanks for the help here.

I am getting a strange response from this.
When I hit enter, I am presented with a screen to update values: Sheet1?

Thanks

 

[jl2509]

Sorry, sorted it, I had changed the sheet name. doh

Many thanks and a great solution.

 

[Sanjeev1976]

Try this in B3 and copied across

=OFFSET(Sheet1!A11:Z11,0,(COLUMN()-1)*5-1,,1)

Modify the range to suit your requirement

 

[jl2509]

I wonder if you could advise further.

I need the results to go over a number of columns, say 50 or more, but the data on sheet1 may not currently be that much.
as a result on sheet2, I get Zeros where no data is diplayed.

Can these be hidden if no data exists to display?

Thanks

 

[AlanY]

I wonder if you could advise further.

I need the results to go over a number of columns, say 50 or more, but the data on sheet1 may not currently be that much.
as a result on sheet2, I get Zeros where no data is diplayed.

Can these be hidden if no data exists to display?

Thanks

may be

=if(isblank(INDEX(Sheet1!11:11,6*(COLUMN(C1)-COLUMN($B$1))-1)),"",INDEX(Sheet1!11:11,6*(COLUMN(C1)-COLUMN($B$1))-1))

 

[jl2509]

Nice one

Much appreciated

 

[AlanY]

you're welcome