Copying cell entries

Discuss the spreadsheet application

Copying cell entries

Postby IanP » Mon Jan 21, 2019 10:59 am

Hi all,

I don't know whether this forum is intended for such questions, but no doubt I'll be told if it isn't! Here goes.

I've created a simple spreadsheet to illustrate a requirement that I can't find a process to deal with.

Box A of the sheet shows the change of price for “funds” by date, with an accompanying chart. To add a new row of data for the next date's values, I add a row (or cells) above the yellow line. This automatically extends the chart to include the next row of data, which is great.

Turning to Box B, this converts the pricing data into values. To get the latest valuation, I have to copy the prices from Box 1. I'd like to be able to pick them up automatically but can't see how I can do that, short of using database techniques or look up table-type actions. AM I missing something simple, or do I have to get technical?

Thanks,

Ian
Attachments
Sampl Calc.ods
(20.37 KiB) Downloaded 16 times
OpenOffice 4.1.5 on Windows 7
IanP
 
Posts: 7
Joined: Sat Oct 06, 2018 11:01 am

Re: Copying cell entries

Postby Lupp » Mon Jan 21, 2019 2:45 pm

Don't list in a column what originally was listed in a row and v.v. Otherwise you get useless complications due to needing to transpose data before you can access them. See attachment.
Attachments
aoo96687SampleReworked_1.ods
(22.47 KiB) Downloaded 9 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copying cell entries

Postby IanP » Mon Jan 21, 2019 4:47 pm

Hi L,

Many thanks for your prompt response. It's complicated by the fact that I have some very large spreadsheets already in that format, but I'll have a look.

Ian
OpenOffice 4.1.5 on Windows 7
IanP
 
Posts: 7
Joined: Sat Oct 06, 2018 11:01 am

Re: Copying cell entries

Postby Lupp » Mon Jan 21, 2019 5:51 pm

There are ways to get the needed transposition based on a primary table either by formulae or by user code. Generally I would dissuade from relying on user code, but doing the task with formulae comes with disadvantages, too. There is a standard function for array-evaluation doing it in a very simple way. However, like any function with array output, it needs to lock a fix range for output on the first run. This makes it very unflexible. Solutions based on single-cell formulae, on the other hand, tend to be inefficient and to bloat the file size at the same time.
For further advice I would need a more realistic example sheet.

(A professional would annyway suggest to move the task to a database.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copying cell entries

Postby IanP » Tue Jan 22, 2019 12:59 am

Hi again L,

Thanks for the additional comments. I've thought of a way of simplifying the task a little, using IF, which is a little cumbersome, still requiring some manipulation with every line added, but quicker than the current method. I did start to look at databases a while ago but didn't get very far. Something to consider when I run out of other "hobbies."

Thanks again,

Ian
OpenOffice 4.1.5 on Windows 7
IanP
 
Posts: 7
Joined: Sat Oct 06, 2018 11:01 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests