Search code examples
excelformulasvba

Vba population rows with formula

I have a sheet with 100 columns in each row. The column in row one has to be populated with data

Such as 

B2= Indirect("'sheet1'!N105")
C2= Indirect("'sheet1'!S105")

And so how it goes till column CW100.

The data to be fetched from sheet 1 is present at interval of 5 column like N105, S105, X105 and so on.

Is there a simpler way to do it at once,instead of manually entering the formulas?

Solution

  • For B2 you can use something like

    =INDIRECT("'sheet1'!R105C"&5*COLUMN()+4,FALSE)

    The R1C1 form of INDIRECT is invoked by using FALSE as the second parameter and the source column number is calculated as 5X the destination column number plus an offset of 4.

    You might also be able to use OFFSET instead INDIRECT, with similar calculation of source columns.

    Hope that helps