# Copying formulas from horizontal to vertical

1. ## Copying formulas from horizontal to vertical

Hello,

I want to quickly copy data between two worksheets within the same work book - one contains the source data and another acting as the summary sheet. In the source data I record cost entries by type and by month with Cost Type as column headers and a row for each month. E.g

A B C D
1 Travel Telephone Petrol
2 Jan £10 £5 £4
3 Feb £5 £23 £7
4 Mar £4 £7 £21

In the summary sheet I want to replicate this but am recording months it horizontally - so it should look like this:

A B C D
1 Jan Feb Mar
2 Travel =Book1!B2 =Book1!B3 =Book1!B4
3 Telephone =Book1!C2 =Book1!C3 =Book1!C4
4 Petrol =Book1!D2 =Book1!D3 =Book1!D4

I can link the dat for one cell easily enough (i.e. "=Book1!B2") but when I try and drag across to the other cells I get the next increment of letter rather that number e.g cell E2 for Travel in April becomes =Book1!C4 not =Book1!B5.

I often struggle with this and wondered which formula and syntax I need to use please - I looked at transpose() but do not think it is the right one. I need to do this for loads of data help much appreciated!

Thanks

2. ## Re: Copying formulas from horizontal to vertical

Try something like:

=INDEX(Book!\$B:\$D,COLUMNS(\$A\$1:B\$1),ROWS(\$A\$1:\$A1)) copied down and across

3. ## Re: Copying formulas from horizontal to vertical

Ok - thanks. So if my source data for Travel actually starts in cell "Book1!G35" for Jan and goes to "Book1!G46" for December whilst my summary data goes from E32 (Jan) to P32 (Dec) how do I need to adjust the formula please?

Thanks!

4. ## Re: Copying formulas from horizontal to vertical

Possibly?

=INDEX(Book1!\$G\$35:\$I\$46,COLUMNS(\$A\$1:A\$1),ROWS(\$A\$1:\$A1))

assuming you have data to bring over in adjacent columns in Book1....

5. ## Re: Copying formulas from horizontal to vertical

Perfect - thanks very much!!

6. ## Re: Copying formulas from horizontal to vertical

You are welcome,

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

7. ## Re: Copying formulas from horizontal to vertical

All done - thanks for the reminder!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1