Posts by Kallia

Re: LIST and COUNT but with certain constraints

Thanks Shades :dance:

NOW for listing...Anyone Know??? :?

Re: LIST and COUNT but with certain constraints

:thanx: Do you know how to list them on another sheet as well, I am guessing it will have to be done using a macro...I know how to list the wins or losses but not necessarily how to add the filter of being greater than 2005-06-30

Re: LIST and COUNT but with certain constraints

Also if I wanted the opportunity revenue (column H) rather than the number of opportunities, what would be the change in formula???

Re: LIST and COUNT but with certain constraints

Cool Thank You Shades...I got it!!! :rock:
Do you know by any chance how to list them on a seperate page?

Re: LIST and COUNT but with certain constraints

SO I have tried this formula with my arranged ranges
=SUMPRODUCT(($J$2:$J$20="Wake")*($G$2:$G$20>"2005-06-30");$F$2:$F20) - I replaced the coma by ; for it to work and entered it as an array formula
But I get Zero as an answer, even when I should get some...
Also I don't see how it is choosing win vs loss or vice versa...should $F$2:$F20="Win"

Please view attachment for better understanding and responses. I would like to list and count how many wins and how many losses (column F) are particular to a certain GTM scenario (column J) but only recent ones starting July 1st, 2005 (2005-07-01) (column G). I would like to list these on each GTMs on page (Worksheets-Wake, snow, and beats). The data is found on the data worksheet. Thank You

Hi, I keep getting a message:
"! No more new Fonts may be applied in this Workbook"
It keeps coming up and then I have to restart my whole computer because even when I press "OK" or "X" it stays (the pop up message), I can't even continue any work...Why am I getting this message? What can I do?

Re: Change in data; comparing 2 workbooks

Hey so I modified the formula to fit my workbooks. This Month's Workbook is named 'July 4th,2005 GTM Report', and last month's is named '2005-09-06GTM Report'. The data sheet is actually called 'OPTY fActMgr gGTM Excel'. (I also changed the range to L500 because I have formulas on that sheet starting at 501). When I try to run the macro it says that there is an error. When I try to debug, it tells me that the error is the "orange writting". I have not changed that part of the macro...Any clue as to why this is happening?

Sub Developing_Opts()
'Written by Barrie Davidson
Dim FileToOpen As Variant
Dim LastMonth As Workbook
Dim LookupRange As Range
Dim Comparison As Range

Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename("Excel files (*.xls),*.xls", , "2005-09-06GTM Report")
If FileToOpen = False Then Exit Sub
With Sheets("OPTY fActMgr gGTM Excel")
Set Comparison = .Range("M2:M" & .Range("L500").End(xlUp).Row)
End With
Workbooks.Open FileToOpen
Set LastMonth = ActiveWorkbook
With Sheets("OPTY fActMgr gGTM Excel")
Set LookupRange = .Range("L2:L" & .Range("L500").End(xlUp).Row)
End With
Comparison.Formula = "=INDEX([" & LastMonth.Name & _
"]OPTY fActMgr gGTM Excel!" & LookupRange.Offset(, -6).Address & ",MATCH(L2,[" & _
LastMonth.Name & "]OPTY fActMgr gGTM Excel!" & LookupRange.Address & ",0))" Comparison.Value = Comparison.Value
Comparison.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 1))

LastMonth.Close False

Comparison.Offset(, 1).Value = Comparison.Offset(, -7).Value
Comparison.Offset(, 1).TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 1))

Comparison.Offset(, 2).Formula = "=IF(AND(N2-M2>0,N2<=0.2),TRUE,FALSE)"
Comparison.Offset(-1, -12).Resize(Comparison.Rows.Count + 1, 15). _
AutoFilter Field:=15, Criteria1:="TRUE"
Comparison.Offset(-1, -12).Resize(Comparison.Rows.Count + 1, 12). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Developing Opportunities ").Range("A1")
Comparison.Offset(, -12).Resize(, 15).AutoFilter
Comparison.Resize(, 3).ClearContents
Application.ScreenUpdating = True

End Sub

