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 ?


    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

    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


    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



    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.


    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