Excelguru.ca http://www.excelguru.ca/forums/ This is a free help forum for those using Microsoft Excel. en Wed, 19 Jun 2013 06:13:54 GMT vBulletin 60 http://www.excelguru.ca/forums/images/misc/rss.png Excelguru.ca http://www.excelguru.ca/forums/ <![CDATA[Changing cell value produces "#DIV/0!" error message in columns]]> http://www.excelguru.ca/forums/showthread.php?1787-Changing-cell-value-produces-quot-DIV-0!-quot-error-message-in-columns&goto=newpost Wed, 19 Jun 2013 05:44:59 GMT The [attached] spreadsheet I have been updating still produces an error message when I attempt to change the value in Cell S23 from any other value than "2%" or "1.5%". I would like to allow a student to also use any value between 1.5-2.0%, but it throws up "#DIV/0!" in a number of columns; which can not be "undone"!)

It appears the trigger is in the formula in Cells J80:J84. (The "4700" [Insurance premiums] would apply to 1.5%, or any value below 2.0%.)

I have tried three [unsuccessful] configurations of a formula, namely:

1. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM((J79+H80+I80)-N80)-4700,0)

2. =IF($S$23=2%,SUM(J79+H80+I80)-N80,0)+IF($S$23=1.5%,SUM(((J79-4700)+H80+I80))-N80,0)

3. =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80)

When I read through each element, it seems to make sense to me, but obviously there is something that I am missing.

I am not an Excel expert and have developed the sheet through a fair degree of Help Files and subsequent trial and error. If someone can identify how the formula should be written to overcome this isssue, I would be most grateful.

Regards,
Preceptor
Attached Files
]]> Formulas Preceptor http://www.excelguru.ca/forums/showthread.php?1787-Changing-cell-value-produces-quot-DIV-0!-quot-error-message-in-columns <![CDATA[Creating formulas & Macros]]> http://www.excelguru.ca/forums/showthread.php?1786-Creating-formulas-amp-Macros&goto=newpost Tue, 18 Jun 2013 21:20:25 GMT Hi, I'm looking for some assistance with excel. I have been attempting to set up the following but have had no luck: Basically, 'in the first box I would to type a start date 'Second box should find or show the available info for that date range (the range of dates and info will be available to use for formulas 'Third box should then generate a 'a link or file path for the correct set range of data I have listed' Now, I need some assistance as to how to set this up on excel, any ideas?

Thank you. ]]> Formulas salgunaidi http://www.excelguru.ca/forums/showthread.php?1786-Creating-formulas-amp-Macros Help required for League Table data entry http://www.excelguru.ca/forums/showthread.php?1785-Help-required-for-League-Table-data-entry&goto=newpost Tue, 18 Jun 2013 11:05:05 GMT Hi,

If someone out there would be willing to help me get the following set up to my specification I'd be more than happy to PayPal them the cost of a nice bottle of wine or a case of beer! If you think you can help please let me know.

-----------

I run a small poker league and enter the results each week into a table that I had some help designing recently. I now want to apply a formula that I've been given to the results each week that will improve the way we determine the league champion. I have very little knowledge of Excel and would appreciate it if someone could help set up this formula and the rest of the worksheet in such a way that I just enter the results each week and everything else is done automatically. I've attached a file that features two worksheets. The first is the old one which features all the formula's, look up tables and conditional formatting. The second is just a template with all formula's etc. removed. It's basically how I'd like the table to look when finished. I've pasted the new formula that should calculate the scores below the table. The first weeks results have all been entered manually. The part of the original table that calculates earnings has been left off the new worksheet as having added new columns for both place and points, the formula gave errors which I couldn't fix so this also needs to be updated!

Many thanks,

Brian

Table_02.xlsx
Attached Files
]]> Formulas Brian Thomas http://www.excelguru.ca/forums/showthread.php?1785-Help-required-for-League-Table-data-entry Exporting Data and splitting it up! http://www.excelguru.ca/forums/showthread.php?1784-Exporting-Data-and-splitting-it-up!&goto=newpost Tue, 18 Jun 2013 06:07:00 GMT hi guys, I have two sheet Program Plan and Staffing Plan-N. I want to export certain data from Program Plan to the Staffing Plan. The Range which...
hi guys,
I have two sheet Program Plan and Staffing Plan-N.
I want to export certain data from Program Plan to the Staffing Plan.
The Range which i want to export is E53:AJ57 (Program Plan sheet) to I5:AN10 in the Staffing Plan Sheet.
But the condition is .
If the cell value in the range is E53:AJ57 is more than 1 then it has to split into 1's so that no cell value in the range I5:AN10 is more than 1.
And the corresponding role in the column E has to be split up to.
The sheet is attached.I have shown in the below cells the expected result.
Attached Files
]]> VBA Programming devpp http://www.excelguru.ca/forums/showthread.php?1784-Exporting-Data-and-splitting-it-up! Transpose Data http://www.excelguru.ca/forums/showthread.php?1783-Transpose-Data&goto=newpost Mon, 17 Jun 2013 18:18:48 GMT I want to take the description to the left of ":" and make a column heading and put the detail to the right of ":" below the headings:

DBV:1
RTX:AE-941/PLACEBO
VIN:AE941PL
ALRDIN:9999999A
DBV:2
RTX:ALVAC CEA/B7.1
VIN:ALVST
ALRDIN:9999999A
DBV:3
RTX:ANASTROZOL MA 27 STUDY
VIN:ANA1TST
ALRDIN:02224135

Want it to look like:
DVB RTX VIN ALRDIN
1
2
3 ]]> Other General Questions Christine http://www.excelguru.ca/forums/showthread.php?1783-Transpose-Data Convert tenths of millimeters to decimal inches http://www.excelguru.ca/forums/showthread.php?1782-Convert-tenths-of-millimeters-to-decimal-inches&goto=newpost Mon, 17 Jun 2013 17:05:12 GMT Hello,

Is there any way to convert tenths of millimeters to decimal inches. Doing several separate conversions would be fine, but I can't even figure out how to do the first part: tenths of millimeters to regular millimeters (ie, 3 = 0.3 millimeters). In the format cells tools all I can find is how to ADD decimal places after the number, not before.

I know there's got to be a way to use a formula for multiplication, but whatever I try to do, I end up with a column of zeros.

Can anyone help? ]]> Formulas iamjak http://www.excelguru.ca/forums/showthread.php?1782-Convert-tenths-of-millimeters-to-decimal-inches Increment File Name http://www.excelguru.ca/forums/showthread.php?1781-Increment-File-Name&goto=newpost Mon, 17 Jun 2013 16:01:30 GMT In Ken's Code, the program deletes any existing file. Without adding a ton of additional code can this be easily incremented? Just add a 1 or a 2 after the file name so that someone does not accidentally blow the file out?

Code:
Sub PrintToPDF()

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim bRestart As Boolean


    On Error GoTo EarlyExit
    Application.ScreenUpdating = False
    Set pdfjob = New PDFCreator.clsPDFCreator
    '/// Edit Output file path ///
    sPDFPath = "C:\Users\pschmidt\Documents\New folder (2)"


    'Check PDFCreator
    Do
        bRestart = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        If pdfjob.cStart("/NoProcessingAtStartup") = False Then
            'PDF Creator is running: Kill the existing process
            Shell "taskkill /f /im PDFCreator.exe", vbHide
            DoEvents
            Set pdfjob = Nothing
            bRestart = True
        End If
    Loop Until bRestart = False


    For lSheet = 1 To ActiveWorkbook.Sheets.Count
       
        If Not IsEmpty(Sheets(lSheet).UsedRange) Then
            With pdfjob
                '/// Edit Output file name ///
                sPDFName = Sheets(lSheet).Name & Format(Now, " mm-dd-yyyy") & ".pdf"
                .cOption("UseAutosave") = 1
                .cOption("UseAutosaveDirectory") = 1
                .cOption("AutosaveDirectory") = sPDFPath
                .cOption("AutosaveFilename") = sPDFName
                .cOption("AutosaveFormat") = 0    ' 0 = PDF
                .cClearCache
            End With
               
            'Delete the PDF if it already exists
            If Dir(sPDFPath & sPDFName) = sPDFName Then
           
            'Print the document
            Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
   
            'Wait until the print job has queued
            Do Until pdfjob.cCountOfPrintjobs = 1
                DoEvents
            Loop
            pdfjob.cPrinterStop = False
   
            'Wait until the file shows up
            'Important:  Counter must reach zero or hangs on next iteration
            Do Until pdfjob.cCountOfPrintjobs = 0
                DoEvents
            Loop
        End If
    Next lSheet
   
Cleanup:
    'Release objects and terminate program
    Set pdfjob = Nothing
    Shell "taskkill /f /im PDFCreator.exe", vbHide
    On Error GoTo 0
    Application.ScreenUpdating = True
    Exit Sub


EarlyExit:
    'Inform user of error, and go to cleanup section
    MsgBox "There was an error and the file was not created.  PDFCreator has" & vbCrLf & _
          "has been terminated.  Please try again.", _
          vbCritical + vbOKOnly, "Error"
    Resume Cleanup
End Sub
]]> VBA Programming p_smith http://www.excelguru.ca/forums/showthread.php?1781-Increment-File-Name Difference in Value in the Program Plan sheet and Actuals Sheet! http://www.excelguru.ca/forums/showthread.php?1780-Difference-in-Value-in-the-Program-Plan-sheet-and-Actuals-Sheet!&goto=newpost Mon, 17 Jun 2013 04:46:40 GMT Hi Guys, I am confused in doing this and this is little complicated for me also. I have already done a Program Plan sheet.In this sheet we enter...
Hi Guys,
I am confused in doing this and this is little complicated for me also.
I have already done a Program Plan sheet.In this sheet we enter the plan the project in sprints (each weeks is called as a sprint-June 3rd is the first sprint week followed by June 10 and so on.)

In each sprints there are different phases of the project (say Phase "Dev" under June 3rd) For Phase "Dev" the resources are

BA-0.25
GD-0
TL-0.25
Dev-1
PM-0.2
QA-0.25

Its for Dev phase.

Suppose there is another phase for the same sprint.Then the resource value changes...i.e it increases.

Now there is another sheet called the Staffing Plan.
In Staffing Plan the actuals are entered.In the former sheet the forecasted values are entered.

In staffing plan the actual emp id and employee name are entered and their role is given.(say : emp name "a" and Role is Dev).

My requirement is

When the staffing plan role exceeds the role in the program plan then there should be given a warning to the user and the extra value should be added as a buffer value.(i.e. column F in staffing plan sheet)
the buffer values have to added seperately and actuals should be entered seperately in the Staffin Plan sheet.
So that i can find the difference from the Program Plan sheet and Staffing Sheet.

My aim is to find the difference between the actual values and the predicted values.
Program Plan sheet-Predicted values.
Staffing Plan sheet-Actual values.

This verification has to done and carried out for each sprints.

Can anyone help me on this.
Thanks a lot for reading this...please help on this too.
Regards,
Devpp

:frusty:
Attached Files
]]> VBA Programming devpp http://www.excelguru.ca/forums/showthread.php?1780-Difference-in-Value-in-the-Program-Plan-sheet-and-Actuals-Sheet! Delete first rows and keep later ones http://www.excelguru.ca/forums/showthread.php?1779-Delete-first-rows-and-keep-later-ones&goto=newpost Sun, 16 Jun 2013 21:21:43 GMT I am struggling to work out a problem I hope you can help with. I have an Excel spreadsheet with 10 columns of data and 1000+rows. The first x... I am struggling to work out a problem I hope you can help with.

I have an Excel spreadsheet with 10 columns of data and 1000+rows. The first x number of rows are called "Group 1" identified by the word "Group 1" in column A of each row. The next x number of rows are identified in the same way as "Group 2" and so on. The number of rows in each group can vary from 4 to 40.
Later in the list another batch of rows may also be identified as "Group 1". In fact there may be several batches of rows tagged as Group 1. Although they are tagged as Group 1, they may have a different number of rows than a previous Group 1.
What I need to do is:

Wherever a group is repeated in my list, I need to KEEP only the last occurrence of the group (all rows) and delete the rows from all earlier occurrences of that group.

I have made reference to Group 1, but, similarly, any of the groups can be repeated and I will only want to retain the last occurrence on the list. ]]> Formulas s1lverface http://www.excelguru.ca/forums/showthread.php?1779-Delete-first-rows-and-keep-later-ones Doubt http://www.excelguru.ca/forums/showthread.php?1778-Doubt&goto=newpost Sun, 16 Jun 2013 10:50:00 GMT Hi All,

I have some around 1000 orders numbers. i need to input in company website & have to downland pdf. & save it in same order numbr..pls will provide some idea, so that i can make my own...code. ]]> VBA Programming vijaydm http://www.excelguru.ca/forums/showthread.php?1778-Doubt Project timeline management http://www.excelguru.ca/forums/showthread.php?1776-Project-timeline-management&goto=newpost Fri, 14 Jun 2013 21:09:02 GMT Hey guys,

I'm curious what software you all use for managing project timelines. We're just ramping up into a massive budget cycle here (3 months from start to finish), and we have to present a budget schedule to our asset manager. For years I've been using an Excel spreadsheet (what else) to manage this, but I hate it.

I've toyed with the idea of using Project, even attempting to put it in once or twice, but found it so tedious to work with that I ended up going back to Excel.

