Excel Help !

tonyh66

Im having a nightmare trying to create a column of formulas from a row of data. i.e. I can create the first formula in the column which is like this - Sheet1!B40, but I cannot replicate this downwards, I want the one below to read Sheet1!C40, Sheet1!D40 etc.

I have $ the 40 to fix it at 40 but cannot get the letter to ascend it stays as B40 in all the formulas if I drag it downwards.

I can copy and transpose the data but I need the location of the data so it changes when I alter the data values.

Stompa

Try:
https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027

Cornucopia

Or use the OFFSET function.

tonyh66

Thanks for the replies, I sorted it eventually created a formula for left hand cell, $ the number, drag copied it sideways to the right, then copied these formulas and did a paste special choosing formulas and transpose from the pop up box.

stragglebod

The ASAP addin has a pretty neat range transpose function that preserves formulae

https://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=52&utilities=42&lang=en_us

WaywardDriver

tonyh66 said:

Thanks for the replies, I sorted it eventually created a formula for left hand cell, $ the number, drag copied it sideways to the right, then copied these formulas and did a paste special choosing formulas and transpose from the pop up box.

That's OK as a one-off. If the formula starts in cell A1 of the other sheet, enter 
=INDIRECT("Sheet1!" & CHAR(65+ROW(A1)) & "40") and copy down.
Note CHAR(65+ROW(A1))="B". So if starting in a row other than 1, the 65 needs to be modified.
EDIT: above only works up to column Z

TrickyDicky101

=INDEX(Sheet1!$40:$40,ROWS($A$1:$A2))

copied down would be an alternative.

WaywardDriver

TrickyDicky101 said:

=INDEX(Sheet1!$40:$40,ROWS($A$1:$A2))

copied down would be an alternative.

Agreed - a better solution.