Posts by GuyGadois

Re: Validation>lists - Named Ranges Force Correct Input

Quote from AAE

I don't know why it makes a difference but it does . . .

In the validation dialogue, on the Error Alert tab, make sure you have it set to show an error alert after invalid data is entered.

I just tested it to make sure. After I deselected it, I was able to enter any value, but after re-enabling it to display the alert I was not.

UGH! I tried what you said and it doesn't work. I have the following

Data Validation: Allow List: Source =Admin_Type (my named range), Ignore Blank and In-Cell Dropdown are checked. "Apply these changes..." is not checked
Input message tab: nothing checked
Error Alert tab: "Show error alert" is checked. I have tried all three options on the pull down and title and error message for all three options to no avail.

What is wrong? Did I not name my range correctly maybe?

Thanks,

Dan

Re: Validation>lists - Named Ranges Force Correct Input

Quote from Parsnip

Data>Validation>Allow:List

=YourNamedRange

Then they should be restricted to the contents of your named range. Is this not happening?

Yeah, I thought the same but it allows anything to be entered. I wonder why that is the case. Maybe I am doing something wrong?

Guy

I am attempting to use a named range as my Validation data source but have two questions:

1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range

2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.

Any help os appreciated.

Guy

Re: Find Lowest Time in Range Above Value Of Zero

Quote from Dave Hawley

=IF(MIN('2007 Log'!DD:DD)=0,SMALL('2007 Log'!DD:DD,2),MIN('2007 Log'!DD:DD))

Dave, thanks for your speedy reply. I tried your suggestion but it is still returning 00:00:00. I tried limiting the area to just DD3:DD89 (where the data lies) but this still returned zero. I am bummfuzzled. Could it be because there are multiple times in that range where 0 occurs?

Guy

I have a column of a bunch of times in the format of HH:MM:SS. Most of the times are 00:00:00 as no time was noted on that day. I am looking for a simple function that will display the lowest time that is not 00:00:00. I have tried the following but don't think it is the correct way of doing it. Anyone have a solution for this?

=MIN('2007 Log'!DD:DD,0)

Thank you,

Guy

Re: Chart Bar To Show Goal And Amount To Date ?

Ahhhh ha! Works like a charm. [COLOR="DarkGreen"]Thanks for helping out[/COLOR]. Sorry it took me a while to get a clue.

Guy

Re: Chart Bar To Show Goal And Amount To Date ?

I plan on having 12 rows. So with 2 series for each row that would be 24 bars - maybe a bit too overwhelming. Is this how you proposed it or am I not gettin' it?

Guy

Re: Chart Bar To Show Goal And Amount To Date ?

Thank Andy, but is there a solution that can contain it in one bar? I think a solution would be to have the height of each bar be a cell value but Excel doesn't seem to allow that.

Guy

I have the following set of data:

Jan $500,000 $490,000
Feb $300,000 $410,000
March $350,000

The first column is month formatted as a date, the next column is a goal while the third column is the actual money raised. I would like to show a chart that each bar is a month and the second column is 100% of the bar and the third column is the actual. So in the forst row the bar would show that the the acutual color didn't reach the goal. The second bar (Feb) would show the Actual more than the goal in bar form (different color). March, the third row would just be a bar with only the goal showing up (Pretend we are in February).

I can't seem to find a way that charts work like this. Any suggestions on how best to approach this?

Guy

Re: Return Value From Table Based Upon Entered Variable

Thank you to everyone. I will check these out now!

Guy

In my spreadsheet there is a main table that has the following:

Name of Company ABC Fund XYZ Fund
Acme $8888 $9999
Joe Co. $9595 $5555

Now, on another sheet I enter in the company name in Cell A1 and then the Fund name in A2. I want A3 to then be equal to the appropriate amount (name of company and amount in fund). I have used vlookup before but that only returns half of the equation. How can I accomplish this?

Thanks,

Guy

I am not sure how to solve this spreadsheet quandry and want to ask for a little assistance. Here is the situation:

I have one worksheet (called 'data')with 20 names of people with each consecutive column being data about the person (DOB and other info). On another Worksheet (in the same workbook called 'name') I have a cell that you pick a persons name from a list. I then want the information populated from the 'data' worksheet to the 'name' sheet with the persons name I just selected. I am not sure the best way to do this. I have aboiut 7 fields that I want populated from the data worksheet to this name worksheet. What is the best way to accomplish this?

Thank you,

Guy

I am trying to do a simple copy and paste of number of cells A42:H76 to another sheet (in the same workbook) and have all the references stay when pasting to the new sheet. Instead when I paste it I get #Ref and the cell formulas no longer point to the right cell (they are all off by the same number because I did not paste the cells on the same row/column on the new sheet). Also, they don't refer to the older sheet where I want them to refer to.

My guess is that this is an easy fix. I am just not sure how to do it.

Any help is appreciated.

Guy

Re: Macro To Match Date Isn't Working

Quote from JimFuller1

Just convert to values (copy/paste special/values). It won't change the formatting and we get to.... oh never mind, I give up. Good luck.

OK, attached is what the feed looks like and the feed copied and pasted values. Again, I said earlier in my post that the feed sends a date that reads "4/25/07" if I do a copy/paste special/values all it will post is exactly what I said. Obviously, I am a beginner at this so I must be just confusing everything.

Re: Macro To Match Date Isn't Working

Sweet Jesus! It worked on the first run through. Let me do some additional tests on it and see if it continues to work!.

Thanks!

Guy

Quote from zimitry

Try this

Code

