## [Solved] Moving info in a row to a column in different sheet

### [Solved] Moving info in a row to a column in different sheet

At work we have two workbooks we use. Each contains a single sheet.

The first sheet contains the sales goals for all the stores in the district. This number is in a row (15 for my store) each column is a day, every 7th column is the weekly goal.

The second sheet contains Columns
A Date
B this year sales
C Goal
D last year sales
E +/- \$ to goal
F % to goal

I have to enter last year sales from last years sheet (copy/paste) and this years sales goal.

I merged the two sheets into one workbook, and used
='goals'.c\$15 in C4. Now if I try to copy or drag that down the column it stays C15. I need the column to advance by one for each cell down.

So C5 should be ='goals'.d\$15
C6 ='goals'.e\$15
Etc

Is there a way to do this?
Last edited by floris v on Sun Jul 14, 2019 7:44 pm, edited 3 times in total.
Reason: Edited solved tag, added check mark, floris v, moderator
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
swmwshrk

Posts: 18
Joined: Fri Aug 15, 2014 3:56 am

### Re: Moving info in a row to a column in different sheet.

swmwshrk wrote:used ='goals'.c\$15 in C4. Now if I try to copy or drag that down the column it stays C15.
Of course. In C4 the cell reference =goals.C\$15 means "This column, row 15". If you copy that formula to C5 it still means "This column, row 15", which is cell C15. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know. You will never be able to use formulas effectively in any spreadsheet unless you understand this material.

swmwshrk wrote:I need the column to advance by one for each cell down.
Put formula =OFFSET(\$goals.\$C\$15;0;ROW()-4) in the cells.
C4 references =OFFSET(\$goals.\$C\$15;0;0) (cell goals.C15)
C5 references =OFFSET(\$goals.\$C\$15;0;1) (cell goals.D15)
C6 references =OFFSET(\$goals.\$C\$15;0;2) (cell goals.E15), etc.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3843
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Moving info in a row to a column in different sheet.

That worked, thanks.
I am very new to excel/calc. What I do know so far is self taught and very limited, I still think formulas are a form of black magic.Please give me step by step instructions, and explain abbreviations. Thank you for your help

Win 7 pro Open Office 4.1.3
swmwshrk

Posts: 18
Joined: Fri Aug 15, 2014 3:56 am