Search found 1873 matches

by squenson
Thu May 29, 2014 9:39 am
Forum: Calc
Topic: Cell protection issues
Replies: 1
Views: 308

Re: Cell protection issues

Your document has certainly been corrupted and over the years the corruption has been spread on most of the cells. I suggest that you open a new, blank document and copy the content of the cells of the original document to the new one.
by squenson
Fri May 02, 2014 6:50 pm
Forum: Macros and UNO API
Topic: Combining info from two spreadsheets
Replies: 3
Views: 742

Re: COMBINING INFO FROM TWO SPREADSHEETS

OK, here is a solution using VLOOKUP. I was not sure which "9" you wanted, so I made two variants, one with the first number (after p/n), one with the last. If you have questions or if I misunderstood your requirements, let me know!
by squenson
Fri May 02, 2014 4:53 pm
Forum: Macros and UNO API
Topic: Combining info from two spreadsheets
Replies: 3
Views: 742

Re: COMBINING INFO FROM TWO SPREADSHEETS

We would need a few more details (you could attach an example of the columns with fake values). The function VLOOKUP should contribute to a solution, I think you don't need a macro for this. In case there are more than one row with the availability, you may need to build a pivot table first, to summ...
by squenson
Thu May 01, 2014 1:20 pm
Forum: Calc
Topic: [Solved] Test to see if cell holds string using macro
Replies: 8
Views: 3291

Re: Test to see if cell holds string using macro

This test comparing Cell.Value and Cell.String seems to work, except if the cell contains the string "0". Try it by opening a new sheet and putting a number or a string in A1. Sub Main oSheet = thisComponent.getSheets.getByName("Sheet1") Cell = oSheet.getCellByPosition(0, 0) If C...
by squenson
Thu May 01, 2014 12:15 pm
Forum: Calc
Topic: [Solved] Test to see if cell holds string using macro
Replies: 8
Views: 3291

Re: Test to see if cell holds string using macro

So, as input, you want to provide a range of cells, what should be the output? Consider the cases where there are 0, 1, 2 or more cells containing a string in the specified range. Do you want the string(s) as output, or the reference(s) of the cell(s)?
by squenson
Thu May 01, 2014 10:01 am
Forum: Calc
Topic: [Solved] Test to see if cell holds string using macro
Replies: 8
Views: 3291

Re: Test to see if cell holds string using macro

There is a function =ISTEXT(<cell>) which returns TRUE if <cell> contains a string. Could it fit your needs?
by squenson
Fri Apr 18, 2014 10:43 am
Forum: Calc
Topic: Trying to setup RANK with ties
Replies: 1
Views: 281

Re: Trying to setup RANK with ties.

You could make a column with the sum of points + #of10 / 1000. This would give:
3069.092
3062.078
3061.084
3061.081
3031.084
If you use RANK on this column, everything should be OK.
by squenson
Sun Mar 23, 2014 10:09 pm
Forum: Calc
Topic: [Solved] Text color changed on row
Replies: 10
Views: 1456

Re: Text color changed on row

Select the blank cells then apply the default formatting.
by squenson
Sun Mar 23, 2014 2:23 pm
Forum: Calc
Topic: [Solved] Not all attributes could be read
Replies: 2
Views: 533

Re: Not all attributes could be read

Is your signature correct? I would recommend that you upgrade to a production version a.s.a.p. as the error message is most probably already corrected.
by squenson
Sun Mar 23, 2014 11:55 am
Forum: Calc
Topic: [Solved] Text color changed on row
Replies: 10
Views: 1456

Re: Text color changed on row

Select your range of cells, then press F11 to show the "Style and Formatting" pop-up window. Double-click "Default" to clear all formatting in the selected range.
by squenson
Sat Mar 22, 2014 9:13 pm
Forum: Calc
Topic: [Solved] Function to convert "1" to "A", "2" to "b", etc
Replies: 3
Views: 1383

