Offset query I think?

[contactjohnpollard]

Hi
I have data on a workbook that is in a row across the columns but missing every other column. I need to return these value to another sheet but filling down the column and skipping the blank columns on the other sheet.

Sheet 1 Data A1, C1, E1, G1

Sheet 2 results values form Sheet 1 go to A1, A2, A3, A4 etc.

Regards

 

[RoryA]

Welcome to the forum.

You could use something like:

=INDEX(Sheet1!$1:$1,1,(ROWS($B$1:$B1)-1)*2+1)

 

[contactjohnpollard]

Rory Hi
Thank you for replying. Can i sent you a sheet for an example as it is a little advanced for me and I am struggling?
Regards John
contactjohnpollard@gmail.com

 

[RoryA]

That formula should work for the locations you described (although if the first sheet is called Data, use that rather than Sheet1). Where is the actual data located?

 

[contactjohnpollard]

Sheet 1 Row 2 columns A, B, C etc.

 

[RoryA]

Then just change the Sheet1!$1:$1 part to Sheet1!$2:$2

On an admin note, why do you appear to have two user accounts? We do not allow that, so which one do you want to keep?

 

[contactjohnpollard]

Then just change the Sheet1!$1:$1 part to Sheet1!$2:$2

On an admin note, why do you appear to have two user accounts? We do not allow that, so which one do you want to keep?

Rory Hi I did not know I had two accounts, I will keep this one thx.

BTW I got your formula to work thx. Can I ask 2 more enhancements

1. If I want to return values from a different row on sheet 1 which part of the formula do I change?
2. If I want to start returning values from same row but different starting column (say K) which part of the formula do i change?

TIA John

 

[RoryA]

1. Just change the row numbers in the Sheet1!$2:$2 part.
2. Simplest way is probably again just to alter the first range to something like Sheet1!$K$2:$IV$2 using whatever columns/row are appropriate.