Sub Equity_Dividend() 
     
    Dim wb As Workbook 
    Set wb = ThisWorkbook 
    Dim ExDivDate As Date 
    Dim dt As Date 
     
     
     
    For Each Sh In ActiveWorkbook.Worksheets(Array("G&I", "Growth")) 
        For Each ce In Sh.Range("a5:a" & Sh.Range("a65536").End(xlUp).Row) 
            If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 50))) Then 
                If IsDate(ce.Offset(0, 50)) = True Then
                      dt = DateValue(ce.offset(0,50))
                If Date = dt Then 
'Remeber to close the Nested End If

Display More

Re: Macro To Match Date Isn't Working

Quote from JimFuller1

GuyGadois,

It's probably just me but the thing in question seems to be the contents and the formatting of the cell 50 columns over. If you get the answer from the people willing to guess what's in that cell or the array of cells, whatever, then good. If you want to help those that want to help you, post the sheet.

Jim

Jim,
I agree. The problem is that without the datafeed the column will only show a #REF so I don't think that will do anyone any good. I have no problem posting it but I am not sure it will do any good. Let me work on it.

Guy

Re: Macro To Match Date Isn't Working

Quote from zimitry

you haven't done any error handling if there isn't a date in that cell thats most likely why your getting a run time error...

something like this

Code

Dim dt As Date


For i = 1 To 10 Step 1


If IsDate(Cells(i, 1)) Then


dt = DateValue(Cells(i, 1))


MsgBox dt


Else


End If

Display More

HTH z

Display More

I will try that. Where would I insert this code at?

Guy

Re: Macro To Match Date Isn't Working

Quote from JimFuller1

GuyGadois,

You know you can just peel off the part of the sheet that has the date and send that little itty bit of the data so we can see...

Jim

I could do that but it is part of an array and will look for the data feed. Without the datafeed it will show up as #REF . Here is the array...

=ILX|Q!'gis,LAST,DIVIDEND,Change,%CHG,ANNHIGH,ANNLOW,PE,ex,bta,is_halt,DATE,MUT_DIV,GAINS,ftn'

DATE is the problem column.

Re: Macro To Match Date Isn't Working

Quote from zimitry

Code

Dim dt As Date


dt = DateValue(ce.Offset(0, 50))

I plemented it as follows and it seems to also get a Type Mismatch Error 13 on the 'dt = DateValue(ce.Offset(0, 50))' line. It seems to work the first pass by that line but when it circles back via the 'for each' statement it then errors out.

Sub Equity_Dividend()


Dim wb As Workbook
Set wb = ThisWorkbook
Dim ExDivDate As Date
Dim dt As Date
 




    For Each Sh In ActiveWorkbook.Worksheets(Array("G&I", "Growth"))
        For Each ce In Sh.Range("a5:a" & Sh.Range("a65536").End(xlUp).Row)
            If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 50))) Then
            dt = DateValue(ce.Offset(0, 50))
                If Date = dt Then
                     
If Sh.Name = "G&I" Then
    AvailableCash = Range("GI_Cash").Value 'u140
ElseIf Sh.Name = "Growth" Then
    AvailableCash = Range("G_Cash").Value 'u140
End If
                     
                    CompanyName = ce
                    NumberShares = ce.Offset(0, 19)
                    ExDivDate = ce.Offset(0, 50)
                    DividendAmount = ce.Offset(0, 44)
                    Transaction_Type = "Dividend"
                    Portfolio_Name = Sh.Name
                    DividendFrequency = ce.Offset(0, 59)
                     
                    QuarterlyDiv = (DividendAmount / DividendFrequency) * NumberShares
                    NewCashValue = AvailableCash + QuarterlyDiv
                     
                    If Sh.Name = "G&I" Then
                        Range("GI_Cash").Value = NewCashValue
                    ElseIf Sh.Name = "Growth" Then
                        Range("G_Cash").Value = NewCashValue
                    End If
                    
                     
                     'Alerting user if specified
                    If Worksheets("Admin").Range("Equity_Alert") = "Yes" Then
                        Load Equity_Alert
                        With Equity_Alert
                            .TextBox1.Value = Transaction_Type
                            .TextBox2.Value = CompanyName
                            .TextBox3.Value = QuarterlyDiv
                            .TextBox4.Value = Portfolio_Name
                        End With
                        Equity_Alert.Show
                    End If
                     
                     'Adding info to Tran Tab
                    Sheets(Sh.Name & " Trans").Rows("3:3").Copy
                    Sheets(Sh.Name & " Trans").Rows("4:4").Insert Shift:=xlDown
                    Sheets(Sh.Name & " Trans").Range("A4") = "Dividend"
                    Sheets(Sh.Name & " Trans").Range("B4") = ExDivDate
                    Sheets(Sh.Name & " Trans").Range("D4") = CompanyName
                    Sheets(Sh.Name & " Trans").Range("k4") = QuarterlyDiv
                End If
            End If
        Next ce
    Next Sh
        
'Paste Date to confirm dividend check
wb.Worksheets("Admin").Range("Dividend_Date").Value = Worksheets("Admin").Range("Current_Date").Value
Application.CutCopyMode = False
    
'Pasting Debug
wb.Worksheets("Debug").Range("3:3").Insert Shift:=xlDown
wb.Worksheets("Debug").Range("A3").Value = Now()
wb.Worksheets("Debug").Range("B3").Value = Now()
wb.Worksheets("Debug").Range("C3").Value = "Equity Dividend Macro"
    
End Sub

Re: Macro To Match Date Isn't Working

Quote from shg

Int doesn't convert its argumunt to a Type Integer, it truncates the fractional part: Int(1000000.2) returns 1000000. CInt(1000000.2) returns overflow.

I think you have a non-numeric string, Guy.

I think the macro broke when my data provider changed the feed a month ago. The field still reads "4/25/07" but for some reason date() does not equal 4/25/07 on my sheet. any idea on how to convert the values and compare apples to apples?

Guy