Copying cell entries

[IanP]

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

[Lupp]

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.

[IanP]

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

[Lupp]

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.)

[IanP]

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