[Solved] Moving info in a row to a column with formula

Discuss the spreadsheet application

[Solved] Moving info in a row to a column with formula

Postby cartoonjazz » Wed Sep 11, 2019 9:21 pm

Is it possible to have calc fill formulas based on an incrementing cell letter rather than number?

the source data from another tab in a worksheet is listed horizontally in a row not vertically in a column. So instead of autofill incrementing A1, A2, A3,,,etc. We require it to fill A1, B1, C1 etc.
source sheet 1 looks like this
Image

sheet 2 should show the data in sheet i vertically instead of horizontally
Image
Last edited by robleyd on Fri Sep 20, 2019 4:38 am, edited 3 times in total.
Reason: Add green tick
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm

Re: Fill formulas based on an incrementing cell letter

Postby RusselB » Wed Sep 11, 2019 11:41 pm

Do the auto-fill in the same direction as the data.
While a formula could be written to properly account for the increasing column designator while going down rows, it's easier to work with what's already coded to the program
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5414
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Fill formulas based on an incrementing cell letter

Postby cartoonjazz » Thu Sep 12, 2019 8:31 pm

Thank you, that does work however I really need to to do it the way described. I wonder if there is a formula for generating it the way I require. ie make the letter part of the formula an expression that increments by 1 and leaving the number part of the cell. You mentioned there was a way of doing this can you point me in the right direction of how you would build the formula?
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm

Re: Fill formulas based on an incrementing cell letter

Postby MrProgrammer » Thu Sep 12, 2019 9:12 pm

cartoonjazz wrote:Is it possible to have calc fill formulas based on an incrementing cell letter rather than number?
[Solved] Moving info in a row to a column

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.

[Tutorial] Ten concepts that every Calc user should know
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: 3815
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Moving info in a row to a column with formula

Postby cartoonjazz » Sat Sep 14, 2019 4:17 pm

Thank you I looked at that offset formula and tried implementing it with no success. wondering if anyone can help me with a suggestion re the syntax of any formula that will get this to work.
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm

Re: Moving info in a row to a column with formula

Postby Zizi64 » Sat Sep 14, 2019 8:32 pm

Try the array function named: TRANSPOSE()
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8230
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Moving info in a row to a column with formula

Postby cartoonjazz » Fri Sep 20, 2019 4:18 am

transpose works great thank you
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests