Microsoft Excel

134K

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

The Close 'x' button, highlights on a red background, and is located at the top right of the main Excel Window

I can use the API functions GetSystemMenu Lib, DeleteMenu Lib, EnableMenuItem Lib - to respectively disable and enable the the Close 'x' button (https://excelprovegue.wordpress.com/2016/11/06/disable-close-button/).

But I want to HIDE / DISPLAY that 'x'control.
How do I do that?

Thanks! Kelvin
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Merge 2 Excel 2016 Files to single file and group records

I have 2 files, each with 4 columns:

Email Address - First Name - Last Name - Type

My goal is to merge these files so that records with same Last Name values will be grouped in the new file, that is the records will be grouped - sorting of fields not necessary.

How do I do this?
0
I have an Excel spreadsheet that is being cut off on the right side both on-screen and print preview/printing. The file works on other machines with no issue. I have confirmed printer settings are all identical.

Image of how it appears on problematic machine.
https://ibb.co/gwtWZJ
0
How to write php output to excel? more than 100 rows and each 10 column. Platform linux.
0
In the given sample in sheet-1 there is a column called skill where filter is applied.
What I need to do is apply filter for each skillset and then count the frequency of numbers under the column baseline level and then place the frequency of each number into sheet-2 under L1,L2,L3 of the current level.
for eg-
in sheet 1 filter the skills column by lets say, the skill "Angular JS".
Therefore, now the baseline level column contains only entries i.e 0 and 1. So count the occurences of 0s and then in Sheet-2 look for the row with the entry Angular JS. After that fill L1 (current level) with 1 (as 0 only occurred once) and L2 with 1(as 1 has occurred only once).
finaltask.xlsx
0
Good Morning,

I have a simple excel template that keeps a record of job..

I want to add some functionality using VBA so that when a job is added the the spreadsheet it automatically emails the row data for that job.

I would like the VBA to run and check for new data when the spreadsheet closes automatically together with the email

Jobs.xlsx
0
Hi ,
    I am looking for an excel macro( or anything to get the functionality) to do following
   
   In  my excel sheet (Excel 2013)    Column A  ( A2 - A300)   has list of folders ( RelativePath)      Source is  D:\Vault  (For example ,if the relative path is  FoldMain\Fold1  the fullpath is D:\Vault\FoldMain\Fold1)

    The macro need to copy the folders in the list to a given relative destination ( For example   D:\Vault\FoldMain\Fold1   should copy to D:\VaultCopy\FoldMain\Fold1
     
      The folders in the list can have subfolders in it,   the subfolder also should be copied    (For example if D:\Vault\FoldMain\Fold1  has a folder Fold1Sub in it, that should be copied )
0
The examples given to simulate a MAXIFS are array entered against a given set of cells. Is it possible to  do something similar using table entries?
0
I wish to assign an object (a/Excel-.jpgb/c) to a person (1 or 2 or 3) based on the following (see image)      

The lat/long of the object must be equal (=) to or between +/- 0.05 the lat/long of the person.

The Max/Min wait days for the object to be assigned to person (1 or 2 or 3) can between 27 to 34 days (excluding sundays)

The total and maximun objects to assign to a person is 5. When this condition is met then assign object to next available person who meets the criteria of number 1

Is there a way of doing this in excel, I would appreciate help with this formula
0
I need to use the sumif function to do the summation for each change in column z in the attached excel sheet
sumif.xlsx
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Need a macro to calculate SLA for the assigned incidents. SLA calculation based on below conditions:
Status columns: Critical,High,Medium and Low (provided SLA time based on status)
Critical = 15 min
High= 30 mins
Medium= 1440 mins
Low=1440 mins
Response SLA: to be calculated from the time incidents is assigned to our queue within business hours i.e 9AM to 5PM, anything outside business hours should be calculated from next day 9AM also excluding weekends (Sat,Sun). if There is any incident which is going to breach the SLA, can an auto generated email be sent to the assignee from assignee column stating that incident will be breached?

Resolution SLA: Critical,High,Medium and Low (provided SLA time based on status)

Critical = 2 hours
High= 4 hours
Medium= 7 working days
Low=14 working days

if columns can give a staus met SLA, or breached SLA or Breaching SLA by this day/time?
0
I had this question after viewing conditional copy and paste.
plz look into the Actual file
this code is very slow  plz look into it
Actual-File.xlsb
0
Nothing to do with yellow highlighted colour data, highlighted colour is only for understanding purpose in actual file there is no highlighted colour
what I want is to copy the data from B to A
see the sheet2, I have putted the result to sheet2
copy the data from sheet1  as per given condition and paste to sheet2

i have to do all this by vba only
Sample-file.xlsm
0
I have data in yellow highlighted colour area, nothing to do with it
Now what I want is look into G coloumn
Put B to below A that's it
I have to do all this by vba
see sheet2, I have putted the result to sheet2
but I want the result in the same sheet not in sheet2
highlighted colour is only for understanding purpose in actual file there is no highlighted colour
Sample-File.xlsm
0
0
Dear Experts,

I'm looking for an excel macro to auto fill cells in Column B based on selected description from cells in column A. I tried using Vlookup but this is not completely successful.

Example:

Cell A1 - Hollywood/entries/Jan 2018 - result in column B1 should be - Hollywood
Cell A2 - Hwood/entries/Dec 2017 - result in column B2 should be - Hollywood
Cell A3 - Sales/Jan - Result in Column B3 should be - January

in above example, the source is not definite and specific information is sometimes expanded sometimes abbreviated, and this is just example, similar to Hollywood and Jan, the list is exhaustive.

Any help to crack this is highly appreciated.
0
Help needed to find wrong format dates in a column,
I have a column with 10K+ dates
My format is
DD-MM-YYYY
If its not this format need a way to know it
My formats are as
22-1-2018
22-01-20188
22-012018
22-01--2018
22-01.2018

So on, any formula that can do the finding
0
Grab text from a word file with Excel VBA
=======================================
My requirement is to grab two texts between two flags placed in word file and write in to excel - 2 columns
Flags can be [mydate] and [/mydate]
and [mytext] and [/mytext]

e.g. [mydate] 2018-07-14 [/mydate] blah blah [mytext] this is my text [/mytext]

It should be repeated until all occurrence in the file.
Sub GrabUsage()
Dim FName As String, FD As FileDialog
Dim WApp As Object, WDoc As Object, WDR As Object
Dim ExR As Range

    Set ExR = Selection ' current location in Excel Sheet

    'let's select the WORD doc
    Set FD = Application.FileDialog(msoFileDialogOpen)
    FD.Show
    If FD.SelectedItems.Count <> 0 Then
        FName = FD.SelectedItems(1)
    Else
        Exit Sub
    End If

    ' open Word application and load doc
    Set WApp = CreateObject("Word.Application")
    ' WApp.Visible = True
    Set WDoc = WApp.Documents.Open(FName)

    ' go home and search
    WApp.Selection.HomeKey Unit:=6
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "[EventDayMth]"
    

    ' grab and put into excel
    Set WDR = WApp.Selection
    ExR(1, 1) = WDR ' place at Excel cursor

   'repeat
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "[EventDayMth]"
  
    WDoc.Close
    WApp.Quit

End Sub

Open in new window

0
Is there any way to convert this XML document to Excel.  The trick is that the formulas do not transfer so is there a way someone is able to do it or show me how.

Much appreciated for whatever you can provide as this seems like a difficult task.
XML-doc.xml
0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

I had this question after viewing combine sheet1,sheet2,sheet3,sheet4,sheet5 into 1sheet.
This is my actual file but it is bigger than this i have deleted 75% of data bcoz i can't upload the file  above 50mb  here
the current code is unable to handle this much of data and it is very slow so plz look into it and do needful
plz see the actual file in the attachment
Actual-File.xlsb
0
I used to use a software called QueryCell.  It allowed you to easily take columns from excel and easily create update and insert statements.  Unfortunately this doesn't work with current versions of excel.  Can anyone tell me if they know of something similar?

Thanks


 Screenshot of QueryCell
0
I have created a userform and for question 2 I need help writing a subroutine to:
•      Create an array and initialise it
•      Store the count of “Poor”, “Average”, “Good” and “Excellent” in the array
•      Plot a graph to show the number of count for each rating.

Attached is an example file that I have to work on. Any help is much appreciated! :)
Logsheet-8.xlsm
0
I have a user who is unable to get Excel (O365/2016) to recognize default sheet options she has saved.
Specifically, she wants gridlines set to print, and heading set to not print.  
She has to change this setting with every worksheet she opens or creates new.  
I have changed these settings, and others, and saved as a template in the standard startup Excel folder  (c:\users\patty\appdata\roaming\microsoft\excel\xlstart\book.xltx)
Other changes to the startup worksheet are saved, but this setting always reverts back to the opposite of how she wants it.
Sheet OptionsThis is how each sheet opens.  Even if I change the two settings so that gridlines print and headings are set to not print, and save as the new template, new sheets open like this.
Any advice on how to get this to behave as desired?
0
On the sample file attached I have data arranged in columns A thru E on Sheet1.  The column E with field name "Deletion Check" is used to check the status of each row for deletion, if it says "Delete" then that row should be deleted in its entirety. I tried three different VBA procedures and those can be seen in modules to achieve the deletion of all those rows that meet the criteria but all procedures raised error.

On Sheet2 I placed the original sample data so after testing the code data set can be reverted back to its original state again.  One quick note here is that initially I was using the procedure "deleteRowswithSelectedText() " without any issues, the way I was using this code from the button was that I was highlighting the column E then running it and it was working pretty good up until this morning but for reasons that are beyond me it stopped working and after that any other code trial generated error even after I tried on brand new files.  

So if any expert can offer a working code either by modifying one of the existing code blocks or adding entirely new code would be much appreciated.
EE_ROWDELETE.xlsm
0
Wanted to calculate Column J based on what's in Column H.  Also Column K based on what's in column I.  I've added some calculations manually to illustrate what it should look like in yellow.

See Attachment.
C--Users-lfreund-Desktop-COUNTING-B.xlsx
0

Microsoft Excel

134K

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.