0

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).

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Make sure to add input and expected output as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 15 '22 at 13:09
  • What do you mean by "connect"? – TheMaster Oct 15 '22 at 13:39
  • Yes, I mean the cells in columns B : F to be connected –  Oct 15 '22 at 13:42
  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [How much research effort is expected of stackoverflow users?](https://meta.stackoverflow.com/questions/261592) – TheMaster Oct 15 '22 at 13:43
  • I have already shared what I tried, and how is it not helping me. =Sheet2!B2 is the easiest way to connect to cells from different sheets, but when I drag this formula down the cell references are not the way I want. please refer to my comments –  Oct 15 '22 at 13:46

2 Answers2

1

Use this if you want to transpose sheet1 in sheet2

=TRANSPOSE(Sheet1!A1:500)

enter image description here

Osm
  • 2,699
  • 2
  • 4
  • 26
  • I have already transposed my sheet, but I want to connect each of the cells with the sheet 1 (original_sheet) so whenever I make a change in my sheet 1 it automatically gets updated in the sheet2 –  Oct 15 '22 at 13:37
  • @Simran Any change made in sheet1 will get updated in sheet2 with this formula – TheMaster Oct 15 '22 at 13:43
  • I agree, but I have to do it daily so the changes are loaded in sheet 2, however if the are connected using a formula, it will get automatically connected. –  Oct 15 '22 at 13:45
  • 1
    @Simran It is automatically connected with this formula. – TheMaster Oct 15 '22 at 13:48
  • I have a new issue regarding this formula, basically, when I apply this formula in me sheet2, the whole sheet is reserved as a transposed sheet from sheet 1, but I want to use the last row of my sheet2 for some calculations, which is now not possible, please help @TheMaster –  Oct 15 '22 at 14:29
  • @Simran Kindly ask a new question – TheMaster Oct 15 '22 at 14:30
  • site says : I can't ask a new question @TheMaster –  Oct 15 '22 at 14:31
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/248822/discussion-between-simran-and-themaster). –  Oct 15 '22 at 14:32
  • @Simran Play with [`INDEX/COUNTA`](https://stackoverflow.com/a/46884012/) – TheMaster Oct 15 '22 at 14:46
  • @Simran try this `=TRANSPOSE(Sheet1!A1:P500)` – Osm Oct 15 '22 at 14:50
0

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"))
player0
  • 124,011
  • 12
  • 67
  • 124