Posts by JoFo

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi - please feel free to change my title, or point me towards an already created answer - I know what I want to get out at the other end, but not how to express (succinctly!) how to do it!

    Part of my job involves analysis of bids that we are winning/losing, and how this is changing over time.

    I have a spreadsheet with overview information of a bid including the following details:
    Date the bid came in, Bid Sector (we have different business units). The bid information is on different tabs depending on whether the bid is current, no bid, lost or won. Within these tabs is then the information on what stage we are at/lost/won etc the bid.

    I have attached a cut down version of this spreadsheet (I've had to take out commercially confidential info, and also take out the no-bid tab, as this put it over the file size limit.)

    I need to create two tables (that I can then create graphs etc from);
    (1) showing a count of bids across all tabs by month that the bid came in (i.e. July 07; August 07 etc) broken down by business sector.
    (2) showing a count of bids across all tabs by month that the bid came in broken down by current stage (i.e. PQQ/ITT/Lost (PQQ)/Lost (Demo)/Won etc)

    Unfortunately, my (horribly complicated!) plan to run the date part of this through nested IFs has been stimied by the fact that I've got over 12 months in there (and this spreadsheet is only going to grow over the next X years...)

    Running a pivot off of the data doesn't work, either, as it looks at the full date, not the months. I have to have the full date in there (i.e. dd/mm/yyyy rather than mm/yyyy), as another part of the analysis is looking at how long it takes for a bid to go from coming in to being lost/won.

    So - my plea is to anyone who can give me a hand with sorting this out! :)

    Many thanks in advance for your help.


    I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.

    The final thread is here:

    and the Macro used is:


    We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!

    Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?

    Many thanks

    Re: Message To Remind That Cell Is Mandatory

    Ah - I see - I thought that the Macro name was like a file name, just something to keep it separate from the last one... (I need to go through the first section of the Excel Macro help course again...!)

    The two macros I've got are:

    The first macro is to put the date into column 1 whenever a line is edited.

    Many thanks for your help

    Re: Message To Remind That Cell Is Mandatory

    Thanks :) (as I said, I know nothing about VB...)

    I take it it doesn't matter what I call the macro, then?

    I changed the words Worksheet_Change to Worksheet_Date, which means I don't get the error.

    However, now nothing happens when I change one of the column K fields to 100 - Purchase Order In (apart from something which means that I lose the undo facility!)

    The code now reads

    Private Sub Worksheet_Date(ByVal Target As  Range) 
        Dim c As Range 
        For Each c In Target 
            If c.Column = 11 Then 
                If c.Value = "100 - Purchase Order In" Then 
                     MsgBox "Is the Month In correct?" 
                End If 
            End If 
        Next c 
    End Sub

    Where have I gone wrong?

    Re: Message To Remind That Cell Is Mandatory

    Hi Daniel - many thanks - how do I make that Column K? Is it a simple letter/number system (A=1, B=2, C=3 etc)?

    My knowledge of VB code is currently limited to how to copy and paste what someone else has given me! (I am trying to teach myself, but I'm not getting very far...)[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Also - I've tried that code (changing 6 to 11 so that it matches with column K), and when I test it, it comes up with the error:
    Compile error: Ambiguous name detected: Worksheet_Change

    What has gone wrong?

    Still on my sales spreadsheets :)

    We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.

    What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"

    Looking through previous answers, I think that this is something along the lines of what I need:

    However, I don't know enough about VB Code to know how to change it for my purposes...

    Many thanks for your help

    Re: Color Row Based On Fixed Cell & Relative Cells

    Thanks - that has worked for making (1) turn the whole row green, or leaving B15 blank when there is nothing in it, meaning (2) kicks in to turn it red.

    However, when $K$15 is not 100%, (and therefore there is no PO number, because there is no PO), leaving (2) as Cell Value is equal to 0 (turn the cell red) makes the cell red. Is there any way of this only turning red when K15 is 100% and B15 is blank?

    My sales spreadsheets have a column which is turned to 100% when the order comes in (i.e. when the salesman gets it in his commission). I have applied conditional formatting to turn the whole row green when this happens (for ease of seeing which orders are in)
    We also have a cell for the Purchase Order "number" (as these come from the customer, these can be a straight number sequence - "12345" or a mix of letters and numbers - "ABC12345"). My boss would like this cell to be red if the "order in" column is at 100%, but there is no PO number.
    However, when I put in the formatting:
    (1) =$K$15=1 (to turn the row green)
    (2) Cell Value is equal to 0 (to turn the cell red)

    it will apply (1) no problem, but will only apply (2) if (1) is false. (i.e. if (1) is true, the whole row goes green, with no red in the PO box.)

    If I switch them round (i.e. have (2) first), then I get the formatting that I want (i.e. green row with a red box), but if (1) is false, I still get a red PO box.

    What I need is a way of only applying (2) if (1) is true. (or any other way of only making the PO box red if the order is 100% but there is no PO number)

    Many thanks

    Re: Automatically Providing The Date A Line Was Updated

    I'm sorry - I'm a total newbie when it comes to macros - I have never used them before. How do I add the code to the sheet (I presume that it isn't pasting the code into the cell)? Do I need to keep the =Now() function in the A column?
    Many thanks

    I've searched on this topic, and I'm not sure if some of the answers given are relevant - if I have double posted, then I am sorry - please let me know!

    I work in a sales team, and am currently planning the new year of sales spreadsheets (tracking prospects and orders) These spreadsheets are linked in both directions to the Sales Director's summary spreadsheet (so that she can set the targets, and also provide a summary to the MD)

    She has asked me whether it is possible to put a formula in that gives the date each line (1 line = 1 prospect) was updated (so that she can see whether information in the line is current / a week old / a month old etc)

    Effectively, what I need is in column A a formula which references columns B-Q and if any of those change, puts in the date of change.
    Is this possible, or am I going to have to ask the sales guys to do it manually?

    I have tried to use the =Now() function, but, because the spreadsheets are linked, and auto-update on opening, every time the spreadsheet is opened, each line goes to today's date.

    The previous threads that I looked at suggested either:



    That is bad design, Enter =NOW() to a single name cell named cell and use date =MyD-T in ALL cells needing the Date & Time. Where MyD-T is the named cell.

    I would advise STRONGLY against using Manual Calculation as it's NOT good spreadsheet design and a mistake waiting to happen.
    Unfortunately, I don't understand either of those answers...!

    Many thanks

    Re: Copy Cells If Between Numeric Range

    Hi Dave,

    I'm really sorry - I must be really thick. I didn't understand a word of that! And I've looked at the "Advanced Filter" page that you linked to, and I don't understand a word that is on there, either!

    I've probably not explained myself very well.

    The original spreadsheet is set up so that there are totals at the top (rather than having totals at the bottom), and then, in row 4 there are the column headings including:
    Project Name; Services Cost; Software Cost; % chance; Total Value

    What my boss wants, on a completely separate spreadsheet (i.e. one that she can then work from without locking down the other one) is a copy across, again with column totals at the top, and then headings of:
    Project Name; 100% (Software); 100% (Services); 75-99% (Software); 75-99% (Services)

    and then the formula in so that if % chance in the first spreadsheet is 80%, the value of the software/services appears in the correct columns.

    Would what you have suggested work, and, if so, please could you explain it for a bear of very little brain?

    Many thanks!
    Jo[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OK {headdesk}
    I'm officially stupid - the nested function was working fine, it was just that I didn't realise that 75% = 0.75 to Excel - I was asking it to look for 75...

    This thread can be locked / deleted :)



    (and sorry about the double posting of the ETA - I still don't understand this forum's quirks!)

    I have to copy cells from one spreadsheet to another, but only if a range criteria is valid.

    I.e. If Cell X is between 74.5 and 99.5%, copy Cell Y. If not, return a value of 0.

    I can do this fine for the topmost value (100%) using If.

    However, I am completely stumped to do >75 <99

    I have tried:

    which ended up returning everything as True

    and I've tried nesting them:


    which ended up returning everything as false.

    Am I using the wrong function for the job?

    Re: Dsum - Getting Value Error - Formula Error?

    Quote from Derk

    Here is a sumproduct way to do it. The percent values can be replaced with cell references as desired.

    Thanks! : D
    It works and my boss is happy :cool:

    Thank you everyone for your help!


    Re: Dsum - Getting Value Error - Formula Error?

    Ok - using the test spreadsheet that I uploaded above (completely ignore the fact that I am using multiple tabs - I should be able to work that back from any formula given)

    I need to be able to get the total Prospects value (O9 down) when the Confidence of getting the order (L9 down) is set at different ranges:
    0-49.4%, 49.5-74.4%, 74.5-99.4% and 100%. The 100% I can do as a simple SUMIF, but I don't know how to get the ranges.

    IE - using the figures in the test spreadsheet, I need to be able to get the answers:

    0-49.4% = £18,750
    49.5-74.4% = £0
    74.5-99.4% = £6,500
    100% = £4,250

    Thanks for your help! :)