Excel - Formula for data in non-consecutive columns

[mejh1984]

Hi all

I have a spreadsheet where I want to extract values from another spreadsheet which are in consecutive columns.

However, in the spreadsheet I am working with, I want the data to appear in every third column (i.e. A, D, G, J...), as I have % values in the '-in-between' cells.

Is there a formula where I can extract the data in columns A-D from one spreadsheet and copy them into A, D, G, J in another spreadsheet, without having to adjust the formula for each cell?

Thanks

 

[Joe4]

Welcome to the Board!

Are you wanting formulas referencing the other sheet, or do you want to copy over the hard-coded values to the other sheet?

Also, because you want to put stuff in between these columns (other formulas/values having your % values), I don't think a formulaic drag option will work, as it will overwrite those formulas values in columns B, C, E, F, etc. I think that this would probably require a VBA solution.

If you are open to a VBA solution, please also let us know the names of your two sheets, and what rows these values in columns A-D need to be copied from (namely, what is the first row to copy from on Sheet1 and what is the first row to copy to on Sheet2 - we can dynamically figure out the last row with data in column A).

 

[mejh1984]

Hi

Thanks so much for getting back to me.

Please find attached an example spreadsheet. I would like a formula for getting the data in A4, B4, C4, D4, E4, F4 on the 'Labour Costs' sheet into B4, E4, H4, K4, N4 Q4 on the 'Budget' sheet.

I have lots of sheets like this one, so wondered if there is a formula that can be copied from cell to cell, rather than manually have to change the formula in each cell.

I am happy with either formulas referencing the other sheet or hard-coded values to the other sheet.

I appreciate it won't be possible to have a formula that can be dragged across as it will mess up the data in the in-between columns.

Thanks again!

Budget

Jan (£)

Jan (%)

Feb (£)

Feb (%)

Mar (£)

Mar (%)

Apr (£)

Apr (%)

May(£)

May (%)

Jun (£)

Jun (%)

Sales

40000​

30000​

25000​

35000​

40000​

20000​

Labour Cost

0.00%​

0.00%​

0.00%​

0.00%​

0.00%​

0.00%​

Labour Costs
Jan	Feb	Mar	Apr	May	Jun
5000​	6000​	8000​	7000​	6000​	5000​

 

[Joe4]

Trying placing this formula in cell B4 on your Budget sheet:

=OFFSET('Labour Costs'!$A4,0,(COLUMN()-2)/3)

Now, try copying it to cells E4, H4, K4, N4, and Q4 and watch what happens.

 

[mejh1984]

That worked, thank you for your help!

 

[Joe4]

You are welcome.
Glad I was able to help!