need a formula where the columm stays constant and the row changes when dragging down-

[Janeice]

In my formula, ='Benefit Other 2016'!$E137 I need the E to stay constant but the 137 to add forward. I am dragging the formula across but it is not working-both the E and the 137 are staying constant so instead of E138 I am getting E137.
I am linking two worksheets where the first sheet is going from left to right and the sheet I am taking it to is going from top to bottom.
Thank you for any assistance.

 

[KenU]

@Janeice,

I believe the problem may be that you are dragging the formula ACROSS rather than DOWN. The row number is only incremented when dragging DOWN. One way to get the desired result is as follows:

• drag your formula down to get the correct row increment
• select the newly created vertical formula range
• copy
• select the leftmost cell where you want the formulas arranged horizontally
• Paste Special checking the Transpose checkbox

You may need to do this on an area of the worksheet where you have some extra space to drag the formula down the needed amount.

Hope that helps.

Regards,
Ken

 

[NARAYANK991]

Hi ,

I am not able to understand the following from your post :

I am linking two worksheets where the first sheet is going from left to right and the sheet I am taking it to is going from top to bottom.

If we assume the first worksheet is where the data is , and the second where the formulae will be entered , then in the second worksheet you should be copying the formula downwards , not across.

If we assume that your above quote is wrong , and the data goes downwards , while the formula will be copied across , then you can try the formula :

=OFFSET('Benefit Other 2016'!$E$137,COLUMN()-COLUMN($B1),)

I have assumed that you will be entering the above formula in any row in column B , and copying it across ; if this column is different from column B , change the formula to suit. The row can be any row , it does not make any difference to the formula.

Narayan