Posts by silverman166

    OK, thanks. The curly brackets are shown in my formula. I've been using arrays for some time now, so am familiar with their use.
    Re- .xls - you may have hit the nail on the head, as my version is certainly before 2010, originally 2003 but may have been updated to 2007, but certainly no later.
    Have you any suggestions for a solution with my early version, please?

    Re: Formula to sum content by particular categories


    Thanks for your reply. It's difficult to put into words, and I thought my spreadsheet would have illustrated the issue - obviously not.


    The requirement is to calculate the number of litres used between one occurrence of 'd full' to the next. When 'd full' occurs twice in one column, I've used a SUMIF statement successfully, but I don't know how to include entries from the previous column, such as occurs in the illustration for May and June.


    The figures are the same simply because I copied and pasted them, and are of no significance.


    May deliberately did not have a 'd full' to illustrate the problem I have when trying to calculate the total volume used since the last fill.


    I need some way of pointing to the last instance of 'd full' in order to be able to establish a starting point for the calculation, using only those occurrences of 'd' after that 'd full' but also including the next 'd full', to give a total usage, to include the latest litres added at 'd full'. This figure will then be used to calculate fuel consumption, knowing the mileage I will have done between fills.


    Hope this clarifies the requirements!

    I keep a spreadsheet detailing my monthly expenditure, on a running basis to fill a period of a year. Some of this expenditure is on diesel, represented by the letter 'd' in a column of other categories. I record the number of litres in the column to the right of this entry.
    I don't always fill up completely, but when I do I enter 'd full' with the litres used to fill up.
    I need to be able to show the total number of litres between fills, which may not occur within the same month.


    How can I do this, please?
    Hopefully the attached sheet will illustrate what I wish to achieve.


    Thanks.

    Re: Change formula to respond to one letter or same letter and others


    Thanks for that.


    After much pondering and experimenting with various things, I've now established the problem, which was that instead of inputting your formula from scratch, I was merely modifying the individual components in the formula already contained in the appropriate cell. It therefore seems to be necessary to enter a new formula, which has not proved a problem previously.
    Anyway, I am now able to do what I wished.


    Many thanks again for your help!

    The following formula is enclosed in 'sqiggly' brackets by using CtrlShiftEnter keys together :-


    [bfn]=SUM(IF(AL4:AL33="d*",AJ4:AJ33)) [/bfn] (I can't remember the correct terminology for the function, I'm afraid)


    Basically it's looking at a column of cells in range AL4:AL33 for the letter 'd,' then adding numbers found in column AJ4:AJ33 where each occurrence of the letter is found, and placing the results int he containing cell. I want the same to happen whether it's a 'd' or any word beginning with 'd'.


    Hope this is followable - I'm sure it must be an easy one, but my knowledge doesn't extend that far, I'm afraid! I tried using ..."d*", or ..."d****", but whilst the formula is accepted no results are placed in the cell containing it.

    I'm using Excel 2003.

    I have a spreadsheet in which in one column I input a value, and in the next column is a formula where the values are simply added together. If I copy the formula down this column, the last value is shown in this column, even when the first column shows no values.
    I would like to hide those results whilst leaving the formula in the cells.

    Re: When a particular word appears print in the next column the contents of another c


    Sorry for the delay.


    Fortunately rather than trying it straight away in my full spreadsheet, I had a go on the Example sheet I gave you. I did as you said, but whenever I try to enter anything into the Item column, I get the Error message shown in the attachment.(Sorry I can't upload the file. For some unknown reason the system goes through the procedure, but on Uploading, the message does not show in the Editor Window when the 'busy downloading' circle stops, but it said "Compile Error: Invalid outside procedure"). Interestingly, that same message appears even if I try to enter something outside the range.


    Out of interest can you tell me how to remove the whole sub-routine, please?

    Re: When a particular word appears print in the next column the contents of another c


    OK, thanks, that does work, but is somewhat tedious for the much larger spreadsheet I actual have, which is filled in on an ongoing basis.


    I was hoping that there would be a solution similar to the way Conditional Formatting allows a chosen colour, for instance, to be placed in a cell adjacent to any cell containing a specific word ie String in this case, for a specified range of cells?

    Re: When a particular word appears print in the next column the contents of another c


    OK, thanks, that does work, but is somewhat tedious for the much larger spreadsheet I actual have, which is filled in on an ongoing basis.


    I was hoping that there would be a solution similar to the way Conditional Formatting allows a chosen colour, for instance, to be placed in a cell adjacent to any cell containing a specific word ie String in this case, for a specified range of cells?

    I feel sure there must be a simple way to do this, but I'm not familiar enough with Excel to do it. Some form of Conditional formatting presumably is needed.


    In essence :- If a cell value = X, in the adjacent rh cell print the contents of a specific cell on the spreadsheet, otherwise do nothing. I want this to apply to the whole spreadsheet area, which will be filled in as time goes by, so that, as the word String is typed in, the contents of A2 (ie 1234 in the Example) appears in the adjacent cell automatically.


    Hopefully the Example sheet attached shows what I wish to achieve.


    I'm using Excel 2003, and am not familiar with how to use sub routines or macros, so please be patient with me!

    Re: When a particular word occurs, fill the adjacent cell a specific colour


    Whoa, there! Sorry you found that unfair - you seem to have incorrectly interpreted my comments. I was trying to say that I know the process may take some time, and that I'd appreciate anyone doing so, nothing more was intended or should have been implied.


    If it sounded as though I was discarding your reply, I certainly did not mean that , and again I sincerely apologise. In my defence, I did start out by pointing out my very limited current knowledge.


    As regards "you post in very general terms assuming someone else will understand exactly what you mean", no further clarification has been called for, and you have found a solution without such, apparently.


    To close, I wish not to fall out with anyone, and apologise if I have caused any problems. I merely wish for a solution, and assistance in applying such.