Ask Your Question

Cantagril's profile - activity

2019-10-20 19:52:01 +0100 received badge  Student (source)
2019-09-24 12:41:29 +0100 received badge  Famous Question (source)
2019-09-24 12:41:29 +0100 received badge  Notable Question (source)
2019-07-17 21:18:25 +0100 received badge  Notable Question (source)
2019-07-16 17:11:31 +0100 marked best answer Calc: VLOOKUP not returning result-no error message

Version: 5.1.1.3

My Spreadsheet contains two columns, one of which is a manually entered text string e.g ABCDE and the other a category, let's say ALPHA, BETA etc.... which I want to be displayed automatically.

I have created two further columns AC & AD) which contain the array with all the possible 5 letter strings and their respective categories.

My target cell formula is =VLOOKUP(C3;AC3:AD25;2;0) This doesn't work and does nor return an error message, merely being displayed in the target cell. I also tried FALSE instead of the 0 to no effect.

I'm obviously doing something stupid but I just can't see what.

2019-07-16 17:11:12 +0100 commented answer Calc - conditional formatting prob after update

Excellent! For some reason the first couple of times I clicked it stayed grey but now a beautiful grateful green :-)

2019-07-16 16:29:36 +0100 commented answer Calc - conditional formatting prob after update

I have already tried clicking on these check marks but nothing seems to happen - What behaviour should I see?

2019-07-16 16:27:31 +0100 marked best answer Calc - conditional formatting prob after update

I have just today upgraded from 5.1.6.2 to the latest version for windows - sorry don't have number as on another machine elsewhere atm.

I have a spreadsheet with extensive use of number formats as styles including N° of decimal places, e.g: formula is if value of C1=1 then apply 2 decimal places to 5 place number in target cell. All was working fine until I upgraded and now the decimal place change no longer works. I have messed around with this so much that I have just cleared out all direct and conditional formatting and started again from scratch (leaving the styles as they were)....now I can't reproduce the same result no matter the direct format of the target cell. Other aspects of the same style work fine such as cell background colour and font effect.

I have just now tried to get this to work on my machine with the 5.1.6.2 but with the same result.

So, my condition is C1=1 with the style to be applied as number of decimal places....I have tried changing the direct format from number to general with no decimal places to the max 5 but no joy.

I feel there must be something very basic that I'm missing but I can't see it.

EDIT: I have uploaded a sample as an attachment to my original question - which I hope will clarify things. To keep clutter to a minimum I have applied the conditional formatting to only the cells G3:G4 and H3:H4 using both a text cell and a number as examples of what isn't happening.

The specific result that I really want is that all numbers take the number of decimal places from column D where "FX" should result in a 5 decimal place number in the target cell and "IND" a two decimal place one. In my earlier functioning version, I was using 5 decimal places by default which were then modified by the condition Dx="IND" to a 2 decimal one.

C:\fakepath\SS_Cond_Format_Prob.ods

2019-07-16 16:05:50 +0100 received badge  Notable Question (source)
2019-07-16 15:02:04 +0100 commented answer Calc - conditional formatting prob after update

Thank you so much (again) as the problem is resolved. Curiously I did establish that one of the issues was direct format

2019-07-16 14:48:45 +0100 received badge  Popular Question (source)
2019-07-16 12:47:16 +0100 edited question Calc - conditional formatting prob after update

Calc - conditional formatting prob after update I have just today upgraded from 5.1.6.2 to the latest version for window

2019-07-16 12:33:38 +0100 edited question Calc - conditional formatting prob after update

Calc - conditional formatting prob after update I have just today upgraded from 5.1.6.2 to the latest version for window

2019-07-16 12:33:38 +0100 received badge  Editor (source)
2019-07-16 11:52:19 +0100 received badge  Popular Question (source)
2019-07-16 11:06:05 +0100 edited question Calc - conditional formatting prob after update

Calc - conditional formatting prob after update I have just today upgraded from 5.1.6.2 to the latest version for window

2019-07-16 11:04:21 +0100 asked a question Calc - conditional formatting prob after update

Calc - conditional formatting prob after update I have just today upgraded from 5.1.6.2 to the latest version for window

2019-07-12 20:48:00 +0100 received badge  Popular Question (source)
2019-07-12 20:32:59 +0100 commented question Calc: VLOOKUP not returning result-no error message

