Blogs

Nick's picture

Nick's Day By Day Excel Experts Blog

Calculate a list of Averages from a Timesheet

Hi Guys

I want formulas that will calculate a summary of averages from the timesheet attached(actual spreadsheet has over 50,000 rows with 12 employee). My aim is to be able to see, when I expand each subtotal/month, the average hours worked per day/week/month for the month/subtotal and in the same manner as the number of counts for all employees - summarised below the table (highlighted yellow).

In other words, each subtotal will have its summary of averages below the table when I collapse it. Cells without data should be ignored in the formula

Many thanks

Freeze first tab in first position

Hi,
I know that Excel doesn't allow you to freeze one tab in the first position (i.e. making sure that a user cannot insert/move another sheet to the first position). Are you able to offer any suggestions on some VBA code that will do this for me please? I'm stuck!

Formula issue

Hello,

I have a worksheet titled Sample on which cell B5 is the due date and cell B4 is the completion date. I want to use conditional formatting on a separate worksheet that displays a dashboard of the project in which a cell will fill red if the due date is today or has passed but the completion date is blank. I'm doing something wrong with the formula but cannot figure out what. Any help would be greatly appreciated.

=AND(TODAY()>=Sample!B5, Sample!B4=" ")

Formula to automatically assign the next sequential number to documents in an Excel register

I'm creating a register in Excel to number engineering documents. Each document will have a unique number and will follow this numbering structure:

Discipline Code - Document Type - Sequential Number

Example: ELE-DRW-0001. This is the 1st electrical drawing in the register.

I'm trying to find the formula that will assign the next sequential number to new documents depending on their discipline code and document type code.

Example: If I am registering a new electrical drawing, I'd like Excel to give it 0002 as the sequential number.

How to Fix “Excel 2013 has stopped working” Error Message

I am a regular user of MS Excel and my complete work is dependent upon it. Recently, I have encountered an error with it, and it was 'Excel 2013 has stopped working'. Every time, I opened Excel, it crashed, and gave me this error. It annoyed me a lot, and I have decided to resolve it by myself. Here, I am describing the solution steps, which I have followed to fix this error.

need help with copy and paste code

the code works fine but I need to change it copy and paste the spreadsheets it collects from my other files to the current workbook where the code resides. It currently saves a to new workbook.

Sub CopySameSheetFrmWbs()
Dim wbOpen As Workbook
Dim wbNew As Workbook
'Change Path
Const strPath As String = "C:\Users\lap\Desktop\file2\master1\"
Dim strExtension As String

'Comment out the 3 lines below to debug
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next

ChDir strPath

NickMoran's picture

Excel VBA Reports and Auditing Expert

Hi,

I'm interested in working as Excel - VBA Reporting freelance work.

Currently I'm working as Reporting Analyst in Hyderabad, India.

I have worked over 7 years in Excel Reporting, VBA programming and also Auditing.

If you are interested please get in touch with me via email: nickkmoran@gmail.com / Phone: USA-Phone# 929-800-3813, skype: info.analytics

Regards,
Nick

Excel help with a Formula

Looking for excel formula to calculate percent of time using standard deviation and mean.

Have existing macro available to open on each new Excel spreadsheet

I need to figure out if it's possible to have my EXISTING macro set up so that each time I open a NEW spreadsheet in Excel, I can run the macro in the same spreadsheet.

For example, if I export a set of data into Excel from another program, and the Excel spreadsheet opens with the data, I want to be able to click on "Macros" and be able to choose the Macro I need right there.

Can anyone please help me set this up?

VBA code to execute Java-script on webpage

Hi

