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
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!
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=" ")
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.
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.
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.
Dim wbOpen As Workbook
Dim wbNew As Workbook
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
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: firstname.lastname@example.org / Phone: USA-Phone# 929-800-3813, skype: info.analytics
Looking for excel formula to calculate percent of time using standard deviation and mean.
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?
and VBA code
For Each MyHTML_Element In IE.document.getElementsByTagName("a")
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.
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?
as attached images, I need to calculate interest rate according to thecondition.
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
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.
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:
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.
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.
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.
Solution to "Macro code that will repeat x number of time based upon a cell in my excel spreadsheet"Submitted by Almir on 29 May, 2014 - 20:57
This is solution to post at: http://excelexperts.com/macro-code. Example file is attached.
Solution to: http://excelexperts.com/budgeting
Solution to: http://excelexperts.com/comparing-data-sets
Thanks "Mr Spreadsheet" John Walkenbach for this formula.
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.
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
I want formuls for retail pick qty form store ,,,
find out greatere qty avalable store name what we want
Please try this to transpose the vertical values to horizontal Values.
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
WASHINGTON STATE PRODUCER
8067 SW GLENWOOD RD
HARRIS & MOURE
600 STEWART ST STE 1200
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.