Data from colum into rows

[harveywalker]

Hi,

I'm stuck.

I've created a spreadsheet for work, that is based on data entered into rows along the worksheet (Sheet1), but I'm wanting to make a summary sheet with the same data from the first sheet, on the second, but this time the vertical data needs to be horizontal (e.g. Sheet1, Cell A15, needs to go onto Sheet2, Cell D4 & Sheet1, Cell A20 needs to go onto sheet2, Cell D5 etc. etc).

So far, I've been manually "autofilling" the cells in Sheet2 (by using the formula function and deleting the =), then dragging them around the sheet into position. There must be an easier way of doing this!

I've looked at OFFSET, but my problem is the data starts at A15, and continues every 5th row down the sheet, and for the life of me I can't figure out where I'm going wrong, as every way I try I get #REF! or #VALUE!.

Anyone tried the same and got an answer? There could be a beer in it for someone.....!

[Søren Larsen]

You may want ot try to create a "Pivot table" or use

Please Login or Register  to view this content.

[arlu1201]

Upload a sample file.

To Attach a File:

1. Click on Go Advanced.
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[harveywalker]

Hi Soren and Arlette,

I'll have to try and figure out a pivot table!

I've attached a verison of my spreadsheet as an example of what I'm trying to achieve.

Thanks for the help so far.

Harvey

[arlu1201]

Why are you using merged cells in your file? They create more problems than helping to solve any.

If you want to have broader rows, you can increase the height of a row but do not use merged cells for this.

See the attached sheet. I have inserted a new sheet called "New Take Off" and put in your data without merged cells. Then i created a pivot in the same sheet. You can just copy paste the values from the pivot into your sheet2 for the report.

[harveywalker]

Hi Arlette,

I used merged cells, as further along the spreadsheet each "FY" has certain components feeding into it, and these need to be on seperate rows. I used merge just to make it clear what references I was using. The pivot table seems to be almost what I'm looking for, but as my spreadsheet changes for each job, I could do with something that is easily copied (as the layout on Take off needs to be copied down the sheet). I've tried the "transpose", but this just gives me the same layout as the first sheet! I'm sure there must be a formula which uses transpose, but with a x5 offset. I manages to get "offset" to work down the sheet, as this gave me all the FY's in consecutive rows, I just need to get this to work in the columns instead!

[arlu1201]

How about putting the formulae via VBA?

[harveywalker]

Hi Arlette,

I've no idea on how to use VBA.....!

I just hoped there was a simple formula to pick data every 5th row and plonk it in a column, and I can then use autofill to populate the remaining empty cells with the same data, but pick every 5th row (ie A15, A20, A25 etc etc...). I've tried everything I can think off, and the closest I've come is using =INDEX(Sheet1!$A:$A,(ROW()-1)) and then amending each cell *5. It's a complete pain, but thanks for the assistance!