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

Discuss the spreadsheet application

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

Postby swmwshrk » Sat Jul 13, 2019 8:05 pm

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.

Postby MrProgrammer » Sat Jul 13, 2019 9:49 pm

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

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

Postby swmwshrk » Sun Jul 14, 2019 4:34 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 31 guests