It works just like that!! I can't understand why mine doesn't but I'm in business and that's what counts, so thank you v

2019-07-12 18:15:31 +0100 asked a question Calc: VLOOKUP not returning result-no error message

Calc: VLOOKUP not returning result-no error message Version: 5.1.1.3 My Spreadsheet contains two columns, one of which

2019-07-12 14:20:45 +0100 commented answer Calc:Conditional formatting-How to stop empty cell being treated as zero?

Thank you so much!! This has been making my life a misery for the last couple of days - now my spreadsheet looks just t

2019-07-12 14:16:47 +0100 marked best answer Calc:Conditional formatting-How to stop empty cell being treated as zero?

Version: 5.1.1.3

Context:

I am using the numerical value of a cell in one column to change the background colour in the adjacent one using the conditional formatting tool.

I have 3 formulae which are working fine except when the source cell is empty. The "treat empty cells as zero" option is NOT turned on but the result of this formula returns the result as if it were zero. I have tried conditions using both a formula and a cell value to return either "" or a white background in the first and last positions in the condition ladder but no joy.

The formula is "IF((AND(P17>=0,P17<6)))" which works perfectly well for the value range in the source cells.

Any help would be very gratefully received:-)

2019-07-12 14:16:47 +0100 received badge  Scholar (source)
2019-07-12 13:17:39 +0100 asked a question Calc:Conditional formatting-How to stop empty cell being treated as zero?

Calc:Conditional formatting-How to stop empty cell being treated as zero? Version: 5.1.1.3 Context: I am using the num

2017-07-27 14:57:52 +0100 received badge  Famous Question (source)
2017-02-10 17:20:31 +0100 received badge  Popular Question (source)
2017-02-09 20:19:35 +0100 asked a question Sum of amounts by non-sequential date

I'm sure this probably very simple but I'm tying myself in knots with this...

I have two columns, one is dates and the other amounts. I want to add the amounts by date on a day by day basis with the result in two columns elsewhere - date next to the sum of the amounts for that date, in date order. . The only problem is that the dates in the source table are not sequential so e.g 04/02/17, 05/02/17/, 07/02/17, 04/02/17, 06/02/17, 04/02/17, 06/02/17, 05/02/17 etc etc.

All help gratefully received

2017-02-06 17:34:03 +0100 commented answer Calc: Is it possible to display the same number of decimal places as formulae input cells

Perfect! Thank you so much:-) I missed out one point that Mr Lupp above pointed out - in the circumstances I outlined, the "Regular" format would have sufficed. In fact, I had not added that I wanted the whole numbers displayed as 00000.0 and the following 4 digits not displayed unless showing a value. I have just altered the format to 0.0#### and this is just right for my purposes....and thanks for the link to the Number Format Codes Topic. All the best, C

2017-02-06 17:28:14 +0100 commented question Calc: Is it possible to display the same number of decimal places as formulae input cells

Thanks very much for your input Monsieur and you are, of course, absolutely right about the General format. I omitted to mention that I want to display a decimal point followed by a zero for the whole numbers, which I wasn't getting. Senor Kaganski (below) has provided with me the perfect answer for me in using "hashes" to replace the zeroes where I'd want them displayed. I do appreciate your prompt answer though.

2017-02-06 16:18:15 +0100 received badge  Notable Question (source)
2017-02-03 00:03:35 +0100 received badge  Popular Question (source)
2017-02-02 16:54:04 +0100 asked a question Calc: Is it possible to display the same number of decimal places as formulae input cells

For example, I'm subtracting a series of pairs of numbers in one column from another and displaying the result in a third. Some of these number pairs are five digits to one decimal place (e.g 17382.1) and others are less than 1 but to 5 decimal places (e.g 0.85467)

At the moment I have to set the cell format to 5 decimal places for the multi decimal place numbers to display correctly and this has the effect of displaying ALL the results to 5 places even when they are whole numbers - e.g 25324 is displayed as 25234.00000 and so on. This makes the column an unnecessary 10 digits wide with the redundant zeroes making the numbers messy and less easy to read.

Is there a way to: A) display only the number of decimal places as in the input cells? B) Limit the the total number of displayed digits to 6? C) Somehow reduce the column width to 6 digits wide without getting the hashes instead of a number?

As a beginner, any relatively polite suggestions will be most welcome.

Many thanks in advance