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

Discuss the spreadsheet application
Post Reply
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

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

Post by swmwshrk »

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
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Post by MrProgrammer »

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.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
swmwshrk
Posts: 22
Joined: Fri Aug 15, 2014 3:56 am

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

Post by swmwshrk »

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
Post Reply