Forecasting using SUMPRODUCT and dates

I am having a problem with counting data that is within a certain date.
for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE
LEASEENDDATE.  I am currently using SUMPRODUCT to count the number o
servers I have for a particular location and type.

A                     B                C        D    
SERVERNAME  LOCATION  TYPE  LEASEENDDATE
Server1           MN             Web  6/30/2004
Server2           CA              App   8/15/2004

SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

On another sheet, I would like to forecast of how many servers I hav
that are not expired yet for every month of the year and have 1
columns for each month, JAN, FEB, MAR, etc.  I am thinking of a formul
like:

For January:
IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

For February:
IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

How do I ONLY count the number of 'Web' servers in 'MN' that have no
yet exprited as of (date in column D)?

Thanks

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 3:51:59 PM
excel.misc 78881 articles. 5 followers. Follow

15 Replies
1132 Views

Similar Articles

[PageSpeed] 27

Hi
try something like
=SUMPRODUCT((D2:D200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))

--
Regards
Frank Kabel
Frankfurt, Germany


> I am having a problem with counting data that is within a certain
> date. for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE,
> LEASEENDDATE.  I am currently using SUMPRODUCT to count the number of
> servers I have for a particular location and type.
>
> A                     B                C        D
> SERVERNAME  LOCATION  TYPE  LEASEENDDATE
> Server1           MN             Web  6/30/2004
> Server2           CA              App   8/15/2004
>
> SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> On another sheet, I would like to forecast of how many servers I have
> that are not expired yet for every month of the year and have 12
> columns for each month, JAN, FEB, MAR, etc.  I am thinking of a
> formula like:
>
> For January:
> IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> For February:
> IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> How do I ONLY count the number of 'Web' servers in 'MN' that have not
> yet exprited as of (date in column D)?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/9/2004 4:04:58 PM
Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:utOroxkHEHA.664@tk2msftngp13.phx.gbl...
> Hi
> try something like
> =SUMPRODUCT((D2:D200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > I am having a problem with counting data that is within a certain
> > date. for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE,
> > LEASEENDDATE.  I am currently using SUMPRODUCT to count the number of
> > servers I have for a particular location and type.
> >
> > A                     B                C        D
> > SERVERNAME  LOCATION  TYPE  LEASEENDDATE
> > Server1           MN             Web  6/30/2004
> > Server2           CA              App   8/15/2004
> >
> > SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > On another sheet, I would like to forecast of how many servers I have
> > that are not expired yet for every month of the year and have 12
> > columns for each month, JAN, FEB, MAR, etc.  I am thinking of a
> > formula like:
> >
> > For January:
> > IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > For February:
> > IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > How do I ONLY count the number of 'Web' servers in 'MN' that have not
> > yet exprited as of (date in column D)?
> >
> > Thanks!
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/9/2004 5:22:31 PM
Bob Phillips wrote:
> Frank,
>
> After our conversation with Norman earlier this week
>
> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))


:-)
yes I remember....
Frank

0
frank.kabel (11126)
4/9/2004 5:28:53 PM
Hi Frank and Bob!

A very productive week! Sorted out European fast date entry and 
discovered the uses of --"2004-04-10" date entry.



-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes.
"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:O5mFiglHEHA.3376@TK2MSFTNGP09.phx.gbl...
> Bob Phillips wrote:
>> Frank,
>>
>> After our conversation with Norman earlier this week
>>
>> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))
>
>
> :-)
> yes I remember....
> Frank
> 


0
njharker (1646)
4/9/2004 5:39:26 PM
Obviously VB works - I think you should send Frank and I a case each, and it
may work for us too.

Bob


"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:ucrydmlHEHA.2556@TK2MSFTNGP12.phx.gbl...
> Hi Frank and Bob!
>
> A very productive week! Sorted out European fast date entry and
> discovered the uses of --"2004-04-10" date entry.
>
>
>
> -- 
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:O5mFiglHEHA.3376@TK2MSFTNGP09.phx.gbl...
> > Bob Phillips wrote:
> >> Frank,
> >>
> >> After our conversation with Norman earlier this week
> >>
> >> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))
> >
> >
> > :-)
> > yes I remember....
> > Frank
> >
>
>


0
bob.phillips1 (6510)
4/9/2004 6:11:42 PM
Bob Phillips wrote:
> Obviously VB works - I think you should send Frank and I a case each,
> and it may work for us too.
>
> Bob

Bob
very good idea <vbg>

Norman: I think UPS can deliver this in a couple of days <ebg>
Frank

0
frank.kabel (11126)
4/9/2004 6:20:02 PM
Hi Frank!

I've got a couple of slabs, but it's like Guinness and just doesn't 
travel. You'll have to come and drink it here.

Bob's had too much already celebrating Arsenal's win!

I sent the European date solution to Chip complete with the 
attribution to VB (Victoria Bitter).
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes. 


0
njharker (1646)
4/9/2004 6:30:19 PM
Excellent.  That works.  Thank you very much.

In addition to this formula, how would I implement --within the sam
formula-- the automatic counting of of rows in my sheet?  ..instead o
choosing a range of  (for example) B2:B200, have my SUMPRODUCT formul
automatically count and utilize rows that are filled since the amoun
of rows of data in my sheet changes daily.  Also, there will always b
data in column A, no blanks.

Thx

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 6:54:28 PM
Will those rows have blanks or not? If not, just change B2:B200 to
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14glkq@excelforum-nospam.com> wrote in message
news:rerhart.14glkq@excelforum-nospam.com...
> Excellent.  That works.  Thank you very much.
>
> In addition to this formula, how would I implement --within the same
> formula-- the automatic counting of of rows in my sheet?  ..instead of
> choosing a range of  (for example) B2:B200, have my SUMPRODUCT formula
> automatically count and utilize rows that are filled since the amount
> of rows of data in my sheet changes daily.  Also, there will always be
> data in column A, no blanks.
>
> Thx.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/9/2004 7:21:21 PM
>>Will those rows have blanks or not? If not, just change B2:B200 t
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.


-> Column B will not have blanks, but others may have blanks.  I assum
it would not matter and I only need to count rows using data from on
column that has no blanks, correct?

Could I also put a range of B2:B65536 to cover the entire spreadsheet?

Thx

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 7:41:32 PM
"rerhart >" <<rerhart.14gnr6@excelforum-nospam.com> wrote in message
news:rerhart.14gnr6@excelforum-nospam.com...

> -> Column B will not have blanks, but others may have blanks.  I assume
> it would not matter and I only need to count rows using data from one
> column that has no blanks, correct?
>

Yes that is good enough.


> Could I also put a range of B2:B65536 to cover the entire spreadsheet?
>

You could but not recommended. It will imapir performance.


0
bob.phillips1 (6510)
4/9/2004 8:27:58 PM
I entered the following formula:

=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:C200="Web"))

But get #N/A for a result??

=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:B200="MN")*(C2:C200="Web"))

...Works fine other than I would like to...
within the same formula, how can I get rid of all my ranges (D2:D200
B2:B200, C2:C200, etc.) and replace the formula with something tha
auto counts filled-in rows...keeping in mind that column B will have n
blanks?

Thanks!
-

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 4:57:15 PM
All of the ranges must be the same size. I thought that was what you
referred to in the previous post.

Try this

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("2003/12/31")))*(B2:OFFSET(B1,
COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14m05d@excelforum-nospam.com> wrote in message
news:rerhart.14m05d@excelforum-nospam.com...
> I entered the following formula:
>
>
=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN"
)*(C2:C200="Web"))
>
> But get #N/A for a result??
>
> =SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:B200="MN")*(C2:C200="Web"))
>
> ..Works fine other than I would like to...
> within the same formula, how can I get rid of all my ranges (D2:D200,
> B2:B200, C2:C200, etc.) and replace the formula with something that
> auto counts filled-in rows...keeping in mind that column B will have no
> blanks?
>
> Thanks!
> -R
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/12/2004 5:41:28 PM
Once again, thank you very much for your help.

With the follwoing formula:

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))))

I am now receiving a #VALUE error.  Not sure where the problem is.

Here is my sample data:

Server Name	Location	Type	Lease End Date
Server1	                MN	Web	6/30/2004
Server2	                CA	App	8/15/200

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 6:06:53 PM
This works for me. It is imperative that you enter the date in the correct
format as I have done

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0)>--("2003/12/31"))*(B2:OFFSET(B2,C
OUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0)="Web"))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14m3df@excelforum-nospam.com> wrote in message
news:rerhart.14m3df@excelforum-nospam.com...
> Once again, thank you very much for your help.
>
> With the follwoing formula:
>
>
=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("12/31/2003")))*(B2:OFFSET(B1,
COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))))
>
> I am now receiving a #VALUE error.  Not sure where the problem is.
>
> Here is my sample data:
>
> Server Name Location Type Lease End Date
> Server1                 MN Web 6/30/2004
> Server2                 CA App 8/15/2004
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/12/2004 7:38:21 PM
Reply:

Similar Artilces:

Using this News Group
I would appreciate it if someone could tell me if I have a setup problem on my computer or there is sometimes a problem with this news group when I access it IE7. After I sign in and see an item that I want to reply to, I press on reply. After I clicked on the reply sometimes the window pos up and I am all set to reply the way I expect other times I get no response or I just get the same message that I was looking at. Anyone else experiencing this issue? "neil154" <neil154@discussions.microsoft.com> wrote in message news:A345E68D-C3B9-4FD8-8211-CACE1374C389@microso...

Sales forecasting
Would like the option to enter the Sales Forecast by Item Class. That way you don't have to enter item by item. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=20564...

VBA code for selecting records by date
I have the following code behind a 'Print' button. Dim strDocName As String Dim strWhere As String Dim YrStart As Date Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("AttendanceCtrl") YrStart = rst!FirstDay strDocName = "Contact" strWhere = "[StudentID] =" & Me!StudentId & "AND [Calls].[CallDate] >= " & YrStart DoCmd.OpenReport strDocName, acPreview, , strWhere Table 'AttendanceCtrl' contains the start of the year and Ta...

MRP Forecast using MO Quote/Estimate inplace of Sales Forecast
Hi, We have a client that has GP9.0 with Standard keys. With GP Standard they cannot purchase the Sales Forecast module to use with the MRP module. Would there be any problem using the MO with a status of Quote/Estimate to enter a "forecast" qty to create component requirements for MRP? Or is there a better way to handle a forecast in MRP w/o the Sales Forecast module? Thanks Yes, there would be a problem. When a Sales Forecast is used by MRP, it can be consumed by actual demands. Thus, if 1000 of an item was forecast in a period and 100 pieces was actually ordered, the...

Covert date to text
I have a mass of data with dates, which I need to group by DAY. I have formatted the cell to custom "dddd" so that it shows the day, however when i pivot report it still reads the date. How can i convert the date field to text to read the day? A straight forward format of cell returns the date in number format. HELP! "Alan" <anonymous@discussions.microsoft.com> wrote in message news:0bc301c3c642$ada1f740$a301280a@phx.gbl... > I have a mass of data with dates, which I need to group > by DAY. I have formatted the cell to custom "dddd" so > t...

using office student for commercial use.
I have Windows 7 Home Premium installed in my laptop and I have MS office 2007 Home and student edition installed. I need to use powerpoint at my office. can I use these software at work ? legally is there any problem ? can I use powerpoint works at work also ? can I also sell my work that I made with powerpoint home and student ? shortly commercially is there any problem ? Sentiere wrote: > I have Windows 7 Home Premium installed in my laptop and I have MS office > 2007 Home and student edition installed. I need to use powerpoint at my > office. can I use these soft...

How to use Windows Media Player control to play avi file
Hello, I need to play an avi file in my program using the media player activex control.I need to be able to indicate a starting frame and an end frame that the control will stop playing after reaching it. Thanks in advance ...

Forecasting
I work in a call centre and I was asked to do some forecasting for call offered for the next 6 months. I have historical data since 2002. Colums A wil have the "month" and Column B will have the number o calls offered. How can I do the forecasting using "moving averages" (need exaple of the formulas in the cells). thank -- Message posted from http://www.ExcelForum.com Hi a little bit more detail would be helpful :-) - how many months do you want to include in your moving average. e.g. if you want two months simply enter the following in C3 (assumption: row 1 is a head...

Using SetDIBits to initialize a CBitmap?
Does anyone know how to use SetDIBits to initialize a CBitmap? http://www.google.com/search?hl=en&q=SetDIBits+Example http://www.google.com/search?hl=en&q=CBitmap+SetDIBits+Example http://www.codeproject.com/info/search.asp?cats=2&searchkw=SetDIBits&Submit1=Search&author=&sd=15+Nov+1999&ed=18+Jul+2007 AliR. "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:Iynni.337$gM1.158@newsfe10.phx... > Does anyone know how to use SetDIBits to initialize a CBitmap? > "AliR (VC++ MVP)" <AliR@online.nospam> wrote in me...

Using the Recovery Storage Group
I want to restore using the Recovery Storage Group, but i get the following error when i try Add Database to Recover: There is no such object on the server. Facility: LDAP Provider ID no: 80072030 Exchange System Manager. More info... Exchange is running in a cluster, has been for about a year without any problems. I use Veritas Backup Exec, and use the Agent for Exchange. The cluster works perfectly, no problem. I right-click the Recovery Storage Group, select Add Database to Recover, in the search results i click the the Mailbox Store i want, (there is only one), and click ok. This i...

Forecaster Benefits suggestion
For 401K benefits, they normally only start after one year, from the start date. My client is budgeting multiple years at once. There's no way to do this in the current system. It would be something like being able to set up start and end dates in the benefits screen. For Part Time employees, you have to enter a FTE amount. Normally, if they're full time, you enter 2080. But if they're less, we enter 1040, etc. But, this calculates only half their benefits, then. And many part-time employees still get full benefits. How could we fix this? Thanks! ----------------...

Allow reminder of password expiracy using OWA
Does anyone know how to allow the pop-up to tell a user that their password will expire in xx amount of days to display when logging into OWA with Exchange 2003? Just to clarify, I am not talking about enabling the change password button under options AFTER they are logged in. This has been a very frustrating problem, any help is MUCH APPRECIATED! -- sarah While http://support.microsoft.com/default.aspx?scid=kb;en-us;297121 I think is more related to showing or not showing the change password button, I think that http://www.mcse.ms/archive74-2005-5-1635986.html Offers up a good idea o...

using XML schemas
Visual Studio dot net has a nice feature for creating schemas from xml files, but it appears unless the schema generated is registered at some official website, that the schema is useless - e.g. Visual Studio doesn't list the schema it just created in the dropdown list of available schemas- and if you try to hand code the schema reference in the xml file, visual studio reports it can't find the schema it just automatically generated, hence no validation can take place. What am I missing and/or can any one help? Thanks ...

Using INDIRECT
I have a spreadsheet with multiple references to an external sheet located on a website. Every one of these cells has the line something like: ='http://webpage.aspx/[EXCELBOOK]FirstPage!$A$1' with the specific A1 reference different each time. What I would like is to be able to use something like INDIRECT to simply write ='http://webpage.aspx/<<LOCAL CELL REFERENCE>>FirstPage!$A$1' So that I could change the local cell reference to the name of different workbooks and it would change to the A1 value of the workbook of that name. I know you can do =INDIRECT...

Using Preprocessor defines in menu resources
Hello, I'm using VC++ 2005 and MFC and have an application which I want in two different versions. I'm using #define and #ifdef to make the two versions and this works perfectly in the code. My problem is that I have some menu items that should be there or not depending on the same #define. I see that Resources has it's own Preprocessor Defines, but what I can't see is how I shall use the #ifdef? Where do I set the #ifdef condition?? Please help! // Anders -- English is not my first, or second, language so anything strange, or insulting, is due to the translation. Please...

forecast
I need help in forecasting. so any help will be highly appreciated. I have a data as follows: Jan05 Feb05 Mar05 Apr05 May05 June05...... Dec05 Total 103 107 ... ... ... .... 1700 Based on Jan05 and Feb 05 data I need to fill up Mar 05 to Dec05 data to get a pre determined total of 1700. Is there any quick way or formula or function to solve this?? Thanks for your help. One way: Assuming your "Mar05" entry is in C1, and that you want the values in succeeding months to be equal: C2: =ROUND(($M2-SUM($A2:B2))/(COLUMN($M2)-COLUMN()),2) and cop...

Insert character using Regex
Hi, Please have a look at the three strings below which are in 3 cells in a column. These are portions of longer strings. KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384 SP-17700IOOI KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTIA-3316776111 Using Regex and VBA, I want to add a "~" character after the "384" in the first line, "938" in the second line and "401" in the third line ie to get the following output. (Regex Buddy gave the following regular expression ...

How to use InvalidateRect
I want to redraw only a portion of my View, for example I have a CRect in the lower right corner where my app draws some text, I want to redraw that rect when the info changes, so if that CRect is named rect, I call InvalidateRect(&rect) (the berase parameter is moot in this case). However this does nothing. If I call Invalidate() to repaint the screen, it works fine, but I want to reduce my drawing overhead. Where am I going wrong with InvalidateRect? According to MSDN : "The invalidated areas accumulate in the update region until the region is processed when the next WM_...

Unable to use User Defined Charts
When I try to save a user define chart, i am getting the error message: "microsoft excel cannot save the user defined chart b/c the chart gallery does not exist or is currently in use" At that point, I am prompted to save my file, which I do, and then I can see and use the user defined chart for the time being. HOWEVER, once i close out of excel and reopen it, the user defined chart is gone. Does anyone know what my options are to get this resolved? Thanks for any suggestions. As an aside, I was told (by Gord Dibben) that i need to be using XLUSRGAL.XLS and X...

Limitations to the use of external email
Hi, In our organisation we have 3 groups of users. The first group can send/receive email to/from all internal and external emailaddresses. The second group can send/receive email to/from all internal addresses and can send/receive email to a limitted list of external addresses. The last group can only send/recieve email to/from internal addresses. What do I have to do to implement the 2 last groups ? We use SBS 2003 Premium with exchange. Bart You can limit user capability to send/receive through SMTP Connector. On Address Space tab, set E-mail domain: * and cost =1. On Delivery Restric...

Can you use an if then statement to select bold values from a list?
I have a column of data and some of the values are in bold to stand out. Can I use an if then statment to look at a cell, determine if the value is in bold font and, if it is, do an action? example: (in cell B1) =if(A1=bold font,"<","") Thanks EggHeadCafe - Software Developer Portal of Choice Useful Google Search Tips and Tricks http://www.eggheadcafe.com/tutorials/aspnet/6fbffa7d-3040-45f3-bbd8-e8a148b071bf/useful-google-search-tips.aspx One way is to use your own UserDefinedFunction that can check the formatting. Option Explicit Function IsBold(rng As Range) As B...

Newspaper layout using Excel?
I work in a newspaper advertising department and use Excel to keep a list of ads that will appear in our newspaper. It contains the advertiser's name, column width and length of the ads. We currently use the Excel list as a reference to create the page layout by hand - Yes, old-fashioned pencil and paper! I'd like be able to use the Excel program itself to create the layout or import the Excel data into a compatible program to create the "map" of the ads and show where we want them to appear. They only need to appear as blank boxes in the appropriate sizes (with the advertis...

Date Format #3
I have a spreadsheet that I want the date to be formatted as day/month/year (13-09-03). I go to format cells and choose that format for my column. When I type in the date as 13/09/03 it will not convert the date to the correct format. However if I type in 09/13/03 Excel will convert to the proper format. Does this have anything to do with my regional settings in Windows. Would I have to change the setting to day/month/year in windows. I am using Excel 2000. Thanks in advance for your help. Regards, Dee Number format affects display only, not how XL parses an entry. To change the p...

FRx Forecaster #2
Hello, In a Calc Set when a range of Accounts is used the Calc set does not work (Calc = '1000..5000') Have followed all the TK and it does satisfy all the requirements for a Calc Set to work. Any suggestions would be highly appreciated. Thanks in advance Ajay Ajay-- is the variable on the left side a valid Account? (i.e., does 'Calc' exist in the list of accounts, and in the lineset? Also, check the syntax in the actual calculation. It should read Calc='1000..'5000 with the single quote in front of the variables. "Ajay" wrote: > Hello, &...

Using Afxbeginthread
Hi I have used AfxbeginThread with ThreadProc as first argument. i wantd to ask what is the use of second type of AfxBeginThread that takes CRunTimeClass * as parameter..also how to use it..i mean in the first type we just have to write our code to be executed in the thread with in that Thread function that is passes as first argument to AfxBeginthread, but what about the other type..where do we write our code in the second type of this API. could someone please give me an example coz i cud'nt understand whats written in msdn..also i didnt find any good example clearly indicating API usage...