Re: Function to convert "1" to "A", "2" to "b", etc

Each letter has a corresponding value: A=65 and a=97. (Do =CODE("A") or =CODE("a") to get these values). Therefore, using the function CHAR, which works the opposite way, =CHAR(65) returns "A". Your function will therefore be:
=CHAR(myNumber+96)
by squenson
Sat Mar 22, 2014 9:01 pm
Forum: Calc
Topic: [Solved] Combine data in separate columns into one column?
Replies: 5
Views: 1104

Re: [Solved] Combine data in separate columns into one colum

Yes, this is the best solution, well done!
by squenson
Sat Mar 22, 2014 9:33 am
Forum: Calc
Topic: [Solved] Combine data in separate columns into one column?
Replies: 5
Views: 1104

Re: How do I combine data in separate columns into one colum

Let's say that you have the three columns A, B and C, starting from row 2, containing such data. In cell D2, put the formula:
=A2&" "&B2&" "&C2
Then copy this formula down the entire column D, up to the end of your list.
by squenson
Wed Mar 12, 2014 9:12 pm
Forum: Calc
Topic: [Solved] Complex 'CONCATENATE' + 'Exclusion' Formula needed
Replies: 2
Views: 649

Re: Complex 'CONCATENATE' + 'Exclusion' Formula needed

=A3&E3&B3&E3&IF(C3=0;"";C3&E3)&D3
by squenson
Fri Feb 21, 2014 10:24 pm
Forum: Calc
Topic: [Solved] If, then, else >< syntax
Replies: 8
Views: 1689

Re: If, then, else >< syntax

Order of the formula: in fact, it doesn't matter, I could have written it: =IF(C7>0;IF(C7>6;((C13*400)+700+10000),(C7*400)+700);0) Final 0: If C7 is 0 or negative (how about spending minus 2 days at the hospital?), instead of returning 0 it would return FALSE, which is less intuitive in my opinion.
by squenson
Fri Feb 21, 2014 9:15 pm
Forum: Calc
Topic: [Solved] If, then, else >< syntax
Replies: 8
Views: 1689

Re: if, then, else >< syntax

You could write:
=IF(C7>6;((C13*400)+700+10000);IF(C7>0;(C7*400)+700;0))
The red part corresponds to the ELSE clause of the first IF (when C7<= 6)
And the final 0 is when C7 <= 0
by squenson
Sat Feb 15, 2014 2:18 pm
Forum: Calc
Topic: Can't open .xlsm files
Replies: 3
Views: 4356

Re: Can't open .xlsm files

You may get a better result with LibreOffice 4.2 but I cannot guarantee it!
by squenson
Thu Feb 13, 2014 9:05 pm
Forum: Writer
Topic: [Solved] Mixing justifications on the same line?
Replies: 8
Views: 2817

Re: Mixing justifications on the same line?

On the left edge of the window, at the level of the ruler (click View > Ruler to eventually display it), there is a "|_" symbol. Click several times on it until you get a "_|_". Then click on the ruler in the middle, to insert this symbol. Click again on the "_|_" symbo...
by squenson
Sun Feb 09, 2014 1:35 pm
Forum: Calc
Topic: [Solved] Cell adding - formula problem
Replies: 4
Views: 893

Re: cell adding - formula problem

Welcome to this forum dhalic! When you add a cell just above or below the first or last row of a range, you have these effects. I suggest that you change your range and include the cell E11 in your count (and eventually decrease the result by one). This way, any cell added between E12 and E13 or jus...
by squenson
Wed Feb 05, 2014 4:27 am
Forum: Calc
Topic: Could i pay someone to restore a Calc file before it was....
Replies: 2
Views: 420

Re: Could i pay someone to restore a Calc file before it was

