LibreOffice Calc question.

[XFool]

My portfolios are on a LibreOffice Calc file/'document', with one sheet for the portfolios and another (same document) for the prices. Originally, on the Portfolio sheet, I referenced the price, eps etc. information on the Prices sheet by cell reference: "Prices.B8"

This became tedious as additions or changes were made to the Portfolio sheet. So I looked into Named cells. This worked very well so, instead of say "Prices.B8" I would have "Prices.EPIC_price", "Prices.EPIC_dps". Great!

With increasing complexity of different Calc documents I wanted to change to a single stand alone Calc document called PRICES that could be used by all other documents needing price information. This indeed works using the original cell reference method: 'PRICES'#Sheet1.B8 But I have been dismayed to find that it seems not to work using Named cells on the PRICES document: 'PRICES'#Sheet1.EPIC_price

Is this the case, or am I just not doing it correctly? Does anyone know how to do this in Calc, if possible?

Presumably I could use cell addressing from the PRICES document to a separate Prices sheet on every document needing prices and then use cell Naming from that sheet to the main document sheets. But that seems to defeat the whole attempt at simplification.

TIA

[ReformedCharacter]

My portfolios are on a LibreOffice Calc file/'document', with one sheet for the portfolios and another (same document) for the prices. Originally, on the Portfolio sheet, I referenced the price, eps etc. information on the Prices sheet by cell reference: "Prices.B8"

This became tedious as additions or changes were made to the Portfolio sheet. So I looked into Named cells. This worked very well so, instead of say "Prices.B8" I would have "Prices.EPIC_price", "Prices.EPIC_dps". Great!

With increasing complexity of different Calc documents I wanted to change to a single stand alone Calc document called PRICES that could be used by all other documents needing price information. This indeed works using the original cell reference method: 'PRICES'#Sheet1.B8 But I have been dismayed to find that it seems not to work using Named cells on the PRICES document: 'PRICES'#Sheet1.EPIC_price

Is this the case, or am I just not doing it correctly? Does anyone know how to do this in Calc, if possible?

Presumably I could use cell addressing from the PRICES document to a separate Prices sheet on every document needing prices and then use cell Naming from that sheet to the main document sheets. But that seems to defeat the whole attempt at simplification.

TIA

This might help:

https://ask.libreoffice.org/t/how-to-reference-a-named-cell-on-a-named-sheet/90742

It depends on the scope of Name (see Menu / Sheet / Named Ranges and Expressions / Manage...)

RC

[XFool]

This might help:

https://ask.libreoffice.org/t/how-to-reference-a-named-cell-on-a-named-sheet/90742

It depends on the scope of Name (see Menu / Sheet / Named Ranges and Expressions / Manage...)

Thanks! But sadly not... I am already aware of the "Scope" option but, AFAICS, it can be used to restrict the scope to individual sheets in the same document, or all of them ("Global" option).

But the "Global" - 'All of them' - only seems to apply to all sheets within the given document or spreadsheet. Not truly globally as in the sense of external spreadsheets.

[chas49]

I'm not a LibreOffice user so only guessing here - but does it make any difference if the referenced external sheet is open?

[XFool]

I'm not a LibreOffice user so only guessing here - but does it make any difference if the referenced external sheet is open?

Good idea. Sadly, it doesn't seem to.

That did give me the idea of trying to Name a cell in an open sheet remotely, via another open spreadsheet. That didn't work either.

[kyu66]

I'm not a LibreOffice user so only guessing here - but does it make any difference if the referenced external sheet is open?

Good idea. Sadly, it doesn't seem to.

That did give me the idea of trying to Name a cell in an open sheet remotely, via another open spreadsheet. That didn't work either.

Use vlookup on a range as per https://www.youtube.com/watch?v=6b4Z7d2iSUM

[Alaric]

Use vlookup on a range

vlookup is the suitable function to use to look up the current price in a "prices" file in order to value a portfolio in a "holdings" file.

[GrahamPlatt]

In essence, you should be using a database…

viewtopic.php?p=634177#p634049