Does anyone have a favourite software for managing project timelines and deadlines? Is it Excel? Just curious to see what others are doing... ]]> Other Business Applications Ken Puls http://www.excelguru.ca/forums/showthread.php?1776-Project-timeline-management Error 1004 Select method of range class failed http://www.excelguru.ca/forums/showthread.php?1775-Error-1004-Select-method-of-range-class-failed&goto=newpost Fri, 14 Jun 2013 16:33:39 GMT Hi, I have this code that I am trying to generate a report Code: --------- rivate Sub Cmdprintpreview_Click() Dim GetDate Dim... Hi, I have this code that I am trying to generate a report
Code:
rivate Sub Cmdprintpreview_Click()
 
    Dim GetDate
    Dim strMonth, strYear
    Dim ws As Worksheet
 
  Set ws = Sheets("Returns")
 
    'get values from comboboxes
    strMonth = Me.Cbomonth.Value
    strYear = Me.Cboyear.Value
   
   
    GetDate = strMonth & "/" & strYear
   
   
    ws.Range("Period").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:=GetDate
   
    Unload Me
    ActiveSheet.PrintPreview   
    Selection.AutoFilter 
    FrmReport.Show
I am getting this error can anyone help thanks ]]> VBA Programming jams http://www.excelguru.ca/forums/showthread.php?1775-Error-1004-Select-method-of-range-class-failed Circular reference error http://www.excelguru.ca/forums/showthread.php?1773-Circular-reference-error&goto=newpost Fri, 14 Jun 2013 13:12:29 GMT How can I code the C equivalent of X=X + value without getting a circular reference error in Excel ? Many thanks for any help How can I code the C equivalent of X=X + value without getting a circular reference error in Excel ? Many thanks for any help ]]> VBA Programming igardos44 http://www.excelguru.ca/forums/showthread.php?1773-Circular-reference-error Create Subfiles from a master file using key to split workbooks to seperate files http://www.excelguru.ca/forums/showthread.php?1772-Create-Subfiles-from-a-master-file-using-key-to-split-workbooks-to-seperate-files&goto=newpost Thu, 13 Jun 2013 14:37:02 GMT Hi, I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the... Hi,


I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet. Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location. Any help on this is much appreciated. Thanks.
Code:
Sub Create_Subfiles()



    Dim FDName          As String
    Dim FBName          As String
    Dim DIName          As String
    Dim WName          As String
    Dim FPath          As String
    Dim BWName          As String
   
    'File names and directory path
    FDName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f14").Value
    FBName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f13").Value
    DIName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f15").Value
    WName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f12").Value
    TOTName = Workbooks("Sales Forecast Template.xlsm").Sheets("Hierarchy").Range("f16").Value
    FPath = "C:/desktop"
 
   
   
   
   
   
   




   
   
   
    'open template files
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\1.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\2.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\3.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\4.xlsm"
    Workbooks.Open Filename:= _
        "P:\Sales\SalesFinance\Sales Forecast\Template\Template Files\Total.xlsm"
    Windows("Sales Forecast Template.xlsm").Activate




    'move worksheets to proper workbooks and save them to correct directory
    Sheets(Array("Sheet1", "Sheet 2").Sheets(1)
    Sheets(Array("Hierarchy", "Couponing", "Sheet1")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Range("A2").Select
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FDName, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close False
    Windows("Sales Forecast Template.xlsm").Activate
   
    Sheets(Array("Sheet 5", "Sheet 7").Sheets(1)
    Sheets(Array("Hierarchy", "Couponing", "Sheet1")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Range("A2").Select
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FBName, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close False
    Windows("Sales Forecast Template.xlsm").Activate
















ThisWorkbook.Close False
End Sub
]]> VBA Programming balla506 http://www.excelguru.ca/forums/showthread.php?1772-Create-Subfiles-from-a-master-file-using-key-to-split-workbooks-to-seperate-files URGENT: TimeR in access 2007 http://www.excelguru.ca/forums/showthread.php?1771-URGENT-TimeR-in-access-2007&goto=newpost Thu, 13 Jun 2013 11:25:10 GMT I have a problem.:( I need to put *timer* who will *show notification or some alert* if 2h are past after the time who is insert in the table... I have a problem.:(

I need to put timer who will show notification or some alert if 2h are past after the time who is insert in the table exp.table Product column- ProductTime

Thanks! ]]> Microsoft Access MagMil http://www.excelguru.ca/forums/showthread.php?1771-URGENT-TimeR-in-access-2007