I am stuck in automation of a website by VBA. It has Javascript under a dropdown menu(Download). I wrote down VBA code but neither it gives any error nor it executes the Java script. Here is HTML code

  • Download
  • and VBA code

    For Each MyHTML_Element In IE.document.getElementsByTagName("a")

    Product Review: Stellar Phoenix Excel Repair Software

    Verdict: Stellar Phoenix Excel Repair is a professional tool to fix corrupt or damaged Excel files and restore the Excel objects to a new blank Excel file. The tool supports XLS and XLSX file repair from any reason of corruption. With this tool, you can recover objects like charts, chart sheets, cell comments, worksheet properties, and other Excel data.

    How to delete undesired rows

    I have reports in which no. of rows has been changed. I have create a macro to sorting the report. Its work fine with 144 rows. But whenever there are more rows in report macro still shows first 144 rows. How can I program my macro to work accurately?

    k33jakha's picture

    Need formula assistant for calculation of Intrest:

    as attached images, I need to calculate interest rate according to thecondition.

    Help Me!

    Excel Interest calculation

    Can i use a date picker to go to worksheets that are named as dates

    I have a workbook that has 365 worksheets, one for everyday of the year. They are named by date. Can I use a date picker to go to the corresponding worksheet. I am using Excel 2013

    How enter data in multiple rows of excel, by calling them from VBA Macro?

    Hi,

    I have a excel database, which contains different classes e.g. Class-1, Class-2 and Class-3 and every class has 10 different names. Now I want to create three macros by the name Class-1, Class-2 and Class-3, so that if anybody in that excel sheet writes =class-1, then the names which are linked with class-1, shall displayed in excel rows. Simillary on the same sheet other classes may also be called, when required.

    If anybody can solve this, it would be a great help.

    BR
    Deepak

    How to Open a Corrupt Excel File

    When you are not open an Excel workbook, it means, your MS Excel file are damaged. The Microsoft Excel Application already provide AutoRecover feature that enables the damage file to be re-opened and simultaneously Fix it. But, however, the application of Excel are not always start the file recovery process each time, you attempt to open a damaged workbook. In such condition, you must attempt manual Repair process. All manually Repair process mentioned below:

     

    How to Recover Data from a Damaged Microsoft Access Database Table

    Microsoft Access database tables hold all the data within it. At times, databases tend to get damaged due to various reasons and exhibit error messages. You can face problems when you perform various functions. Some of them occur when any one of the following conditions comes in front.

     

    Database Programming using VBA/Excel

    Hi,

    Can anyone please instruct me how to use my current worksheet containing 5 columns as a table in a relational database? This way, I can use codes like SELECT....FROM....WHERE...ORDER BY... as in database programming.

    I am new to VBA and I apologize in case my question is trivial and ill-framed.

    Thank you very much.
    ---Preetam

    Top 5 Methods to Repair your Corrupt Excel 2007 File

    MS Excel is widely used for storing and managing data in both text and graphical forms. Being a part of MS Office suite, Excel is used in various companies, colleges and institutions, and at many other places for keeping data in organized form with the help of its inbuilt formulas, formatting techniques, filters, and other internal functions. One of the major features of MS Excel application is data portability, which makes your data accessible on any Windows based computer.

    Almir's picture

    Solution to "Macro code that will repeat x number of time based upon a cell in my excel spreadsheet"

    This is solution to post at: http://excelexperts.com/macro-code. Example file is attached.

    Macro code that will repeat x number of time based upon a cell in my excel spreadsheet
    Almir's picture

    Solution to Budgeting

    Almir's picture

    Example of calculated pivot field to compare two data sets

    Almir's picture

    Calculate Easter Date

    Thanks "Mr Spreadsheet" John Walkenbach for this formula.

    Calculate Easter Date
    Almir's picture

    Add a custom command to your right mouse-click menu

    When using particular macro often, it is handy to have it on right mouse-click. In this example there is a simple macro "Show Date and Time", and it was added to right mouse-click menu.

    Supposing you have a macro called "DateAndTime" in a "Module1", copy code from attached .TXT file to the "Workbook" part in VBE.

    With slight modification you can add more commands to the right mouse-click menu. It was explained in the comments.

    If you want custom commands available on right mouse-click, regardless of the file you work on, copy the code to your "Personal" macro file.

    Add a custom command to your right mouse-click
    Nick's picture

    Comparing Data sets

    An excellent way to compare data sets with the same column headings is to list them under each other, and add a new column called "source", and populate that source.

    Then create a pivot table, and add source as the column field.

    Then you can easily spot differences. Here's an example for comparing Trade PVs

    Retail

    I want formuls for retail pick qty form store ,,,
    find out greatere qty avalable store name what we want

    Regards
    Bhagwat Tambe

    Vikas Verma's picture

    TRANSPOSING WITH FORMULA

    Hi,

    Please try this to transpose the vertical values to horizontal Values.

    =IFERROR(OFFSET(INDIRECT("A"&SMALL(IF((ISBLANK($A$1:$A$35))*ROW($A$1:$A$35)>0,ROW($A$1:$A$35)),ROW($A1))),COLUMN(A1),0),"")

    with CRTL+SHIFT+ENTER

    Thanks

    Transposing

    I'm trying to transpose (A) to (B), but every time I do this it just strings the text across one row. I need each address to to be read horizontally in a row with a blank cell between each address in the column. I have 1155 address in the column I don't want to transpose one at a time. I want to copy all 1155 address and have them transposed in the format I need. See (B) Format Needed

    (A) Existing

    WASHINGTON STATE PRODUCER
    RICHARD ANDERSON
    8067 SW GLENWOOD RD
    PORT ORCHARD
    WA
    98367

    (Blank Cell)

    AMERICANNA NATURALS
    HARRIS & MOURE
    600 STEWART ST STE 1200
    SEATTLE
    WA
    98101

    Almir's picture

    An Example of Bracketed Column Name

    This is an example based on post at: http://excelexperts.com/brackets-in-formulas

    Bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[meals])" means "SUM of 'Meals' column", where "Meals" is a column within Data Table.

    Maybe "Sum" cell was copied away from a Data Table, and that is why it looked strange.

    Bracketed Column Name in Data Table
    Syndicate content