Posts by JoFo

Re: Formula Too Big For Nesting

Fantastic - thank you Ian - that's exactly what I needed

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.

Re: Undo Option Lost After Event Macro Code

Wow! Thanks very much - I shall give that a go (alternatively, I shall get my colleagues to be more careful when they are adding in information )

Hi,

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: http://www.ozgrid.com/forum/showthread.php?t=89607

and the Macro used is:

Private Sub Worksheet_Change(ByVal Target As  Range) 
    Dim c As Range 
    Application.EnableEvents = False 
    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 
        If c.Column > 1 And c.Column < 18 Then 
            Cells(c.Row, 1) = Now 
        End If 
    Next c 
    Application.EnableEvents = True 
End Sub

However...

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
Jo

Re: Message To Remind That Cell Is Mandatory

Absolutely fantastic! Thank you very much for all of your help

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Application.EnableEvents = False
    For Each c In Target
        If c.Column > 1 And c.Column < 18 Then
            Cells(c.Row, 1) = Now
        End If
    Next c
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(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

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

Many thanks for your help
Jo

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?
Cheers
Jo

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

Hi,
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:

Sub Print_Out() 
     
    With  Range("d6") 
        If .Value = "" Then 
            .Select 
             MsgBox ("Make sure you enter your surname") 
            Exit Sub 
        End If 
    End With 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
End Sub

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

Many thanks for your help
Jo

Re: Color Row Based On Fixed Cell & Relative Cells

Fantastic - thank you very much

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?

Hi,
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
Jo

Re: Automatically Date & Time Stamp Row On Change

It works - you are fanastic! Thank you very much
(and now I have dipped my toe in the scary world of the macro... )

Re: Automatically Providing The Date A Line Was Updated

Woah!
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
Jo

Hi,
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:
http://www.ozgrid.com/forum/showthread.php?t=48508

or

Quote

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
Jo

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

Thanks
Jo

ETA

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

Hi,
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:
IF('CellX'>=74.5<99.5,'CellY',0)

which ended up returning everything as True

and I've tried nesting them:

=IF('CellX;<99.5,IF('CellX'>74.5,'CellY',0),0)

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.
=SUMPRODUCT(($L$11:$L$17>=0)*($L$11:$L$17<0.495)*($O$11:$O$17))

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

Thank you everyone for your help!

Jo

Re: Dsum - Getting Value Error - Formula Error?

I thought that SUMPRODUCT was for multiplying things? I'm looking to add rather than multiply. Or have I got myself completely muddled?

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!