Posts by Joe Derr

Re: sum including blank cells using ISERROR

Robert,

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
-----------------
338

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

Joe

Re: sum including blank cells using ISERROR

Robert,

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,

Joe

Re: sum including blank cells using ISERROR

Quote from Trebor76;696629

Hi Joe,

You don't say what version of Excel you're using but for 2003 and prior try this...

=IF(ISERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102)),0,'Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102))

...while for 2007 and later try this:

=IFERROR('Sales Metrics'!B78+'Sales Metrics'!B85+'Sales Metrics'!B92+'Sales Metrics'!B85+SUM('Sales Metrics'!B101:B102),0)

Kind regards,

Robert

Display More

Robert,

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!

Joe

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

http://office.microsoft.com/en-us/excel-help/hide-error-values-and-error-indicators-in-cells-HP003056121.aspx

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...

Ideas?

Thanks in advance!

Joe

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

cytop,

I was worried about that.. well not worried, but figured.. I knew I could do it via userform/textbox input, but this particular spreadsheet is sheet based (for the most part) Thanks!

Joe

Re: Upper Case Cell as Typing Occurs

Thanks Attilas.. I will post if I find anything too.

Re: Upper Case Cell as Typing Occurs

AAE,

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.

All,

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.

All,

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!

Joe

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:
http://www.ozgrid.com/forum/showthread.php?t=144128

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

Joe

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

ShosMeister,

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

=IF(ISTEXT(R31),(R31),(IF(ISERR(W31),(""),(CONCATENATE(R31,"",W31)))))

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??

Row = ActiveCell.Row
Range("N" & ActiveCell.Row).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(M" & Row & ")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

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
    Loop

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 TRN 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
    Loop

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]

[/FONT]

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

Rory,

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...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
Dim REPORTFILENAME, TRACKNUMBER, TYPEINSP As String
Dim DataObj As New MSForms.DataObject
Dim objAcc As Object
If Target.Column <> 2 Then Exit Sub
   
' This is Rory's code on passing the double click'd cell contents to access
' My Textbox name is RCN
     
   Set objAcc = GetObject(, "Access.Application")
   objAcc.Screen.ActiveForm.Controls("RCN").Value = Target.Value
        


'This part collects variable information for a file name
 
    TRACKNUMBER = ActiveCell.Offset(0, -1).Value
    TYPEINSP = ActiveCell.Offset(0, 5).Value
    
' This combines the type of inspection puts in a dash and then adds the tracking number
    REPORTFILENAME = TYPEINSP & "-" & TRACKNUMBER
    
' This part puts the combined variable to the clip board, so
' on the saveas dialog from the word report that I am saving
' all I have to do is Ctrl V to paste the file name over the default
' report name
 
    DataObj.SetText REPORTFILENAME
    DataObj.PutInClipboard
   
' This selects 6 cells right of the target (where I double click)
' this is the next area I need filled in on the tracking sheet.
 
    ActiveCell.Offset(0, 6).Select
End Sub

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..

Joe

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.