Posts by Oracle2054

    Re: formualae to export row from one sheet to another paid invoices


    Copy the following code into a VB module and run it and it will do what I believe you are trying to do. There is unfortunately no formula that I am aware of that would do what you are wanting that can show them all like this so VBA is the only way to go here. It looks up the value in column E and checks for "Paid" if it finds it then it copies that row into the "Remittances" Sheet. Hope This Helps!!!

    Re: Use list of 400 text 'C:\path\[filenames.xls]SheetNames'! to pull cell values


    Paste The Following code into your VB and it will do what you want I believe. Now there is a few things I need to point out as I'm not entirely sure the format you are wanting to use to grab the files. Basically you type into column A all the names of the files just the names. In the code where it says FolderPathName put the path to the folder where all of these are stored and it will do the rest. It will paste the results onto a new sheet called "Results" that it makes. Also it will only look at Sheet1 on these other workbooks and it will select only cell B9 so just making sure that is correct. Hope This Helps!!!!

    If you have any further questions I'd be more than happy to help or if you were looking for something a little different I just need a little more info or an example sheet of what you are looking for but this should be good.

    Re: Excel concatenate / rollup list of names macro


    Paste the following code into your VB and it will do it make sure the sheetname in VB shows it as Sheet1 and the results will display in sheet2 if you wanted to change it to the tab name then use the code in the second code box. Otherwise just use the code in the box directly below this not the bottom one. Hope This Helps!!!!

    Re: Auto copy/paste and rename sheet from text box and command button


    Paste the following code into VB and link it to the command button and it will grab the name from a textbox labeled textbox1. Then it will rename and copy everything from the template sheet now for the template sheet you will need to rename the sheet to whatever you call that sheet and it will work. Hope This Helps!!!

    Re: How To Run code Before Print Preview


    Paste the following code into VB in the "ThisWorkbook" sheet and it will run before a print preview or before printing. Hope This Helps!!!!

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    'Insert Code Here
    End Sub

    Re: VBA script to collect data from multiple worksheets

    I can help you with this I will post the code within the next 15-30 minutes. Now on this all you are wanting is to have the data pulled from multiple worksheets to the summary sheet for any cert # that matches the elevator number that is entered into the summary sheet.

    Re: Vacation Tracking Formula

    To account for any rollover time the formula will need to be changed a little if there is a place you store that information just link it with column H like you did with column E. You will need to add a column to account for the rollover time from last year and there is not a formula that i am aware of that will always count the rollover vacation time so it will have to be changed each time there Hire Date hits again the amount of vacation that was unused would need to be put into column H. Below is the revised formula to account for rollover time entered into Column H.


    Re: Vacation Tracking Formula


    Im Sorry for the delayed respons been real busy at work but I did take another look and if you are wanting to do the calculation based on when they were hired then it will be a little different the spreadsheet i have attached has the formulas and the correct information is displayed it will give you the amount of accrued vacation based on there hire date to today. I did have to add another column I highlighted it in yellow to calculate the number of months they have been working since the hire date as i couldnt put it all into one simple formula because it would be to many if statements.

    Hope This Helps!!!


    Re: Vacation Tracking Formula


    In the Spreadsheet it looks like you are trying to get the accrued amount of vacation time based on the month which if this is the case then the formula is right as it will take right now the month which right now is 10 then it minuses 1 which makes it 9 and 9*1.67 for instance would be the case in line 29 would be 15.03 which is correct. Now with this if it hit december it would never show there full amount of vacation time as it would take december month of 12 -1 which is 11 and would never calculate december fully the following forumla takes care of that by simply getting rid of the -1's


    Now from what your forum states it says your wanting the actual full amount of days that the people get a year if that is the case then the following formula will do that.


    Hope This Helps!!!!

    Re: Auto update of table for dynamic chart


    I have attached a copy of the document with the best thing that I can think of it works fine what I did was inserted a bit of VBA code to run when the Pivot Table Refreshes and places the data in without using formulas so that your Column E will remain blank and not show up on your chart this should suffice. Hope This Helps!!!

    This was the code that was added to run with the pivot refresh and past the data into Column E.

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim X As Integer
    X = 3
    If ActiveSheet.Range("B" & X) > 0 Then ActiveSheet.Range("E" & X) = ActiveSheet.Range("B" & X)
    X = X + 1
    Loop Until ActiveSheet.Range("A" & X) = ""
    End Sub

    Re: Function to print a single date for the week in following column.


    Here is a function that I have made to do what you are wanting I have had to do this myself for work to find deadline dates. Also I noticed just a quick tip might help you I see you did a formula in column D to get one number higher this can also be done by just typing 1 into the first cell of that row and then autofill down there is a small black box in the righthand corner of the cell when you click on it and then drag down and a box will appear when you finish dragging it click that little box and hit fill series and it will do that for you without the formula in there just a quick tip same for the dates in column A. Hope This Helps!!!

    Re: Interdependent Relative Reference formulas on two different sheets - Do I Lock on


    I have taken a quick look at the problem and from what I am gathering you are deleting the row in the MultiSite Data Tab and it then messes with the formulas in column S on the Regulated Tarriffs Tab. If this is the case then you need to clear cell contents or simply hit the delete key rather than fully deleting the cells because this will cause the references in the cells to that particular row in the MultiSite Tab to (in a sense) no longer exist because they were deleted. Hope This Helps!!!

    Re: Delete Dup tickets If The Value of the two or more duplicate tickets is lower tha

    Copy the following code into your VBA and this should do what you want it to first sorts the data so that it can be easily identifiable on which ones match then it searches through the ones that match only and looks at the value in column B if it is less than 1800 it will be removed. Hope this Helps!!

    Re: Open Word File and GoTo bookmark

    You were very close the following code will work for you be sure you have Microsoft Word Library referenced. Hope this helps!

    Re: Pull external data from same sheet in other workbooks


    This code will pull the data from the other files as specified above. Just copy this into your workbook and rename the files and paths in the code accordingly to match the filenames and paths that you are working with.

    Re: macro to go back to previous worksheet

    Try this it is a hyperlink it should suffice from what I gather you are trying to do.

    =HYPERLINK("[Filename]Sheetname!A1","Go Back")

    The Filename is the full name of the file for instance is i have a file called IdeaLog.xlsx and i had 3 sheets called sheet1, sheet2, and sheet3 the link would look like this

    =HYPERLINK("[IdeaLog.xlsx]Sheet2!A1","Go Back")