Posts by hgus393

• Formula For Matching Dates And Returning Values

Re: Formula For Matching Dates And Returning Values

Wow, I am impressed - I did not know that you could do a double match from an index!! That is great!

Thanx alot

Robert

• Formula For Matching Dates And Returning Values

Hi all,

I am stuck with a problem that I cannot solve by myself. An explanation - I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.

I have tried with varying Index and matching formulas and I do believe that this is the way to go. Can anyone help?

• Formula For Number Of Days In A Month

Re: Formula For Number Of Days In A Month

Cool thank you very much!!

;o)

Robert

• Formula For Number Of Days In A Month

Hi all,

I have checked searching the forums for this but nothing that really matches what I am looking for. I am trying to, from a date, deduce how many days there are in that month. I have tried a solution where I look at EOMONTH adding and subtracting but this is quite cumbersome. Is there some code out there where someone has solved this?

/Robert

• SumIf By Cell Color

Hi all,
I found a really excellent function on the net that you guys might have seen before but that I wanted to share. This formula sums cells according to colour. Neat huh?

Robert

Aha!!
Thanx alot!
Robert

Hi all,
I have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?

Thanx

Robert

• Fill/Copy Formula Down But Increment Across

Re: Fill/Copy Formula Down But Increment Across

I am always amazed how easy it seems for you to find a solution. :wowee: I have tried to solve this myself by reading various posts and other sources and I get about 80% :duh: of the way but no more...any recommendations to getting to 100%??

Thank you

Robert

• Fill/Copy Formula Down But Increment Across

Of course, in the sheet Extraction is where the source data is located. In the sheet Extraction offset is where the data should be displayed in from range a3 downwards.
Cheers
Robert

• Sum Across As Formula Dragged Down

Re: Filling Down Formula From Source That Goes Horisontally

Ok I must be a dunce cause I cannot get this to work.
I have enclosed an example of the data that I need to autofill via the offset in the sheet extraction. The sheet Extraction offset is where the data in extraction should be offset into.

all the best

Robert

• Sum Across As Formula Dragged Down

Hi,
From the title "Filling Down Formula From Source That Goes Horisontally" I hope that someone understands what I mean. If I try to autofill a formula where the source cells that I want to autofill is organised in an horisontal fashion instead of vertical fashion, then the autofill always goes in an vertical fashion. Is there a way to get around this, macro, formula??

Thanx

Robert

• Automatically Update Graph/Chart

Re: Automatically Update Graph/Chart

Ok will do that, Thanks alot for your help!

Cheers

Robert

• Automatically Update Graph/Chart

Re: Automatically Update Graph

Hi Krishnakumar,
That's awesome, however will this work as I delete the worksheet with data everytime I use the macro? The name of the sheet will be the same, but it will be a new sheet.

Robert

• Automatically Update Graph/Chart

Hi,
I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that?

The code for extracting from the workbook is the following:

Robert

• Close Workbook If Macros Not Enabled

Hello,
I have after buying David and Raina Hawley's book, Excel Hacks, implemented their password protection (Hack 130) in one of my workbook. However it has one downside that I wonder if there is a way to trap this. When I or someone else than me opens the workbook then excel displays a Security Warning that this workbook contains macros. If I choose Disable Macros then anyone can see my work. Is there a way to shut down the workbook if someone chooses to open the workbook without macros?
//Robert

• Count Values Less Than Zero

Re: Matching Date Where Value Is Less Than Zero

Quote from shg

[COLOR="Blue"]=COUNTIF(B10:K10, ">0")[/COLOR]

Well that's all nice and well if you have a linear time axis. However I do not, so that will not work I am afraid. I have toyed with the idea of using an IF statement to find if the length of the date cell is >10 then it should use a right formula and use the 10 right letters. Hope you understand what I mean. If you check the spreadsheet, I basically want the formula that is #N/A to show the same as the formula that has a value (except for number of dayes and date of course).
Robert

• Count Values Less Than Zero

Re: Matching Date Where Value Is Less Than Zero

Ie number of days with positive Cash Flow.
//Robert

• Count Values Less Than Zero

Hi all,

I have a little formula problem, I am using and index and match formula to find what date the value is less than zero. However I am not using a continous time series in my figures but a time span for example 2008-01-30 - 2008-02-05. The formula works fine with a continous time series but not for time spans. Does anyone have a clue to get round this. I am enclosing an example where the formula works and also an example where it does not.
Grateful for any help!

Robert

• Identify Breakeven Point, Or Positive To Negative, On Chart

Re: Identify Breakeven Point, Or Positive To Negative, On Chart

I concur,
:wowee:
Dave, a very nice solution which I have implemented into my graphs.

Many thanx

Robert

• Identify Breakeven Point, Or Positive To Negative, On Chart

Re: Insert A Line Where Chart Is Breakeven (or Changes From Positive To Negative)

Quote from davecurtis

How about as in the attached. Plot chart as x-y rather than Line. Use TREND function to calculate when the line between the two points is zero. Add point to chart as new series. Add error bars and format as required.

Dave

Nice!!! That is awsome :o), thanx a lot!

Robert