Posts by Joe Derr

    Re: sum including blank cells using ISERROR


    Ok.. that came out to 338..

    Ok.. decided to run the numbers.. and you are spot on...

    78 = 119
    82 = 219
    92 = 0
    99 = 0
    101 & 102 = 0

    Spot on! Great job, thank you so much for your help!


    Re: sum including blank cells using ISERROR


    Decided to give it a quick go.. the first one you posted for the 2003 at least, worked.. well it posted 557. I will see where that number came from, but if it is YTD (year to date) then that would be more than excellent!

    Thanks again,


    Re: sum including blank cells using ISERROR


    Thank you, I will try that tomorrow. It is actually Google, docs, excel.. its pretty interesting to work with when you are accustomed to working with real Excel.

    I will let you know! Thanks again!


    Hey all...

    I am working on a project for my wife. It is a sales tracking sheet of some sort. She got confused so I took over for her, now I went from basic formulas to complex, now I got myself confused.

    There is a Sheet called "Sales Metrics" and one called "Monthly Totals"... this is where I got myself confused.

    On the monthly sheet, I read cells from the "Sales Metrics" sheet to add together and then give a total for the month.

    I know there are going to be errors, because I am pre-filling out the formulas for the next year, thus info has not been entered, it returns an error #ERROR or a #VALUE sometimes.. here is my formula...

    =IF(ISERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102),"-",=sum('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102))

    I have been messing with it, so somethings might be out of whack... but here is what it is trying to do...

    IF Sales Metrics Cells B78, B85, B92, B99 and then the two cells B101 and B102 (which would be today Monday the 30th and Tomorrow Tuesday the 31st)

    The sales guy is waiting to fill in data until I get it fixed, so with out the data, it errors, but if I plug in data, it works (or at least it did)

    What the code I am using is trying to do is emulate this site help


    I tried plugging in my code into the "old code" area of their example, but like I said it keeps erring, so I kept moving ( ) around and who knows, I may have messed it up.

    This is the "old code"

    =sum('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B99+'Sales Metrics'!B101:B102)

    That error'd too because all the data in the month of December is not filled out.. thus it is trying to add blank cells...


    Thanks in advance!


    All, I have a chart that is displayed to represent a goal however, when the goal is close to being met, the chart rescales itself to zoom in toward the top of the "thermometer" style chart. Is there anyway to keep it showing the whole chart and not zoom in to the top as the goal is nearing 100%?

    Re: Upper Case Cell as Typing Occurs


    My post is asking how to make the cell change to upper case as I type. Is that possible?

    The second thing of this happening is how much room is left to type, which I will know when it does the first, no matter if you have cap locks on or not, it will upper case each letter as you type it, not after you leave the cell or not at all in the case of the "On Change" worksheet event I posted.

    So the Title *is* what I am after, once that happens the second desire (knowing how much room is left as I type) is apparent. I could changed the end of it to "as you type." I do see how the length can be seen as the ultimate goal, however it's really Upper Case as I am Typing in the Cell.

    The column width is 44.14, I just need to automatically convert what is being typed to upper case, with out having to rely on someone doing it right and typing it all in caps... I have to fix so many entries, so looking for a way to fix it.


    Maybe this isn't possible, I have looked around and I keep finding this method:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not .HasFormula Then
            .Value = UCase(.Value)
        End If
    End With
    End Sub

    However, the column I am working in (Column H) is limited in size, thus I have typed information in the cell, upper cased it and then found out my narrative was too long for the cell, after it was converted to Upper Case.

    I can simply hit, Caps Lock, but this needs to be dummy proof for others. We have had others fill out the form, all in lower case or proper case and then I had to go in and edit the entryies. So I thought, take the problem out, auto convert the text.

    The above will (sometimes) convert the text after you leave the cell, this is close to what I need, but would rather know how much room I have as I type.

    Ideas? Thanks in Advance.


    Does anyone know how to pass an Excel Variable to Word? I have two "Text Form Fields" that I want to pass from Excel to Word. One is a Report Number, the other is the Report Date. The Text Form Fields do not have a name, nor can I see how to assign them one. I am at a loss in the MS Word realm, so any help would be appreciated.

    The way I have it set up on the Excel file side is I double click in Column A of the Excel Sheet, the Row that contains a QA Failure. It populates two Variables.. RCN (Report Control Number) and RDATE (Report Date) I have the Excel part ready to pass to Word, but I cant figure out how to fill the "Text Form Fields" since they don't have an assigned name... if they do, like I said, I can't figure out where to find it.

    Attached is an example of the word doc... if for some reason it says something a accessing a database, or QATracker.xls it's because I tried to mailmerge it with the Tracking Sheet.. but found out that isn't what I was trying to do.. or at least I couldn't get it to work.

    Please help.. if you can.

    Thanks in Advance!


    Re: Write Formula to Cell with "If True" Blank cell option

    Rory and all,

    I am getting a test workbook put together.. This post here is when I was getting the formula built last July. I thought there was more to the post because I remember a part where after it worked (more or less) the times were still off. It's calculating flying times, based on hours and tenths. The end of the post isn't there.. but it continued leaving me with the current (and working) sheet based formula I posted. If you goto the other thread to see how the formula was built (mostly - sans the ending part) it might help fix it so I can write it using VBA.

    The reason am moving it to being written via VBA, is I have code that when you double click in Column A it will ask you, do you want to insert a new line.. (sometimes data has to be inserted after everything is filled out, thus needing to insert a line. When the line is inserted, it inserts a blank line. The requirement of this post is how to now write the formula to the Cell via VBA after the new line was inserted.

    I can get simple formulas to write to the cell, but as soon as it gets near the null or the blanking arugment, it errored. I tried your suggestion Rory, and it does write it to the sheet, however when it's used to calculate the time, it just displays 0.0 ... read the development thread of the formula to under stand the formula.. and like I said, there was another page, because it ends in showing a round down and doesn't show us working out the tenths error after the 30 min mark where he has to take a tenth of a second off the time for it to calculate right.. that might be where the double rounds comes from.

    I will see if I can find in my e-mail tomorrow the e-mail from OzGrid telling me about the post last July and see if its still there.

    Here is the Development of this particular formula for sheetbased usage:

    I will finish the test workbook so you can see it working tomorrow, thanks for your help everyone!!!


    I have a formula I am trying to write to a cell, via code.. however, it keeps erroring at the "If solves True" then "" I have tried """" and ("""") but nothing works. If I end the formula before that part, it will write it.. any ideas?

    'Selects the row you added
    Range("A" & (Target.Row)).Select
    'Next Row down and read in Row Number
    RowNum = Target.Row + 1
    Range("A" & RowNum & "").Offset(0, 4).Formula = "=IF(ISBLANK(D" & RowNum & "),(""""),ROUND(ROUND((V" & RowNum & "-T" & RowNum & "+(V" & RowNum & "<T" & RowNum & "))*1440,0)/60" '= "=sum(A" & RowNum & ":B" & RowNum & ")"

    I am baffled... Thanks in advance.....

    Re: Data Validation


    Believe me I know, it's confusing..

    I will see if I can somehow create an example workbook... As I said, I am just trying to insert a data validation quickly rather than, having to do all the clicking...

    I just deleted two paragraphs of how this workbook ties everything together..

    One updates the other, based on what it is.. the datavalidation changes to anyone of 7 data validation lists..

    Quickly... if the code in Column "N" is LL, in Column "M" it looks under the list for LL, which is 9 codes... If the code was GA in Column "N" then "M" would have 11 choices in the data validation...

    LOL... it's complicated... It's not what is in the other column or how it gets there.. I just wanted to put in the code via VBA.. sigh..

    Re: Data Validation

    I am thinking this isn't going to be easy...

    Column M has a Formula


    Column R is Data Validated based on a List of options when one is selected populates Column M which in turns populates Column N... All I am trying to do is quickly put the code on to the sheet via Macro.. but because its so imbedded, it wont be possible it seems..

    All I am trying to do is using a Macro, input data validation based off of the current selected row..

    guess I will be putting in by hand..

    Hi all...

    The code below is what I am trying to modify to insert a Data Validation, List, source is indirect to the next column back.

    This formula I am intending to hit CTRL SHIFT Z and it will grab the current row, and write the formula .. the reason is I have (150+) to insert this data validation and I would like to click the cell, chit Ctrl Shift Z and then select the next row..

    The problem is, I recorded the process using the Record Macro, and even *that* code errors.

    The error it gives me a "Run-time error '1004': Application-defined or object-defined error"

    Any ideas??

    Re: Formula If And statement via VBA

    Thanks Rory!

    I added your code to the one I had, I will look at your whole solution off line.. I slid this on the active workbook for a quick fix.. Now to tackle the conditional formatting ..

    Thanks again..

    Dim TRow As String
        Do While Not IsEmpty(ActiveCell.Offset(0, -1))
        TRow = ActiveCell.Row
        Formula = "=IF(E" & TRow & " = ""PASS"",""NA"",IF(AND(E" & TRow & "=""NONRATED"",G" & TRow & "=""TRN""),""NA"",""""))"
        ActiveCell.Formula = Formula
        ActiveCell.Offset(1, 0).Select

    Hi All... I need help with this formula..

    What it's supposed to do is IF (on that row Column E) = PASS Then that cell = "NA" and also IF (on that row Column E) = NONRATED --- AND --- (on that row Column G) = TRN then the cell will have a NA otherwise leave it blank.

    Column E Column G Column J and K
    ---------- ---------- ---------------
    PASS numerous options NA
    NONRATED TDV (blank cell out)

    Dim TRow As String
        Do While Not IsEmpty(ActiveCell.Offset(0, -1))
        TRow = ActiveCell.Row
        Formula = "=IF(E" & TRow & " =""PASS"",""NA"","""") & (E" & TRow & "G" & TRow & "=""TRN"",""NA"","""")"
    ActiveCell.Formula = Formula
        ActiveCell.Offset(1, 0).Select

    My formula code fails miserably... before I needed to add another criteria to the formula it was working fine... that code is below...

    [/FONT][FONT=Tahoma]    Formula = "=IF(E" & TRow & " =""PASS"",""NA"","""")"[/FONT][FONT=Tahoma]


    Re: Pass Variable to Access Textbox via excel dbl click event


    Thanks that works perfectly.. now I am off to find out how to run a macro in access from the same double click function... Here is the code I used, it pulls from the cell I am double clicking in, incase anyone is looking for this type of code...

    Re: Pass Variable to Access Textbox via excel dbl click event

    Hi Rory,

    Thanks for the reply... the report is ran by having the report number typed into the text box, then when you would hit enter, it runs the Macro "ExportToReport"

    That Macro is:

    ACTION                          ARGUMENTS
    -------                           ------------
    Echo                               No,
    RunSavedImportExport       Export-vRCN REPORT

    The Export-vRCN REPORT is the name of the saved steps to open the report (I am an excel guy, so I will take their word for it)

    So.. recap...

    I type in the report number from my tracking sheet to pull the report.. 5851 ... I type that in the textbox on the form and hit enter, it runs the report and opens a .rtf word file with the report.. I save the word document and then.. next number...

    so what I am trying to do is pass the RCN # ie 5851 to the form, and then either I hit enter, or even better, it runs the report.. I would like to "pass it" via double clicking in the RCN # in Column B to pass or run the report.. if it's possible.

    Thanks again.. I will tinker with your code..


    I am probably dreaming but thought I would ask to see if it's possible.

    The database contains all the reports, data etc. for the excel sheet. What I am looking to see if it can happen, is in Column B of my tracking sheet is a RCN number.. it is basically the report number of the data in the database pertaining to that report.. so kinda the key if you will.

    In the database we take this number type it into the text box that is named RCN and hit enter, it opens a word document with the report...

    What I am wondering.. is there a way to double click the RCN number in Column B and it autofill the RCN text box of the database.

    The database will be open....

    Ok.. am I dreaming or is this possible? I know I can get a number *from* access and populate a excel field, I am just not sure about populating the textbox via excell

    Excel Gods... thank you...

    Thanks in advance.