Mar 09 2021 11:30 AM
I'm trying to use a formula =Data!J$3, but when I spread it downwards it goes
=Data!J$3
=Data!J$3
=Data!J$3
I know its correct, but I wanted it to be like this:
=Data!J$3
=Data!K$3
=Data!L$3
Is there any way to "spread it" downwards and change the letter instead of the number?
Mar 09 2021 11:48 AM
One option is to use the TRANSPOSE function.
Let's say you want to refer to J3:Z3 (17 columns).
Select 17 cells in a column, enter =TRANSPOSE(Data!J3:Z3) and - if you don't have a recent version of Microsoft 365 - press Ctrl+Shift+Enter.
Another option: let's say the first formula will be in a cell in row 2, e.g. D2.
Enter the formula =INDEX(Data!$J$3:$Z$3,COLUMN()-1)
Fill down.
If the first formula is in row 5, use COLUMN()-4 etc.