copy values from columns to rows while skipping empty columns...

forum_sample.xlsxtable.png

Hello! I have a problem in excel that I do not know how to solve.

I have a sheet "data" with values in columns like in the image. In between the columns there are gaps or other data that I do not want to draw from.

I have another sheet "calculation" in which I draw from the "data" sheet and do some calculations, calculate some indicators.
The way I do this, is as if I am transposing the data.

For example, in the "calculation" sheet, each row will take values from one specific column and do the calculations.

My problem is the following:

Because I have many columns in the "data" sheet and many "rows" in the "calculation" sheet, what I want to do is prepare one row and fill-down the formulas and calculations.

However, when I do this I need to skip the empty columns in the "data" sheet. So if a row takes from column A, then I want the next row to take from column B and and ignore the copy in between. I tried preparing 2 to 3 rows of data like this and fill them down but it doesn't work like this. I have formulas like in the 2nd picture and I want to be able to fill down the sheet.

RESULT.png

I hope someone knows a quick work-around on this. Can I do this without using VBAs? I am not experienced in VBA.

Thank you,

Eric

Please attach a sample Excel sheet, and not a picture of one. Few people will want to have to retype your data for you. Also, some members cannot see images on their PCs, depending on the browser being used..

Mmm. I had been hoping that you would also have put in some indication of what you were expecting to see as a result. You didn't; so this is a bit of a blind guess!!!

In B18 (or wherever you want), copy across & down:

=OFFSET($B$2,COLUMNS($A:A)-1,2*(ROWS($1:1)-1),,)