Microsoft Excel

136K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi Experts

I received some amazing support on the attached sheet whereby basically I was given some superb direction in adding a summary sheet that calculated the instances of ‘red’ cells in the Air, Sea and Road sheet by using the dates in column D on each of these sheets and returning a monthly summary on the Summary sheet

The issue is on the Summary sheet, on the Monthly Overview section (rows 10 to 19) For Jan and Feb the code appears to be working but not from March onwards?

I cannot see why this is happening as the date format for all entries appear to be the same and I don’t see anything has changed across the three entry sheets?

If anyone could help in rectifying this I would be very grateful (we have an audit soon …)

Thanks
J
Compliance-Checks-2019.xlsm
0
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Strange behavior with Excel - 2-56 = 20486   and   8-32 = 11902 ????

I was trying to type ANSI Machine Screw Sizes.  They are all weird.  

I can get it work with a leading quote or space, but how is Excel coming up with these numbers?
0
In the 2 attached files from Excel 2016, I want to compare the identifier column from clients 1 workbook, with the network login ID column in the clients 2 workbook and find which records are in both workbooks. The results can be put on a different sheet in either workbook if necessary, I just need to know where the data is the same.
Clients-1.xlsx
Clients-2.xlsx
0
In excel I can move a column by highlighting it, holding shift and placing it at the bottom border of the column heading and dragging it where I would like it to go. I like the method but it seems to take a lot of mouse control to get the cursor to change and I end up needing a second or third go at it. I could just copy and insert the cut cells. But I wonder if there's some way to make the drag and drop method easier. Is there any means to make the column identifiers (A B C D..AA BB CC DD..) bigger to it's easier to land the cursor on the right spot? Or perhaps there a means of modifying the method for letting excel know you want to drag a column somewhere else?
0
I need to convert dates to fiscal year and fiscal quarter in Excel. The fiscal year ends Sept 30th. Thanks
0
Excel left or right formula. I have a value in cell A1 as follows:

"Ohio: The Message"

I need a formula in cell A2 that will give me everything before the colon (i.e. Ohio).
0
Hi

I have a workbook that has 2 sheets and data in columns A-D what im trying to do is in column A on both sheets there is a unique 4 digit number of which is duplicated numerous times and then changed to a new one.

Im trying to somehow automate it so it sets up the filter in column A in both sheets and then selects the first number to filter it out in sheet 1 and then filter out same first number and data in sheet 2.

I then  need it to do an auto sum down column D of both sheets and if both total amounts agree (totals in sheet 1 & 2)  then thats fine leave it alone and move onto the next unique number and repeat function.  Dont worry about saving autosum.

If both total amounts dont agree I need it to compare data against each other in each sheet.  If the data's in sheet 1 but not sheet 2 then I need that row of data highlighting in sheet 1.

If all the data in sheet 1 & 2 are both present but still dont balance I then need it to look in sheet 2 only and look for duplicated entries. As far as I mean by duplicated entries all the data in columns A-D .

I then need any duplicated entries highlighted too.


Any Ideas?
0
need to adjust the CONVERT 0 NUMBER 2N   button
right now this button works when column A has 3 digits

but this file is being adjusted for 2digits

I am adjusting column A of 3 digits to 2 digits and all its buttons in this file

a102.PNG
Any question is welcome
29151977.xlsm
0
How do you create an array formula in vba that returns the resultset of a sql query?

I have the sql/recordset part working already...
0
when trying to do mail merge , I am getting this error. (attached)

I am running office 2010
Capture1.PNG
0
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Excel - I need to count how many times a number occurs is a column.
0
Looking to filter an Excel csv file that has 17 columns. 1 Column is where I'm looking for 'xxxx' and once found I want to export that finding along with the other 16 columns into a new excel sheet.

Never used powershell to filter excel and been researching online for a bit, but was hoping I can get some help.
0
Excel - Flagging like items based on condition