[XFool]

Thanks to those who suggested the VLOOKUP() function (I use spreadsheets but am not an 'advanced' user). I have checked and this does indeed work, though it seems a lot less elegant than directly addressing a Named cell in another sheet.

But it looks like the use of Named cells is tethered to the sheet in which the names are defined. Unless there is some technical reason why it cannot readily be implemented in an external spreadsheet this looks to me like an oversight in spreadsheet design.

I'll have to think about this...

[GrahamPlatt]

Thanks to those who suggested the VLOOKUP() function (I use spreadsheets but am not an 'advanced' user). I have checked and this does indeed work, though it seems a lot less elegant than directly addressing a Named cell in another sheet.

But it looks like the use of Named cells is tethered to the sheet in which the names are defined. Unless there is some technical reason why it cannot readily be implemented in an external spreadsheet this looks to me like an oversight in spreadsheet design.

I'll have to think about this...

This is one area where LibreOffice is lagging behind Excel (though hopefully not for long) with their introduction of the XLOOKUP function
https://support.microsoft.com/en-us/off ... eae8bf5929

[XFool]

This seems to be the way to do it:

Firstly, you need two spreadsheets, starting with the original PRICES file which is to be the single place where prices etc. are to be updated. Then you have another spreadsheet file, let us here call it DATA which is more or less a simple copy of the PRICES file, to start with. (Actually, the filenames might make more sense the other way around...)

You then use the VLOOKUP() function in the second, DATA spreadsheet, to point to all the data fields in the PRICES file. As this is just a single, one off operation, it won't be too much of a problem. You then Name all the relevant cells in this second DATA file. "Epic_price", "EPIC_eps", "EPIC_dps" as required. *

This DATA file, with the Named cells, then becomes the starting point or root file for all future spreadsheets, to preserve the cell Names. You cannot, AFAICS, simply copy and paste this DATA file into your other spreadsheets as needed (my original idea) because the Named cells do no copy across, they seem to be tethered to the Calc spreadsheet file (MS Workbook) in which they were created. So, to be any use outside the original DATA file, you need to start a new spreadsheet by cloning a new sheet from this DATA file. e.g. Using Windows "Copy here" to create a duplicate file "DATA - Copy". This file is then renamed as required and used as the basis of the new spreadsheet. If converting an existing spreadsheet file, create the same number of sheets in the "DATA - Copy" file and copy and paste across the sheets from the existing file.

Thanks again for help in this matter.


* In my OP I made a mistake here, with Global context (the default) for the Named cells in a spreadsheet you don't need to specify the sheet ("Sheet1.EPIC_price"), just the simple name anywhere in the spreadsheet. So the direct name e.g. "EPIC_price" will work throughout the entire spreadsheet file (MS Workbook). Which is why it is such a great convenience.

[kiloran]

This is one area where LibreOffice is lagging behind Excel (though hopefully not for long) with their introduction of the XLOOKUP function
https://support.microsoft.com/en-us/off ... eae8bf5929

I hadn't heard of XLOOKUP (I have Excel 2016 which does not support this function).
It looks like XLOOKUP is like VLOOKUP but allows retrieval of data to the left of the lookup range (VLOOKUP can only retrieve to the right), and also allows wildcards in the match. There is a request to include it in LibreOffice Calc, but it looks like it was delayed due to the developer's ill health. I see it is available in Google Sheets.

--kiloran

[modellingman]

It looks like XLOOKUP is like VLOOKUP but allows retrieval of data to the left of the lookup range (VLOOKUP can only retrieve to the right), and also allows wildcards in the match. There is a request to include it in LibreOffice Calc, but it looks like it was delayed due to the developer's ill health. I see it is available in Google Sheets.

--kiloran

The "old school" way of using any column in an array as the lookup to return the corresponding row from another column is the INDEX/MATCH construct as in

=INDEX(returncolvalues, MATCH(lookupvalue,lookedupcolvalues,0))

The looked up column and the return column can be any columns of the array. I don't make much use of LibreOffice but suspect the construct will work with it.

modellingman