Forum Discussion

jkpanic's avatar
jkpanic
Copper Contributor
Oct 16, 2019

Fixing row numbers and changing columns in copying formulas

Hi

 

When I write a lookup formula such as "='Sheet1'!B3" and then try to copy it down a column I changes the row number. What I want is for the row number to stay fixed and the column to change. I know that this can happen if I copy the formula across columns as opposed to going down rows but for my purposes I need the information displayed as if on an A4 page when printing in portrait not compressing the size to fit columns on a printed page in landscape.

 

Is this possible?

 

Cheers

 

excel
Formulas and Functions

1 Reply

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 16, 2019

    jkpanic 

    You may try this...

     

    In B2

    =INDIRECT("'Sheet1'!"&ADDRESS($B$1,ROWS(A$1:A2)))

    and copy it down.

    The cell A1 on Sheet2 contains a dropdown list for all the names in column A on Sheet1 and if you change select a name from the dropdown, the formula will return the correct output as per the name selected.

    Please refer to the attached for more details.

     

     

Resources