Posts by hgus393

Re: Formula For Matching Dates And Returning Values

Quote from RichardSchollar

Hi

=INDEX(Data!$B$2:$AE$25,MATCH(Portfolio1!$B$1,Data!$A$2:$A$28,0),MATCH(Portfolio1!$A3,Data!$B$1:$AE$1,0))

In B3 copied down. See attached for the example.

Richard

Display More

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

Thanx alot

Robert

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?

Re: Formula For Number Of Days In A Month

Cool thank you very much!!

;o)

Robert

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

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

Function SUMIFCOLOUR(TheRange As Range, TheColourCell As Range) As Variant


    Dim TempRange As Range
    Dim Result
    Dim Colour
    
    Application.Volatile
    On Error GoTo BailOut
    Colour = TheColourCell.Interior.Color


    For Each TempRange In TheRange
        If Colour = TempRange.Interior.Color Then Result = Result + TempRange.Value
    Next
    
BailOut:
    SUMIFCOLOUR = Result
    
End Function

Re: Change Chart Link Source

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

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

Quote from TheDude

Try this...

Click and drag the bottom right corner of a cell or group of cells and drag them to the preferred location.

ah, just realized what you are saying... Could you repost a sample of the problem? I only see a string of dates on your current sample.

regards,

-Dude

Display More

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

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

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

Re: Automatically Update Graph/Chart

Ok will do that, Thanks alot for your help!

Cheers

Robert

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

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:

Sub Extract()
Dim myFileName As Variant
    Dim SourceWkbk As Workbook
    Dim CurrentWkbk As Workbook
    Dim testWks As Worksheet
     
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
     
    
myFileName = Application.GetOpenFilename("Excel files,*.xls")
     
     
    If myFileName = False Then
        Exit Sub 'user hit cancel
    End If
     
     
    Set CurrentWkbk = ActiveWorkbook
    Set SourceWkbk = Workbooks.Open(Filename:=myFileName)
     
     
    Set testWks = Nothing
    On Error Resume Next
    Set testWks = SourceWkbk.Worksheets("CURRENCIES")
    On Error GoTo 0
     
     
    If testWks Is Nothing Then
         MsgBox "Missing the worksheet!"
    Else
        testWks.Copy _
        before:=CurrentWkbk.Worksheets(2)
    End If
     
     
    SourceWkbk.Close savechanges:=False
end sub

Robert

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

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

Re: Matching Date Where Value Is Less Than Zero

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

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

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

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