Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 31 Aug 2019 23:27:46 +0200INDIRECT and arithmetichttps://ask.libreoffice.org/en/question/206953/indirect-and-arithmetic/Indirect seems to work for group function ":" =SUM(INDIRECT("F4:" & "D" & L2))
When I try to use other functions it gives a #REF! error: =SUM(INDIRECT("F4+" & "D" & L2))
Is there a workaround?stellrSat, 31 Aug 2019 23:27:46 +0200https://ask.libreoffice.org/en/question/206953/Problem with hlookup and indirecthttps://ask.libreoffice.org/en/question/204562/problem-with-hlookup-and-indirect/I have a VLOOKUP formula, [ =VLOOKUP(G2,INDIRECT(F2),2,0) ], where F2 varies, but is a named range. This formula works.
When I try the same with HLOOKUP, [ =HLOOKUP(G17,INDIRECT(F17),2,0) ] the INDIRECT fails. If I put the name of the Range directly into the formula, it works, however if I try to do it where the name of the Range is in F17, failure...
I think this is a bug, has anyone else come across this?
[C:\fakepath\working_vlookup.ods](/upfiles/15657315286678577.ods)gairstyTue, 13 Aug 2019 23:28:07 +0200https://ask.libreoffice.org/en/question/204562/I need help in creating a lookup formula from my chart.https://ask.libreoffice.org/en/question/198078/i-need-help-in-creating-a-lookup-formula-from-my-chart/So far I have tried to enter my data as a vertical table for length and weight. These are set whole numbers. I did another table for the weight distributions with percentages and the results I have calculated. I am stuck on the simple starting point of having my list recognize the predetermined weights chosen via a drop down option.
I keep getting this error code---508 ---My sheet info is attached--- I have tried a Vlookup =VLOOKUP(Info.A3:a12l,Info.B3:B12,3,0) i think this was the last formula used, i have used so many that were wrong on my end i can't even see straight.
I have attached my file that i am trying to do [C:\fakepath\Example.ods](/upfiles/15608895037799529.ods)007CharletteTue, 18 Jun 2019 18:29:12 +0200https://ask.libreoffice.org/en/question/198078/How to escape double quote in INDIRECT()https://ask.libreoffice.org/en/question/178879/how-to-escape-double-quote-in-indirect/In a spreadsheet I have the following formula:
=MINIFS($'2019'.B$1:B$150");$'2019'.E$1:E$150;"="&C4;$'2019'.B$1:B$150;">"&TODAY())
This works fine!
I want the tab (in this case '2019') to be red from another cell (in my case D2), so I started to build the statement using INDIRECT():
=MINIFS(INDIRECT($D$2&".B$1:B$150");$'2019'.E$1:E$150;"="&C4;$'2019'.B$1:B$150;">"&TODAY())
I changed the function for all ocurrences of the tab '2019' (which is in A2:
=MINIFS(INDIRECT($A$2&".B$1:B$150");INDIRECT($A$2&".E$1:E$150");"="&C4;INDIRECT($A$2&".B$1:B$150");">"&TODAY())
This works..
For speed I would like to have only one INDIRECT().
I can't get it to work, though.
Question:
Can the whole string in one go, so MINIFS(INDIRECT(......))?-rob-Thu, 10 Jan 2019 19:59:30 +0100https://ask.libreoffice.org/en/question/178879/Calc: INDIRECT() function with Column/Row labelshttps://ask.libreoffice.org/en/question/177468/calc-indirect-function-with-columnrow-labels/Hi there.
My curiosity is mostly based on [question by @JohnSUN](https://ask.libreoffice.org/en/question/3266/what-is-the-best-way-to-get-the-value-from-a-rectangular-table/) and [question by @ky](https://ask.libreoffice.org/en/question/177215/how-do-i-select-a-date-range-in-an-array/).
Assuming we have a rectangular table, where columns are years and rows are days of the year (test spreadsheet with examples is [attached](/upfiles/1545940260540763.ods)), we can use Named Ranges or Column/Row Labels to refer specific column or row. Then we can refer table cell via Year!DayOfTheYear syntax, for example `=Year2016!Jan05`. This method (subjective) is more elegant and easier to read, than using `MATCH()`, `INDEX()`, `LOOKUP()` and so on.
The next interesting thing is to use such a syntax for referencing some cell dynamically, when Year and Day values are not hard coded in the cell/formula, but are the result of user selection or some calculations. In this context, using Column/Row labels seems much more promising, cause, based on Calc settings, they can be assigned automatically and, depending on columns/rows count in the table, they do not mess Named Ranges list.
Meanwhile, while it is possible to use Named Ranges, combined with `INDIRECT()` function, to successfully refer desired range, the same functionality fails, when passing calculated column/row label strings to `INDIRECT()`.
So the question is, what is the difference between Named Ranges and Column/Row labels, that makes `INDIRECT()` treat them differently, even if they both refer the same range address and is there workaround possible?SM_RigaThu, 27 Dec 2018 21:19:20 +0100https://ask.libreoffice.org/en/question/177468/Can Indirect adressing use a cell value to change the "sheet" value?https://ask.libreoffice.org/en/question/168026/can-indirect-adressing-use-a-cell-value-to-change-the-sheet-value/ Good evening
The work here is to prepare a large spreadsheet for Cancer Research. Intended for physical chemistry undergraduates and doctorate researchers, the idea is to give them a map of the whole spreadsheet (an Index, a "yellow pages") so that they can select the z values from a different sheet that will be investigated compared to an x,y plot on the sheet where they work.
To do this, at the base of a column of data (of 248 rows), they're offered the column number of a different sheet (which they can look up in the 'map' or 'index'). The column above (for fetching that data) is programmed: =INDIRECT(ADDRESS(248,AQ264,1,,"Cancerdata"))
(this is the formula at cell AQ248 only, for the example).
In column AQ, AQ264 is the cell (highlighted in bright yellow) where they choose the other sheet column number. It works very well. The difficulty is that "cancerdata" is not the only sheet involved. It can be any other disease or indicator of disease.
Some user effort is required (of course), so it were ideal if they type once in one cell (correctly it's hoped) the name of the sheet to be fetched - Cancerdata; the intention is that their typing changes the "abcd" value in all the rows of the column above in quotes. Indirect adressing of a cell value to modify indirect adressing of a column to fetch data from a different sheet.
Without this, it's possible but laborious to edit the whole column above using 'find and replace'. I can do it to construct the spreadsheet (as author) yet have my doubts that users (3000 or more international) will find an IT specialist just around the corner to do it for them.
I hope this question is clear, ready for questions if not.
MatthewArchetypeSun, 07 Oct 2018 20:20:41 +0200https://ask.libreoffice.org/en/question/168026/Optimizing a Calc spreadsheet with 1000s of INDIRECT entrieshttps://ask.libreoffice.org/en/question/160687/optimizing-a-calc-spreadsheet-with-1000s-of-indirect-entries/ I have an inventory of over 3000 items, each item has its own spreadsheet listing name, year, company, etc. In each spreadsheet there is also an AVG function for price fluctuation, with a defined array of up to 500 entries and a COUNT function of entries to average. I was looking for a way to create a master spreadsheet compiling all item spreadsheets without having to either copy/paste data from each separate spreadsheet to the master (static data) or linking each cell from over 3000 spreadsheets one by one.
I came across @Lupp 's brilliant solution here (https://ask.libreoffice.org/en/question/43564/how-can-i-combine-the-data-from-2000-calc-files-into-a-single-csv-file/ ) and set about adapting the example to my needs.
Unfortunately, once I get over 250 or so entries, LibreOffice starts to bog down, CPU- and RAM-wise. Over 1000 entries and LibreOffice uses 4.7GB of RAM and 100% CPU, taking well over a minute to register changes and/or saving the document. Even scrolling stalls the program. For the technically curious, I'm on an i3 laptop with 6GB RAM running Ubuntu 16.04 LTS, LibreOffice 5.1.6.2. Yes, it's not the latest machine out there but more than sufficient for office-suite work. I can't imagine that the original poster @henryh13 didn't experience the same kind of performance issues.
Can @Lupp or any of the other resident experts here think of a less-CPU/memory-intensive way of corraling thousands of .ods files in a master document? Thanks in advance.gtomorrowSat, 14 Jul 2018 14:22:17 +0200https://ask.libreoffice.org/en/question/160687/Error referencing a cell from of other file with INDIRECT() functionhttps://ask.libreoffice.org/en/question/155769/error-referencing-a-cell-from-of-other-file-with-indirect-function/**Context**: I have large calc (.ods) text-data files, that needs some basic processing (text-processing, sums, sums-if,...), and I want to separate the files holding the data from the file holding the processing.
In the "process" calc file, I enter the considered filename, say in A1 I write "my_file.ods", and I want to use that as a reference for all the processing.
Now, in another cell, I want to fetch the data from a given cell in that file.
If I do that manually, I end up with a cell holding the following string:<br>
<code>='file:///mnt/sda2/some/path/my_file.ods'#$ABC.F2</code>
The cell will now hold the content of cell F2 from sheet ABC from file "my _file.ods".
*Note*: I notice that if I enter manually the string<br>
<code>='file:my_file.ods'#$ABC.F2</code><br>
it gets automatically converted to the above string (absolute path), but it does correctly return the cell value.
**Problem**:
Now I want to use the file name defined in a cell. This sounds like a usecase for the [INDIRECT()](https://help.libreoffice.org/Calc/Spreadsheet_Functions#INDIRECT)
function:
Entering in A2 the following function:
<code>=CONCATENATE("'file:";A1;"'#ABC.F2")</code><br>
produce the string
<code>'file:my_file.ods'#$ABC.F2</code><br>
**which is exactly the same as the string above** (with the exception of the '=' sign) !
But if I enter in cell A3 the string <code>=INDIRECT(A2)</code>, I get an error: <code>#REF!</code>
What did I miss here ?
**Edit** See also the help website [has a page](https://help.libreoffice.org/Calc/Referencing_a_Cell_in_Another_Document) about this.kebsWed, 23 May 2018 10:43:34 +0200https://ask.libreoffice.org/en/question/155769/INDIRECT() won't work for other sheets in one .xlsx filehttps://ask.libreoffice.org/en/question/146499/indirect-wont-work-for-other-sheets-in-one-xlsx-file/I have a particular file in which the INDIRECT() function gives a #REF! error whenever it is pointed to another sheet. For example: I have a sheet called "a", and on the current sheet (not "a") I enter =INDIRECT("a.a1") and I get a #REF! error. When I enter =a.a1 it returns the expected value. Likewise all other formula throughout the spreadsheet still work as expected when dealing with references to other sheets (eg, MATCH(), HLOOKUP(), etc). Only INDIRECT() fails.
But when I create a new file, INDIRECT() works as expected.
The spreadsheet is saved as an .xlsx, and moves back and forth between a Mac with MS-Excel and my own Win7 box with Libreoffice (v.5.3.7.2).
Are there any known bugs that could cause a spreadsheet to develop such a weird quirk? If so, is there a fix?
--
[I discovered this while trying to create a formula that worked with INDIRECT(). When it wouldn't work I stripped the components back to individual formula, and discovered the culprit. I thought I was going nuts. I literally created a sheet called "a" just to eliminate the possibility of typos.]Paul451Fri, 16 Feb 2018 11:35:20 +0100https://ask.libreoffice.org/en/question/146499/vlookup() inside indirect()https://ask.libreoffice.org/en/question/143811/vlookup-inside-indirect/I'm having trouble setting up a VLOOKUP formula that references another file when inside an INDIRECT function.
Here is an example:
**Works fine:**<br>
*A1*: January /// *B1*: VLOOKUP(C3, 'file:///C:/File.ods'#$January.C$1:E$10, 3, 0)<br>
*A2*: February /// *B2*: VLOOKUP(C3, 'file:///C:/File.ods'#$February.C$1:E$10, 3, 0)<br>
*A3*: March /// *B3*: VLOOKUP(C3, 'file:///C:/File.ods'#$March.C$1:E$10, 3, 0)<br>
**Doesn't work**<br>
*A1*: January /// *B1*: INDIRECT("VLOOKUP(C3, 'file:///C:/File.ods'#$"&A1&".C$1:E$10, 3, 0)")<br>
*A2*: February /// *B2*: INDIRECT("VLOOKUP(C3, 'file:///C:/File.ods'#$"&A2&".C$1:E$10, 3, 0)")<br>
*A3*: March /// *B3*: INDIRECT("VLOOKUP(C3, 'file:///C:/File.ods'#$"&A3&".C$1:E$10, 3, 0)")<br>
Any help would be greatly appreciated, thanks!
P.S.: Please note, C3 is a text value (such as someone's name) to be searched in the array.Michaud79Mon, 22 Jan 2018 08:06:57 +0100https://ask.libreoffice.org/en/question/143811/Two parameters functions: slope, intercept, etc. do not work with indirect(address()) as one parameterhttps://ask.libreoffice.org/en/question/131818/two-parameters-functions-slope-intercept-etc-do-not-work-with-indirectaddress-as-one-parameter/Hi,
I have a strange problem with libreoffice calc latest version 5.4.1.2 under ubuntu 16.04 64 bit (build 5.4.1~rc2-0ubuntu0.16.04.1~lo0).
I would like to use some functions that take two range parameters as input (slope, intercept, rsq, etc.).
These functions work well if the parameters range are defined as usual:
`SLOPE(absolute_range1,absolute_range2), INTERCEPT(absolute_range1,absolute_range2)` where absolute_rangeX is like this `$A$10:$A$100.`
However, I would like to able to change the range on which the functions work. For this purpose I use ADDRESS and MATCH function:
`H1=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell)-1,COLUMN(cell),1)` and I correctly get the cell address, let say `$C$10`.
After, by using `H2=INDIRECT(H1 &":$C$100")` I can obtain the required range and I able to use it in functions that take one parameter as `SUM(H2), SUMPRODUCT(H2)`.
Unfortunately, it does not work with `SLOPE(H2,$A$10:$A$100), INTERCEPT(H2,$A$10:$A$100)` and `SUMPRODUCT(H2,$A$10:$A$100)` too.
So is there any intrinsic limit about using `INDIRECT(ADDRESS())` together with functions that take two range as parameters?
Thank you
P.S.
For curiosity, I tried also with the same version of libreoffice on windows and with excel 2016 and both do not work.erotavlasThu, 21 Sep 2017 10:29:15 +0200https://ask.libreoffice.org/en/question/131818/CALC: Reference Cell Data on different Sheets based Day of the Weekhttps://ask.libreoffice.org/en/question/125762/calc-reference-cell-data-on-different-sheets-based-day-of-the-week/I am working in LibreOffice Calc v5.3.0.3 and I am looking for some assistance on being able to Reference/Return Cell Data on one Sheet based upon the Day of the Week (Sun, Mon, etc).
I have multiple Sheets that range: WEEK 1, WEEK2, .... WEEK 12, WEEK 13 that contains sales data for that specific Week. Contained within each week's sheet are the following Columns: Day of the Week (A7:A14), Daily Sales (N7:N14) and Number of Customers (BH7:BH14).
On a separate Sheet called WTD, I would like to Reference, based on the current Day of the Week, the Sales and Number of Customers for that specific day of that week.
I am using a Cell on the WTD Sheet called Current Week (WTD.I8) that contains an Integer representing the week number.
Example: 3
Entering an Integer between 1 and 13 allows me to Reference Specific Sheets based upon this number.
I utilize this integer along with functions such as INDIRECT.
Example: =INDIRECT("Week "&WTD.$I8-1&".N14")
In the above example it will return the Previous Weeks (Week 2) Total Sales (N14) since I8 = 3
I am stumped on finding a way to Return a Cell value based upon the Current Weeks number and Day of the Week.
Example:
Current Week: 3
Today's Sales - Last Week: $XXXX (Returns the Value on Sheet: Week 2 for today (Wed).
I hope that I have not made this too confusing to understand.
Thanks for reading...
BerniebkucharskiWed, 23 Aug 2017 20:15:45 +0200https://ask.libreoffice.org/en/question/125762/=INDIRECT(A4 & ".O$17") doesn't workhttps://ask.libreoffice.org/en/question/111921/indirecta4-o17-doesnt-work/I'm working with LO Calc v. 5.3.4.2.
The O.S is Windows 10 on an MS Surface.
I don't have enough points to upload a file, so I hope this description works.
On the first sheet, I call it "Master" I have a column of sheet names. ABC, DEF, GHI, etc.
The remaining sheets (ABC, DEF, GHI etc), each has data. I'm interested in the same address on each sheet, C4
Suppose C4 in ABC is 12, in DEF is 23, in GHI is 57 etc.
Then I want to end up with a table in Master, col.A the sheet names, col.B. the C4 data (12, 23, 57 etc.)
I can achieve this by entering =ABC.C4 in col.B beside the ABC in col.A, DEF.C4 in col.B beside the DEF in col.A etc.
However this is quite tedious if I have 50 sheets.
I have tried variations of the Indirect function, but none work. So what should I do?
As a related question, does Calc (or Excel for that matter) offer an "Execute" function. i.e. that evaluates a string and then, well, executes it? Then I could just enter =Execute(A4&"C$4")LCL999Thu, 20 Jul 2017 03:33:20 +0200https://ask.libreoffice.org/en/question/111921/More subcategories for existing dependent dropdown list - Calchttps://ask.libreoffice.org/en/question/111924/more-subcategories-for-existing-dependent-dropdown-list-calc/ I am a linux user (Fedora), I was given a .xls made in Windows that had categories and subcategories, the data sheet was arranged so that the first entry in each column was the category, and all the entries below it the subcategories.
The important part in the spreedsheet (Not the same where the data was placed) was that you could choose from a drop-down list a category and then in the cell next to it choose the subcategory from another drop-down list.
My problem is I need to add another new category and subcategories but cant seem to do it if I just add the text the way it is arranged right now. Can choose the new category from the list but when choosing the subcategory #REF appears.
Have read all the entries here and in another forum and they all say the validity check for cell range but cant seem to find a way to make it work. I can make it so that if i choose the cell range of the subcategores then it shows me everything, but dont know how to make it so it only shows the subcategories of the category that was chosen . All i can tell you for sure is that in the Data/Validity for the subcategory drop-down list is in the way of INDIRECTO($previouscell), and the previouscell is the one were you picked the category.
Hope everything was clear enough, I really dont know much about excel or libreoffice calc. I have been busting my head reading and just exploring options and cant seem to figure it out.moodmaThu, 20 Jul 2017 06:07:30 +0200https://ask.libreoffice.org/en/question/111924/Display Cell as Blank and not #REF!https://ask.libreoffice.org/en/question/85439/display-cell-as-blank-and-not-ref/Hello,
I have a sheet that brings in stats from other sheets. Problem is I need to create the sheets down the road. Sheets are named say 00001 then 00002 and so on. Right now I'm using =INDIRECT(B5&".G16")
This formula checks B5 and if that sheet name exist say 00001 then it will place the content from sheet 00001.G16 in that cell.
If the sheet does not exist it displays #REF! after the sheet is created it will populate correctly. What can I add to the formula to make it just stay blank till the sheet is created? I was having a problem with isblank not sure if I'm entering it wrong or if it does not work in this situation.
Thank You!bdennThu, 12 Jan 2017 09:41:57 +0100https://ask.libreoffice.org/en/question/85439/calc - function to define cellrangehttps://ask.libreoffice.org/en/question/82194/calc-function-to-define-cellrange/
INDIRECT(prefix & "0" & suffix & "." & myROWA & ":" & myROWA)~INDIRECT(prefix & "1" & suffix & "." & myROWA & ":" & myROWA)~INDIRECT(prefix & "2" & suffix & "." & myROWA & ":" & myROWA)
where
prefix: mySheet_
suffix: _A
myROWA: B
are stored in named cells on a sheet
Can I somehow turn this into a "lambda function"?
e.g. let's say ``myROWA`` is stored in ``$E$11`` what I want is
data3(E11)
which desugars into
INDIRECT(prefix & "0" & suffix & "." & B & ":" & B)~INDIRECT(prefix & "1" & suffix & "." & B & ":" & B)~INDIRECT(prefix & "2" & suffix & "." & B & ":" & B)
(because ``E11`` stores ``B``)
now if ``E12`` stores ``F``, then ``data3(E12)`` should desugar into
INDIRECT(prefix & "0" & suffix & "." & F & ":" & F)~INDIRECT(prefix & "1" & suffix & "." & F & ":" & F)~INDIRECT(prefix & "2" & suffix & "." & F & ":" & F)DiesNutsSun, 20 Nov 2016 17:14:08 +0100https://ask.libreoffice.org/en/question/82194/calc - vary column in indirecthttps://ask.libreoffice.org/en/question/81298/calc-vary-column-in-indirect/Assume sheets that have some values on columns ``B-K``.
Each sheet has
R2=AVERAGE(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))
R3=STDEV(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))
for, say,
O2=1436
O3=10260
How can I write the indirect s.t. I can drag it right to sumarise the other columns?
i.e.
S2=AVERAGE(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3))
S3=STDEV(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3))
T2=AVERAGE(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3))
T3=STDEV(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3))DiesNutsMon, 07 Nov 2016 11:09:40 +0100https://ask.libreoffice.org/en/question/81298/calc - filename from cellhttps://ask.libreoffice.org/en/question/81237/calc-filename-from-cell/Let's say I have sheets "myStuff_10", "myStuff_30", ... imported
on the A column, I have
10
30
50
70
...
and in the C column, I want to have
='myStuff_10'.$B$5
='myStuff_30'.$B$5
...
How can I take that value from the A column, though?
I.e.
='myStuff_<take from A column>'.$B$5
='myStuff_<take from A column>'.$B$5
...
something like (doesn't work, ERR:501):
=INDIRECT("myStuff_"&A3).$B$5
such that I can drag it down rather than manually adjust a thousand cells?DiesNutsSun, 06 Nov 2016 14:05:54 +0100https://ask.libreoffice.org/en/question/81237/Calc Crash on deleting row containing indirect() formulahttps://ask.libreoffice.org/en/question/67512/calc-crash-on-deleting-row-containing-indirect-formula/How to produce the bug:
1) Enter A1 in cell A3. Enter B1 in cell A4, Enter C1 in cell A5.
2) Enter formula =indirect(A3) in cell B3. Enter formula =indirect(A4) in cell B4. Enter formula =indirect(A5) in cell B5.
At this point there should be A1, B1 and C1 in column A, and zeros in column B
3) right click row 5, delete selected rows.
LibreOffice Calc crashes.
Version: 4.4.1.2
Build ID: 40m0(Build:2)
Locale: en_US
O/S: Ubuntu 12.04, 3.2.0-58-generic-pae
spyong.hslThu, 31 Mar 2016 04:07:58 +0200https://ask.libreoffice.org/en/question/67512/SUM(...) give 0 when the contents are INDIRECT(...) because they are not numerichttps://ask.libreoffice.org/en/question/66378/sum-give-0-when-the-contents-are-indirect-because-they-are-not-numeric/In cell F27 I have this formula: =INDIRECT(C27,0) Value: $550
In cell C27 I have this: =CONCATENATE("R",ROW(),"C",3*ROW()-6) Value: R27C75
I have a whole column like that.
When I sum it, I get zero.
It should be the sum of the numbers that I am seeing as a result of the indirect references.
https://imgur.com/klb1rLz
(It started when I tried to paste HTML from my bank's website, and every row was shifted over 3 from the row before it)
(I have found that I could use OFFSET and it would simplify the situation considerably)
(Apparently this is more fun than doing my taxes!)
A lame attempt at anonymitySun, 13 Mar 2016 17:48:32 +0100https://ask.libreoffice.org/en/question/66378/Trying to use vlookup with indirecthttps://ask.libreoffice.org/en/question/66330/trying-to-use-vlookup-with-indirect/ I have a recipe costing sheet which has a combo box which allows a user to select between vendors. The combo box names correspond to a sheet names for the various vendors and I am trying to do a vlookup to search a range of fields for the name of the product and return its cost per unit.
In other words, you would select a vendor from the combo then to get the per unit cost you would do the vlookup on the vendor sheet (using Indirect) and reference the item name to find the per unit cost and return that value.
Below is the formula I am using:
=VLOOKUP(D4,INDIRECT("'"&E4&"'!&A60:I80"),9,0)
It currently returns a #ref! error and I can't seem to figure out what is causing it.
I don't know if its a syntax error or if maybe there is a difference between excel and office. I used a couple of different examples from videos to no effect.
Anyway, any help would be greatly appreciated.
Jim
manwencSun, 13 Mar 2016 02:08:52 +0100https://ask.libreoffice.org/en/question/66330/How to extend a formula using INDIRECT ?https://ask.libreoffice.org/en/question/64563/how-to-extend-a-formula-using-indirect/ I want to reference a column from another sheet, with the other sheet name configurable, so I use this formula:
=INDIRECT($K2 & ".C2")
with:
* K2 the cell containing the other sheet name
* C the column from the other sheet
How to extend the formula to the whole column?
Since "C2" is a string, calc takes "K" as the variable and not "C"...nicoulajTue, 16 Feb 2016 20:48:51 +0100https://ask.libreoffice.org/en/question/64563/Is there any way to shorten this formula?https://ask.libreoffice.org/en/question/60256/is-there-any-way-to-shorten-this-formula/It's just an INDIRECT and then a bunch of INDIRECTS within consecutive OFFSETS:
=INDIRECT("Main."&CELL("address"))&OFFSET(INDIRECT("Main."&CELL("address")),0,1)&OFFSET(INDIRECT("Main."&CELL("address")),0,2)&OFFSET(INDIRECT("Main."&CELL("address")),0,3)&OFFSET(INDIRECT("Main."&CELL("address")),0,4)
Since a lot of the formula is repeating, is there any way to shorten this? Even with a cheat or shortcut?
Here it is broken up into lines so you can see how repetitive it is:
=INDIRECT("Main."&CELL("address"))
&OFFSET(INDIRECT("Main."&CELL("address")),0,1)
&OFFSET(INDIRECT("Main."&CELL("address")),0,2)
&OFFSET(INDIRECT("Main."&CELL("address")),0,3)
&OFFSET(INDIRECT("Main."&CELL("address")),0,4)
For example, is there a way to put **INDIRECT("Main."&CELL("address"))** in cell A1 and then the formula would look much more condensed like this?:
=A1&OFFSET(A1,0,1)&OFFSET(A1,0,2)&OFFSET(A1,0,3)&OFFSET(A1,0,4)
Or even better something like this?:
=OFFSET(A1,0,0:4)
I know the last two examples don't work but maybe you'll see what I'm trying to accomplish.argh.pirateFri, 30 Oct 2015 04:11:55 +0100https://ask.libreoffice.org/en/question/60256/Need Sheet2 to always mirror Sheet1 but w/ some columns combinedhttps://ask.libreoffice.org/en/question/60213/need-sheet2-to-always-mirror-sheet1-but-w-some-columns-combined/I want Sheet2 to be an exact copy of Sheet1 except I want some columns from Sheet1 combined in the Sheet2 version. For example:
![image description](http://i.imgur.com/RYiOuhm.jpg)
The problem is I need it to be able to work when I add and remove rows from Sheet1. So when I use the method `=CONCATENATE(Sheet1.A1,Sheet1.B1,Sheet1.C1)` and add a line on Sheet1 between Rows 1-2, the references on Sheet2 update and the new line is non-existent on Sheet2.
I also need empty cells to simply be empty. Not **0** or **#REF!** That way, when I have something like "Theodore Roosevelt" who has no middle name, I don't want it to show as Theodore0Roosevelt or Theodore#REF!Roosevelt
I found this method that seemed to work but I had to place the coordinates in quotes for it to work and that prevented me from being able to use the AutoFill Series feature. I don't know a thing about INDIRECT so that might be why.
`=CONCATENATE((INDIRECT("Sheet1.A2")),(INDIRECT("Sheet1.B2")) ,(INDIRECT("Sheet1.C2")))`
argh.pirateThu, 29 Oct 2015 06:28:21 +0100https://ask.libreoffice.org/en/question/60213/Convert Excel multi function formula to Calchttps://ask.libreoffice.org/en/question/41533/convert-excel-multi-function-formula-to-calc/=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$15&"'!$A$2:$A$33"),$A19,INDIRECT("'"&$A$1:$A$15&"'!D$2:D$33")))
The formula works in Excel but in Calc it's giving sporadic results - which indicates to me the syntax isn't completely incorrect.
I have the profile formula set to Excel A1.
Cells A1:A15 contain the names of 15 different sheets I'm wanting to search and total the amounts.
Cell A19 contains the value to search each sheet. The values being search are in A2:A33 on each sheet and the amounts being added in each sheet are in D2:D33.
I'm not getting any errors and I haven't been able to determine a pattern in the results but some results are coming from different sheets but none are totaling from multiple sheets. I initially thought there could be an issue with the cells being different formats so it wasn't finding the values but all appear to be the same.
Any suggestions would be appreciated.
NewerCalcUserThu, 23 Oct 2014 22:17:20 +0200https://ask.libreoffice.org/en/question/41533/How can I use nested VLOOKUPs?https://ask.libreoffice.org/en/question/36765/how-can-i-use-nested-vlookups/I'm trying to give the lookup array for a `VLOOKUP` as a named array coming from another `VLOOKUP`.
Namely:
1) This works: `VLOOKUP(C2, VendorExams, 3, 0)`
I get as output `CAT_exams` which is the name of an array.
2) This works too: `VLOOKUP(C2&D2, CAT_names, 4, 0)`
I get as output the desired cell value from the `CAT_names` array.
3) Nesting them with (or without for that matter) `INDIRECT`, doesn't work: `INDIRECT(VLOOKUP(C2&D2, VLOOKUP(C2, VendorExams, 3, 0), 4, 0))`
I get an 504 error which indicates to me that the nested `VLOOKUP`s output is not understood.
Is this not-working-as-intended, a bug or am I doing something in a wrong way?
Using 4.2.3 if it has any relevance.BgsFri, 11 Jul 2014 21:15:53 +0200https://ask.libreoffice.org/en/question/36765/Error using INDIRECT in a LOOKUP functionhttps://ask.libreoffice.org/en/question/35865/error-using-indirect-in-a-lookup-function/I am having a problem in using INDIRECT in a LOOKUP function in Calc 4.1.3.2 in Linux (Kubuntu 13.10).
In my worksheet, I have column of values (0-255) in column A, in the cell range A3:A258. Column B has a corresponding set of values (which are image grey scale pixel counts in this case).
From this data, I want to do some anaylsis based on maximum and minimum values in column B. However the nature of the data is that can be more than one maximum/minimum in the data in column B so I want to set a range of cells to find local maximum/minimum valies, rather than simply looking at the entire range of cells B3:B258. For example, if I know that there is a local maximum around cell B200, then I want to set the search range to B180:B220 or similar.
Once the local maximum/minimum has been found (that part of the process works just fine), and is placed into cell B268, I want to use that value in my LOOKUP to tell me what the corresponding grey scale value in column A is.
The formula I am trying to use looks like this:
<strong>=LOOKUP(B268,INDIRECT("B"&B266):INDIRECT("B"&B267),INDIRECT("$A$"&B266):INDIRECT("$A$"&B267))</strong>
where:
cell B268 holds the local maximum/minimum value,
cell B266 holds the lower bound of the row in the range of cells in column B where the max/min value is,
cell B267 holds the upper bound of the row in the range of cells in column B where the max/min value is
So I want the above function to effectively translate for example into:
=LOOKUP(B268;B180:B220;$A$180:$A$220)
The absolute cell references are necessary for column A as there are multiple data columns which will change as I copy the formula across into the data columns but they will all be matched against column A.
I hope that all makes sense.
When I try to use the above formula, I get a #N/A error and don't know why. What am I doing wrong? Any advice would be helpful.
Thanks in advance and sorry if this question is a bit long.joe.aquilina.92Mon, 23 Jun 2014 11:14:44 +0200https://ask.libreoffice.org/en/question/35865/How to define a print range with INDIRECT()?https://ask.libreoffice.org/en/question/30718/how-to-define-a-print-range-with-indirect/Hi,
I'm using LibreOffice 4.2.1.1, English and I want to define a print range using indirect:
=INDIRECT($Sheet1.$A$1)
1. I tried to insert it directly
in the Edit Print Range, didn't
succeed -> Invalid sheet reference.
2. I defined a name (Insert - Names -
Define) with Range:
=INDIRECT($Sheet1.$A$1), Scope: Document (Global), Range Options:
Print range The Name doesn't show up
in the Edit Print Range...
What am I missing?
Thanks
jeneWed, 05 Mar 2014 09:57:53 +0100https://ask.libreoffice.org/en/question/30718/{Can I use INDIRECT to create different named ranges on diffent Calc sheets?[Solved]https://ask.libreoffice.org/en/question/28818/can-i-use-indirect-to-create-different-named-ranges-on-diffent-calc-sheetssolved/I would like to use a single LibreOffice macro to sort different ranges of data on different sheets.
For example, on Sheet 1, I want to sort, A1:B10, but on Sheet 2, I want to sort A1 to B12; in both cases, using Column A as the key.
I have tried making named ranges such as $A$1:INDIRECT(S1) where S1 contains the text $B$10, but the name doesn't appear in the named ranges list.
As an experiment, I also tried a named range of a single cell INDIRECT($S$1), which I know works in a cell formula, but the name still doesn't appear in the list.
Am I doing something wrong, or is this just not possible?ThailandianFri, 24 Jan 2014 21:07:31 +0100https://ask.libreoffice.org/en/question/28818/Error: when use indirect formula to link external datahttps://ask.libreoffice.org/en/question/22717/error-when-use-indirect-formula-to-link-external-data/I found data link error when I try to open file that contain manual link with function INDIRECT.
In LO 4.0.4,4.0.5, 4.1.1
example.
I use file name and address in cell B2 ('HostLink.ods'#$Sheet1.A1), the B3 formula "=INDIRECT(B2)",
I place all file in c:\My Document
After I open file cell B3 return "#REF!". With error message
> "The following external file could not be loaded. Data linked from this file did not get updated.
> file:///D:/DATA/HostLink.ods"
However, after I made chang with file (input value in cell / Show/Hide Column)
B3 will link data from source correctly.
I 'm sorry, i can't attach file
ChansitTue, 17 Sep 2013 03:42:48 +0200https://ask.libreoffice.org/en/question/22717/