Transposing Data

[chinanomad]

I would like to know how to transpose (or paste link) data without using those functions. Ultimately a table assuming it has the same sets of values for the column(s) and row(s) headers should be able to match values in the matching cells.

In the attached example I'm trying to create a formula which says:

=IFS(Sheet1!B3:Sheet1!F14,Sheet1!B1:F1,"A",Sheet1!A3:A14,<="&EOMONTH($C1,0),Sheet1!K3:K62,">"&EOMONTH(C1,0))

The value that it should input is 3. I researched MATCHing functions, but could not find an example that uses the information from one table to fill in the other table.

[DonkeyOte]

Based on your specific sample file

Sheet2!B2:
=INDEX(Sheet1!$B$2:$F$14,COLUMNS($B2:B2),ROWS(A$2:A2))
applied to matrix

You can replace the COLUMNS & ROWS with MATCHes as nec.

[chinanomad]

In the formula =INDEX(Sheet1!$B$2:$F$14,COLUMNS($B2:B2),ROWS(A$2:A2))

Does $B2:B2 and A$2:A2 refer to sheet1, sheet2 or both?

What is a good reference book to learn excel logic?

[DonkeyOte]

Range references are relative to the sheet on which they reside unless explicitly specified, eg:

=A1
relative to sheet on which it resides

=Sheet2!A1
tied to Sheet2 explicitly (irrespective of which sheet the above resides)