For the time being, no need to pay! Try this: 1. Make a copy of the document and don't open the original nor the copy with Calc because any opening may delete old information! 2. Find on your hard disk a file with the same name but a different extension. If you find it: i) make a copy as myfile-copy...
by squenson
Tue Feb 04, 2014 9:02 pm
Forum: Writer
Topic: [Solved] Cannot remember bullet's formatting in .DOC format
Replies: 10
Views: 1457

Re: [Solved] Cannot remember bullet's formatting in .DOC for

... and don't forget to update your signature, it still shows OpenOffice 4.0.1 :-)
by squenson
Tue Feb 04, 2014 9:00 pm
Forum: Calc
Topic: [Solved] Blank cells yield #Value in Formula
Replies: 13
Views: 6019

Re: [Solved] Blank cells yield #Value in Formula

... and don't forget to update your signature, it still shows OOo 3.3 :-)
by squenson
Sun Feb 02, 2014 9:53 am
Forum: Writer
Topic: [Solved] Cannot remember bullet's formatting in .DOC format
Replies: 10
Views: 1457

Re: Cannot remember bullet's formatting in .DOC format.

Welcome to this forum omiazad!

OpenOffice has sometimes some difficulties to write a file with the exact Word syntax. You could try LibreOffice 4.2 which has just been released and claims it has better .docx (unfortunately not .doc) support.
by squenson
Sat Feb 01, 2014 9:32 pm
Forum: Calc
Topic: [Solved] Blank cells yield #Value in Formula
Replies: 13
Views: 6019

Re: Blank cells yield #Value in Formula

Why don't you upload a sample, so we can check the document?
by squenson
Sat Feb 01, 2014 5:31 pm
Forum: Calc
Topic: [Solved] Blank cells yield #Value in Formula
Replies: 13
Views: 6019

Re: Blank cells yield #Value in Formula

I suggest that you try a more recent version of OOo or LibreOffice. With LibreOffice 4.1.3.2, blank cells are ignored by SUM formulas.

Question from me: why do you write =SUM(F339-D340+E340) and not simply =F339-D340+E340?
by squenson
Thu Jan 30, 2014 8:32 pm
Forum: Tables & Queries
Topic: Search acroos multiple tables?
Replies: 1
Views: 1034

Re: Search acroos multiple tables?

Unless you have very large tables -- and in such case I would really discourage you to use Base -- it is much better to create ONE table and, eventually, 12 queries which will present you the data per month. This way, the search will be on a single table. To answer your specific question, it is not ...
by squenson
Sun Jan 26, 2014 6:41 pm
Forum: Calc
Topic: Lost document when saving as .csv
Replies: 2
Views: 798

Re: lost document

Unfortunately, if you saved it as a .csv, OpenOffice only saved the active sheet. It seems you never saved the file before, so the situation is quite simple: nothing can be done.
by squenson
Thu Jan 23, 2014 10:27 pm
Forum: Calc
Topic: Block Copy?
Replies: 4
Views: 721

Re: Block Copy?

Strange... You mean that if you select a sheet, then press Shift and click on the last sheet, it has a different behavior? Most propably a bug, but I can't help you as I cannot reproduce it with LibreOffice.
by squenson
Wed Jan 22, 2014 8:41 pm
Forum: Calc
Topic: Help arrange cells
Replies: 2
Views: 610

Re: Help arange cells

If you are OK to put in A3-A6 the text "first sell", same for A10-A12 and A15-A18, you can then write in G2: =SUMPRODUCT(A2:A18=G$2;B2:B18=$F2;C2:C18) and copy this formula on the entire range G2:I11.
by squenson
Tue Jan 21, 2014 9:22 pm
Forum: Calc
Topic: Block Copy?
Replies: 4
Views: 721

Re: Block Copy?

It's there! Select the range you want to copy. Then, select the first sheet and while you press the Shift key, click on the tab of the last sheet, you will notice that tabs of all the sheets are highlighted. In the current sheet (the first destination sheet), select the destination cell and press Ct...