First time here? Check out the FAQ!

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

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 |

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 |

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.