Please see attachment.
LikeItems.xlsx
0
Excel - I need to look at column  A and made a determination based on the value and place a word in Col  B.
If   A2  is less than  673  put "Short" in Col  B2
If   A2  is between  674 and 2016  put  "Mid"  in Col  B2
If   A2  is greater than  2017  put  "Long"  in Col  B2
0
I have a large column of numbers in Excel. I have a name that will remain the same.

I want to populate many pages, based on a template, from the Excel data. One page per number. Numbers and pages need to be kept in the same order.
Example.docx
Example.xlsx
0
need to adjust the COUNT HITS  RELATIVE   button
right now this button works when column A has 4 digits

but this file is being adjusted for 3 digits

I am adjusting column A of 4 digits to 3 digits and all its buttons in this file

Any question is welcome


a101.PNG29151977.xlsm
0
Depending on the value entered into a cell, I need to either go to the next column, or jump 5 columns.   The cell has data validation and only has 2 possible values.
0
I am using Windows 10 and MS Office 365.   It's been a long time since I've used Excel, and forgot which function allows a specific cell value to change based on specific parameters in another cell.  For example, assume I have a spreadsheet that calculates an investment return on a land purchase - and the return is 5%.  I want to know how the land purchase price would have to change to make the return 10%.  Can you help??

Thanks,

Phil
0
need to adjust the  RELATIVE   button
right now this button works when column A has 4 digits

but this file is being adjusted for 3 digits

I am adjusting column A of 4 digits to 3 digits and all its buttons in this file

Any question is welcome

a98.PNG29151916.xlsm
0
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

need to adjust the  Digit Summary   button
right now this button works when column A has 4 digits

but this file is being adjusted for 3 digits

I am adjusting column A of 4 digits to 3 digits and all its buttons in this file

Any question is welcome

a94.PNG29151913.xlsm
0
adjust  in DTA 2 sheet  Count Hits button

a96.PNG29151908.xlsm
0
adjust Convert 0 3N button to work with 3 digit

a95.PNG29150996--1-.xlsm
0
Hello experts,
I was wondering if someone can help me with a code to import/copy worksheets located in another workbook to active workbook based on userform.
In the userform I will specify the workbook path in which is reported worksheets and file name worksheets.

Checking and error handler:
1-If workbook path doesn't exists exit sub "Workbook path reported doesn't exist"
2-If worksheets reported doesn't exist exit sub "Worksheet doesn't exist"

If worksheets exist in active workbook they should be imported and renamed with (2) as it does excel when you copy a worksheet
It would be great if I can report multiple worksheets in the user form.
If you have questions please contact me.
0
Why does my Excel SQL statement sort have different results from a manual sort

Table - Before VBA query
Account Sales-Amount   Tax-Amount
312          102.00                10.00
312           101.00                10.00

SQL Query in Excel
...SQL...Select.....Order By Account,Tax Amount

Table After SQL Query
Account Sales-Amount    Tax-Amount
312          101.00                10.00
312           102.00                10.00

If I remove the Order BY in the SQL statement and manually Sort by Account and Tax Amount (smallest to largest) I get the below
Account Sales-Amount    Tax-Amount
312          102.00                10.00
312           101.00                10.00

Why does the SQL query change the sort - given that the Tax Amount is the same in both rows - shouldn't it leave it as is...with 102 in the fist row ?
0
Been working on a formula or function that would give the totals of values based on the year the transaction.  the attached excel has 3 years of transactions.  I need to place the totals per year in the columns I, J and K (as I did manually in the excel by filtering the years and copying it over to the years columns).  In real life the excel shas over 30 years of data that is being changed based on the contents sent to me, but the format stays the same.

In the excel I placed filters so you can filter by year and see the results (which I copied over in the columns I, J and K).

I tried working around the formula i have used of =SUMPRODUCT(SUBTOTAL(3,OFFSET(D5:D926,ROW(D5:D926)-MIN(ROW(D5:D926)),,1)), --( D5:D926=D1)), in order to place the values in the columns I, J and K, but can't seem to include a way to read dates and only extract based on the year.

I hope I have explained correctly (I included the excel so it could be as clear as possible).

Thank you very much Experts.
CountBasedDates-others.xlsx
0

Microsoft Excel

136K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.