Get data from rows to column

[Martines91]

Hi, I'm currently working on a table for my department, that will gather data from another table and it will make a chart out of it, but the values that will be written to the main table is going to be written in rows e.g. from G1 to G10 but I need another table that will gather these values but to cells let's say D5 to L5 (I'm posting this from my phone so it's hard to explain) I just wanted to write something like ='Data'!G1 and drag it horizontally, but of course it would just change to H1, I1 etc. is it possible to drag it that way but the letter will stay but number will change? Thanks

Just to be clear, I'm using slovak excel so formulas may lool different

[sandy666]

you can try =INDIRECT("Sheet1!G"&COLUMN(A1)) (with your example and sheet1, sheet2)

ups I did column to row but it's easy to change it

or you can try TRANSPOSE()

[Martines91]

So my colleague lent me his PC to access the internet from work (we have utterly retarded policy about internet here)

Ok so again, I'll try to explain the situation again and properly:

The current table I'm working on is going to be used each week and month so using the transpose would be time costly, it needs to be automated but clicking on every cell and liking it manually is a bit too much since
there are a lot of cells to be automated,

I have a workbook named "data" where my colleagues will add measured values, it's separated to Left and Right and the values are added vertically (to rows), then on another workbook named "Values - Left"
is another table, which will take individual values from "data" but horizontally

To be exact, "data" values are from Row: G6 to G14, these values need to be added to "Values - Left" D6 to L6, and this goes for Rows: H6 to H4 to Columns: D12 to L12 etc.

So I wanted to just link a single cell to "data" and then drag it horizontally but instead of changing numbers I need it to change letters, then do this to all 12 points / cells

Thanks

[sandy666]

hm, without attached example excel file I can say: look at PowerQuery

so

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Martines91]

I thought so, so I've made the file without the sensitive data, if you still don't know
what I need, just let me know

[Glenn Kennedy]

In E7, copied across to M9:

=INDEX(Data!$E$11:$J$19,COLUMNS($A:A),1+INT(ROWS(($1:1)-1)/7))

then copy paste into E16, E23, etc.

[Martines91]

Dude, you're amazing! That's exactly what I was looking for! :D They bought me a Excel II Course but stuff like this is just... a bit too complicated to learn in 3 days I think. While you're here, I have another question, but I'm not sure if I should post it here or make another topic since it's regarding charts?

In short: from these data I will make a line chart, from 9 measures - this is a weekly report, and I need to make another file, in which there will be a chart, that will take all the data from the whole month and make a link chart out of that, so let's say there are going to be 4 files, each with a line chart from 9 measures, total of 36 measures, and then a single file that will take the data from each individual file and make a huge 36 measure line chart

[Glenn Kennedy]

Better to start a new thread, as it's a separste issue.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.