I have a sheet which looks like :
| Employee | Sat 10 /01 | Sun 10 /02 | Mon 10 /03 | Tue 10 /04 | Wed 10 /05 |
|---|---|---|---|---|---|
| a | 1 | 1 | |||
| b | 1 | 1 | |||
| c | 1 | 1 | |||
| d | 1 | 1 | 1 | 1 | |
| e | 1 | 1 | 1 |
I have transposed this sheet:
| Employee | a | b | c | d | e |
|---|---|---|---|---|---|
| Sat 10 /01 | 1 | 1 | 1 | ||
| Sun 10 /02 | 1 | 1 | 1 | ||
| Mon 10 /03 | 1 | 1 | 1 | ||
| Tue 10 /04 | 1 | 1 | 1 | ||
| Wed 10 /05 | 1 |
I just want that each of entries are connected with each other which simply means, using this formula on each cell i.e B2 in sheet 2 should be connected using a formula : =Sheet2!B2 and B3 should be =Sheet2!C2, but dragging down the formula it gives =Sheet2!B3, =Sheet2!B4 and so on, but I want this formula to work horizontally.
I'd appreciate your help because this data is quite large (approximately 90-100 employees).
CodePudding user response：
Use this if you want to transpose sheet1 in sheet2
=TRANSPOSE(Sheet1!A1:500)
CodePudding user response：
Is there anyway I can create a series like =Sheet4!B2, =Sheet4!C2, =Sheet4!D2
try dragging down:
=INDIRECT(ADDRESS(2, ROW(A1), 4,, "Sheet4"))