Hi I have 2 workbooks from 2 months of sales (see attachment) I would like to identify those opportunities that have moved from one sales stage to another. If sample 0 is last month and sample 1 is this month, how can I identify those opportunities that have changed sales stage but only up until the developing stage???
The data is found in each workbook in a sheet called data
The Sales Stage Name is column F of each data sheet
Opportunities are distinguised by their Opportunity ID, column L
I would like to list the opportunities that have changed from one stage to another in the past month (thus comparing workbook sample 0 to workbook sample 1) and list them in this month's (sample 1) workbook on the sheet named developping opportunities.
Remember only up to the develop stage (thus 2 scenarios possible...(1) Prospect 0% to Qualify 10% or (2) Qualify 10% to Develop 20%. Thank you

Re: Listing without duplicating

OK so I have my mappings Sheet and I wrote the macro...Everytime I try to run the macro it tells me that there is an error in mappings in column one. In fact when I try to add a column to your sample it tells me that there is an error in mappings in column three...I checked my spelling, the categories per row are the same...I don't get it...I noticed in the macro it says
Oops:
MsgBox "Error in mapping entries in column " & j
On Error GoTo 0
End Sub
I am guessing this has something to do with but can't quite figure it out

Re: List and count change in data

So my question is...this thread is getting so long I figured I would repost it:

Every month I get a list of our opportunities. What I would like is to find a way to count and list all the new opportunities that we gained in the month (compare this month's data sheet (sample 01) to last month's (sample 00)using the opportunity ID column). Also to count and list all the opportunities that are gone. I have attached samples, so lets say sample00 is the previous month and sample01 is this month. On the Ops Gained-Lost sheet of the sample 01 workbook is it possible to do this
Please refer to the attacments at the top of the page, thank you

Re: List and count change in data

Hi I just realized that the answer was always off, and I also got a little confused. First, I found a way for me to add columns so that is no big deal, if your next answer needs to have columns I could do that...but how can you put a 0 next to the opportunity ID in order to distinguish when the opportunity is gone...the new data set will not even show this opportunity...if it is gone it will not appear on the new data set, which means you cannot put a zero next to it. This might solve why there are mistakes in the solution for example Company NNN, Opportunity ID 2R-51BITU
is not a gone opportunity, if you look at both of my sample workbooks, it appears in both workbooks. Please explain this to me, for all I know maybe your macro brought back the gone data to analyse it but it just doesn't make sense :? ...the gained opportunities are right though

Re: Listing without duplicating

If its not too much to ask, do you think you could show me this macro (resend the same answer but have the macro run automatically) without the push button, so that I can then customize it to my data, thanks

Re: List and count specific data

Is it possible to do this without the transfer button, and simly a regular macro? When others view the report I don't want them to have to click on transfer but rather have the info out after having chosen to update all macros (I have a lot in the report)...if so could you resend it like that so I could read the macro and see how it is done, thanks

Hi please see attachment. I would like to list and count all the partners involved for each GTM scenario. (GTM scenario is column H on data sheet and Partner name is column J on data sheet). I would like to list these each on their own sheet according to the GTM scenario. For example how many partners (different names) there are for Activity- Snowboard and Who are they...of course all my data is fictional for privacy issues. Gracias

Re: Listing without duplicating

I have used macros, in fact I don't mind macros, I just never used this push button to extract...is it necessary, or can I do it with an ordinary macro that normally I just have to adjust the number of rows each time and enable them when I open the document

Re: Listing without duplicating

My current data is so complex that it would be preferable to find a formula that I could simply insert into my present workbook. Also, is there a way to do this without having to push the icon in order to extract the data each time? I would like this to be as automated as possible as I will have to generate new data each month and also have others view the final report without having to go back to the spreadsheet and push to view results

Hi
I am working on a project where I get new data every month. I have created all these formulas (many with your help) to retrieve certain data. I was wondering though, becuase each month we have a different number of ooportunities, meaning a different number of rows...Is it possible to formulate formulas so that you don't have to go back and change the number of rows in each formula, each time? The project is getting pretty complex, and this takes a lot of time to do and leaves more room for error, also I will not always be in charge of this so I want it to be easy for the person who takes over after me

Re: Listing without duplicating

I mean my range was A1:K3

Re: Listing without duplicating

OK I am new at this so bare with me...I wrote all my 3 colums in a new sheet called mappings. Then I went to advanced filters and it took all 3 rows and all columns (in my case A1:K1) Then I wrote my macro. When I press run it says that I have a mistake in my first column...I never did this advanced filter stuff and I don't know how you got that box that you press on to extract the data...I am a little cnfused :confused: