Serious help needed for this project Can anyone help me with this

Programming and Web Development Forums - MS EXCEL - This section provides information about Excel commands and functions, Excel states, worksheet and expression evaluation, active vs. current worksheet, and worksheet references

Skip to content

Serious help needed for this project Can anyone help me with this

MS EXCEL
Post a reply

    Next

  • 1. Prompt for update
    When I open a workbook it prompts me for:- "Update" or "dont update" "Workbook linked to other sources" type of message. The workbook concerned is a copy of my original in another folder (I always work off copies) I have never experienced this before (the prompt) which is annoying. I have tried both `update` and `dont update` but still get the prompt every time I open it. How can I stop this. thanks
  • 2. assign an incremental number to a cell, for each new spreadsheet
    Can someone advise as the formula required to be entered into a cell, that allocates a new incremental number each time a spreadsheet of the same name is opened (like creating a new invoice individual invoice number for each spreadsheet)
  • 3. Vlookup with Multiple criteria and multiple sheets
    Hi I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny
  • 4. =?Utf-8?Q?_Functions_Expert=E2=80=A6..four_=284=29_?= =?Utf-8?Q?different_colors_if_a_specific_c?= =?Utf-8?Q?on?=
    Mike H, I'm sure this works, but my ability is limited in where to put. How do I insert this into the work sheet. I need direction in order to execute. "Mike H" wrote: > try this:- > > Private Sub Worksheet_Change(ByVal Target As Range) > If Intersect(Target, Range("A1")) _ > Is Nothing Then Exit Sub > Select Case Target.Value > Case "A" To "E" > icolor = 3 > Case "F" To "J" > icolor = 41 > Case "K" To "O" > icolor = 4 > Case "P" To "T" > icolor = 6 > Case Else > End Select > Target.Interior.ColorIndex = icolor > End Sub > > > "JVANWORTH" wrote: > > > I need a cell to change into four (4) different colors if a specific > > condition is met. For example if A1 matches a text value thru EI need >>>> ed if it a thru Jthen lue thru Othen reen thru >gt; > > then ye>l>w. >>>> > > I struggling with the set up of this require>e>t. > >
  • 5. printing parts of worksheet
    In a worksheet with a orderlist,I would like to print only the part of the list that I shall order,and not the whole list.How do program the worksheet/book so that this is possible?

Serious help needed for this project Can anyone help me with this

Postby SmFubmllIHdvcmtzaGVldCBmdW5jdGlvbg » Fri, 24 Aug 2007 22:38:00 GMT 

I have a w/s that compares forecasts and actuals.
Column A is Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov Dec.
Column B is the Planned Forecast in $ for each month.
Column C is the Actual Amt in $ that was spent for that month

I need 3 formulas that at the end of each column do the following:

1) YTD total for each - this formula would take the $ amount for each month 
and * by the number of days in that month then divide by the actual number of 
toal days YTD so far. We are a month behind so we would only use through July 
not include August yet. In other words it is not to date (system date).

2) Forecast  total - $ amt for each month * # of days in the month divided 
by 365 days for the entire year.

3) Sum for the year (got this one figured out).

for Actual Totals:

Same as for forecast for YTD.

The second formula is my problem: for Actual $ amt MTD Forecast. Take the $ 
amt for each month *the # of days in that month PLUS include the $amt  for 
the forecast months that we have NOT gotten to yet this year. We are done 
with July, so you would only include Planned/Forecast $ for Aug, Sept, Oct, 
Nov, Dec taken from that column and sub for actual $ until we can replace it 
with the info when it comes in. This total is under Actual/Forecast. 

The last one for Actuals is sum and I got that one.

3) Total - got this one - it is the actual sum YTD.
EXAMPLE   
Month	Forecast	Actual
Jan	91.21	51.75
Feb	82.39	109.31
Mar	91.21	159.24
Apr	88.27	69.86
May	91.21	51.92
Jun	89.69	53.76
Jul	92.73	51.20
Aug	92.73	
Sep	89.73	
Oct	93.25	
Nov	90.26	
Dec	104.22	
_______________________________
YTD	89.64	77.86
Forecast	91.50	84.67
Total	1096.90	547.05

Jannie
Top

RE: Serious help needed for this project Can anyone help me with this

Postby VG9wcGVycw » Fri, 24 Aug 2007 23:22:33 GMT 

Assuming your data is in columns A to C:

in Column D to calculate days in month:

in D2 and copy down:

=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))

YTD Forecast:

=SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($B$2:$B$13)*($D$2:$D$13))/SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($D$2:$D$13))

YTD Actual:

=SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($C$2:$C$13)*($D$2:$D$13))/SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($D$2:$D$13))

Forecast/Plan

=SUMPRODUCT(($B$2:$B$13)*($D$2:$D$13))/SUM($D$2:$D$13)

Forecast Actual

=(SUMPRODUCT(--(MONTH($A$2:$A$13)<=MONTH(TODAY())-1)*($C$2:$C$13)*($D$2:$D$13))+SUMPRODUCT(--(MONTH($A$2:$A$13)>MONTH(TODAY())-1)*($B$2:$B$13)*($D$2:$D$13)))/SUM($D$2:$D$13)

HTH
Top

RE: Serious help needed for this project Can anyone help me with this

Postby SmFubmllIHdvcmtzaGVldCBmdW5jdGlvbg » Fri, 24 Aug 2007 23:44:04 GMT 

I understand what you are saying here to do but I copy and paste the formulas 
but I get a !Value error.
Top

Similar Threads:

1.Serious help needed for this project Can anyone help me with t 

Is the data in column A actual dates, with cell format of "mmm": I have 
assumed they are dates so "Jan" would be 01/01/2007, "Feb" 01/02/07 (UK 
format!)

"Value" indicates a data mismatch and the dates most likely candidate. Did 
the days of the month work OK in column D?

I tested my solution on your data and got identical results.

If you still have problems post w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM

"Jannie worksheet function" wrote:

> I understand what you are saying here to do but I copy and paste the formulas 
> but I get a !Value error.
> 
> "Jannie worksheet function" wrote:
> 
> > I have a w/s that compares forecasts and actuals.
> > Column A is Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov Dec.
> > Column B is the Planned Forecast in $ for each month.
> > Column C is the Actual Amt in $ that was spent for that month
> > 
> > I need 3 formulas that at the end of each column do the following:
> > 
> > 1) YTD total for each - this formula would take the $ amount for each month 
> > and * by the number of days in that month then divide by the actual number of 
> > toal days YTD so far. We are a month behind so we would only use through July 
> > not include August yet. In other words it is not to date (system date).
> > 
> > 2) Forecast  total - $ amt for each month * # of days in the month divided 
> > by 365 days for the entire year.
> > 
> > 3) Sum for the year (got this one figured out).
> > 
> > for Actual Totals:
> > 
> > Same as for forecast for YTD.
> > 
> > The second formula is my problem: for Actual $ amt MTD Forecast. Take the $ 
> > amt for each month *the # of days in that month PLUS include the $amt  for 
> > the forecast months that we have NOT gotten to yet this year. We are done 
> > with July, so you would only include Planned/Forecast $ for Aug, Sept, Oct, 
> > Nov, Dec taken from that column and sub for actual $ until we can replace it 
> > with the info when it comes in. This total is under Actual/Forecast. 
> > 
> > The last one for Actuals is sum and I got that one.
> > 
> > 3) Total - got this one - it is the actual sum YTD.
> > EXAMPLE   
> > Month	Forecast	Actual
> > Jan	91.21	51.75
> > Feb	82.39	109.31
> > Mar	91.21	159.24
> > Apr	88.27	69.86
> > May	91.21	51.92
> > Jun	89.69	53.76
> > Jul	92.73	51.20
> > Aug	92.73	
> > Sep	89.73	
> > Oct	93.25	
> > Nov	90.26	
> > Dec	104.22	
> > _______________________________
> > YTD	89.64	77.86
> > Forecast	91.50	84.67
> > Total	1096.90	547.05
> > 
> > Jannie
> > 
> > 
> >

2.Serious Need of HELP!!! 

Getting a "Not Enough Memory" error when I attempt to open 
a spreadsheet with only "OK" as an option to clear the 
dialog box. I have a lot of data in this speadsheet. It is 
only data in fields, no calculations, no charts/graphics 
and it is only <3 mb in size. It resides on a network 
drive so various users can view it but only one user 
has 'write' rights. It isn't open by another user. We get 
the error on any PC we try to open it on.

Previous posts did not provide any response but I am in 
need of help here and would appricate any assistance I can 
get to get the data back. Excel did not give a warning 
about not being able to get your data at a certain point.

I hope someone can help me....Thanks!!!!

3.VBE - Need Serious help

4.I have a complicated formula that I need SERIOUS help with ple 

And the criteria for shading it is_________________?

-- 
Don Guillett
SalesAid Software
 XXXX@XXXXX.COM 
"Thrlckr" < XXXX@XXXXX.COM > wrote in message 
news: XXXX@XXXXX.COM ...
> It is shaded manually.
>
> "Don Guillett" wrote:
>
>> What and how is the cell shaded? Manual, conditional formatting, macro?
>>
>> -- 
>> Don Guillett
>> SalesAid Software
>>  XXXX@XXXXX.COM 
>> "Thrlckr" < XXXX@XXXXX.COM > wrote in message
>> news: XXXX@XXXXX.COM ...
>> > Hi,
>> >
>> > I am trying to count cells in a row that either contain a "1" or are
>> > "blank," but some of the cells in the row are shaded.  The problem that 
>> > I
>> > am
>> > having is that I cannot make the formula work where it counts and adds 
>> > up
>> > all
>> > the "1's" and "blank" cells, but not the "shaded" cells.  The other
>> > problem
>> > is that sometimes the shaded cells do contain a "1" in it, but I still 
>> > do
>> > not
>> > want that cell counted.
>> >
>> > If someone can PLEASE help me with this, I would greatly appreciate it!
>> >
>> > Sincerely,
>> > Carol
>>
>>
>>

5.I NEED SOME SERIOUS HELP =) 

I have created a workbook to track sales, GM etc for each of our salesman in 
each region. Each workbook contains a combined sales page and then 10 
additional pages (one for each salesman). Ont he combined sheet I was able to 
set up formulas to pull the dollar info from each cell on each page by using 
the = sign and then clicking on each tab and then the cell. It worked great 
to pull a grand total. I for the life of me cannot do the same for the GM 
percentages. I need to get an average of these percentages from each page to 
go on the combined sales page. Can anyone help? I have tried doing the same 
formula as the dollar amount one but using average with failure.
Thanks so much!
-- 
jennifer s

6. Some serious help needed

7. * Need serious help with formula/function *

8. Serious Need of Help Here - retrieving data from excel to use as labels for toolbar

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 33 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group