Microsoft Applications

44K

Solutions

Content

38K

Contributors

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.

Excel - nested formulas problem

Doesn't Trim work within formulas?

I got a SUMIF like this
=SUMIF($A$18:$A$1006,A2,D$18:D$1006)

It looks like some of the text in the range $A$18:$A$1006 have trailing zeros! So they are not included in the SUMIF.
I tried this, but it doesn't like it
=SUMIF(Trim($A$18:$A$1006),A2,D$18:D$1006)

Why???? Isn't that just a nested formula?

Thanks

1 Comment

Experts:

In the attached XLS, I am using a command button to add trend lines to the graph.

I also have a 2nd command button ("remove trend lines"). I tried to replace the ".Add" with ".Delete" for all three series; however, that syntax throws an error message.

My questions:
1. What is the correct syntax that will allow me to delete the trend lines from the graph.
2. Also, I would like to *combine" the two functions into one... maybe through an IF statement indicating the following:

Pseudo:
If command button caption = "Add" then
add trend lines
Else
remove trend lines
End if

Open in new window

How can the above be accomplished?

Thank you,
EEH
Command-Button-to-Add-Trendline-v01.xlsm

7 Comments

What are some Windows 10 utilities that will scan a specified folder path and provide a list of duplicate files?

2 Comments

Question by

SteveL13

1d agoSolved

Month abbreviation in a form field

I'm trying to get the month abbreviation in a field on a form. For example, if in a date field on the form I enter 6/1/2018 in the 1st field I want the 2nd field to indicate "Jun", ( no quote marks)

9 Comments

How to manage error handlers in importing mails from outlook to excel through VBA?

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
i = 1
For Each OutlookMail In Folder.Items
If OutlookMail.ReceivedTime = Range("From_date").Value And OutlookMail.Subject = (" updates") Then
' Range("eMsail_subject").Offset(i, 0).Value = OutlookMail.Subject
Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
' Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body

i = i + 1
End If

Next OutlookMail
msgbox "operation done!!."

If i = 0 Then
' OutlookMail.ReceivedTime <> Range("From_date").Value Then
msgbox "No Login/Logout found for the given date"
End If

But it is not working properly .

Here i need if mails are available for mentioned date it has to show success message and if no mails are available it has to show message as no mails.

Please help!

Thanks in advance..

25 Comments

Question by

nav2567

4d agoSolvedPrivate

Merge columns conditionally.

Hello,

In the attachment, I would like to combine column A and column D in column E but keep the entry in column D if field in column D is not blank.

Please advise how to do that.

Many thanks.
mytestdocument.xlsx

2 Comments

I'm trying to configure a SharePoint Online document library so that it includes an Enterprise Keyword column that is required. The column is added to the library, not to a specific content type, and there are no custom content types defined. Each time I try to make the column data 'Required' in column settings, the toggle changes back to 'No.'

2 Comments

I have a Exchange 2016 DAG across 2 sites, soon to be 3. The 3rd site will be in the US and have a Exchange DB created on it to serve local users. There will be a copy of that database on 2 of my current exchange servers here for redundancy.

My question is, if I activate the US DB copy on one of the Exchange servers here, shut down that host and have it offline for 2 days while in transit will I have an issue when it is up again in the US? Will the database simply copy the changes it missed and eventually become available to be activated? Is there anything special I need to do like suspend the now copy?

Thanks!

5 Comments

LVL 12

Question by

HuaMin Chen

5d agoSolved

Install POP3 client or enable IMAP

Hi,
Further to this
https://www.experts-exchange.com/questions/29113309/Problem-to-set-up-Email-account.html

can I have more details to install a POP3 client or enable IMAP?

50 Comments

LVL 2

Question by

CipherIS

6d ago

SSIS Failed to start project (Microsoft Visual Studio)

Creating an SSIS app with VS 2015. Finally have the pieces connected. When I run it I receive the below error.

===================================

Failed to start project (Microsoft Visual Studio)

===================================

Error starting debugging. (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
Program Location:

   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.LaunchVsDebugger(IVsDebugger iVsDebugger, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

===================================

Unable to

Open in new window

…

4 Comments

Question by

doctorbill

7d ago

Excel shared workbook

Shared excel workbooks:
We have an excel (2013) shared workbook which is updated by one user
When another user access the workbook the data entered by the first user can be wiped out when the workbook is re saved
The workbook is on a network share
Any ideas please

2 Comments

We are transitioning to Skype for Business from an older product called Spark. With Spark it is fairly easy for each user to populate their own
groups with contacts that are saved and used to IM thru skype. How can we populate groups without adding them one user at a time. We have some fairly large
groups

2 Comments

Question by

ExpExchHelp

2018-08-08Solved

Dynamic graph in Excel

Experts:

I need some assistance with creating a dynamic graph (histogram chart in Excel). Please find attached XLS with includes a) example data b) dynamic example graph based on example data, and c) the actual data for which I need to replicate the concept of "being dynamic".

The attached XLS includes all relevant information, so I won't repeat the whole process here... "picture is worth a thousand words".

I thank you for your help in advance.

VR,
EEH
Dynamic-Graph.xlsx

22 Comments

Question by

ssblue

2018-08-07SolvedPrivate

Compare 2 sheets and 2 columns

I Need to compare two sheets. Fill in data on sheet ARMS column A with data from sheet Group columns C or B
Look at Group!E:E and compare with ARMS!C:C
If there is a match, then return what is in Group!C - if there is a match and nothing in column C then return what is in Group!B.
If there is a match and both columns are empty then return a '0'
If there is NO Match then return 'NO MATCH

Please see attached example
Compare2Sheets2columns.xlsx

7 Comments

I receive data in the Body of an email. I am looking for an Excel macro that looks in a designated Outlook folder, loops through the emails. extracts the data, and puts it in Excel.

The email body is structured , so it is the same every time. The content of the "fields" varies, and that is the data i want to extract.

Below is a sample email. Anything to the right of a colon is data

Fun Plant Hold ID 3366 (THIS IS the HEADER)
__________________________

Hold ID :3366
Date :06-AUG-18 06:19:38.0
Product :501179 - DS1173-11N-4MM
Machine :CRLINE07
Hold Tag Creator :And.factd.lab

Hold Category :QUALITY
Defect 1 :Wet Glass
Disposition :

Required Tests:

Number of Pallets on Hold: 2

PALLET ID LABEL CREATION TIME NET WEIGHT
---------------------------------------------------------------
125640400170 05-AUG-18 03:38:49.0 1091
125640400176 05-AUG-18 06:15:09.0 1091

5 Comments

LVL 33

Question by

phoffric

2018-08-07Solved

Merge two Excel sheets into one using a unique key

I am given an Excel Spreadsheet with a single sheet, "Original", filled with Abbreviations (mistakenly, I called it Acronym). I added more abbreviations in a "New" sheet. Now I want to merge the two sheets into a "Merged" sheet. Since my "New" abbreviations, LOL and ACK, are duplicated in the "Original", I do not want to include them in the "Merged" sheet.

Shows Original sheet, my new sheet, and the desired Merged sheet

I can do this manually as follows: Append the "New" rows to the "Original" rows, and sort by "Acronym" (C-Column). Then delete adjacent rows that have the same "Acronym" to avoid duplicates. How can I do this without this manual (and error-prone process)?

<<EDIT>> Hmm, I better have the font of the new entries in the merged sheet color-coded so that the person I deliver the document to can readily understand the additions.

The table is sorted by Term (but that is not the most essential point, since once the unsorted merge is done, I can sort the B-Column).

Since I am on a closed network, I cannot add plug-ins, and if this requires a script vs. using Excel wizards, then I'll have to type the script in.

Thanks,
Paul

10 Comments

LVL 3

Question by

Fordraiders

2018-08-06Solved

Create excel workbook with Column Headers

Access vba 2010
Excel 2010 vba

I need to revise the code below to do the following:
name sheet1 "Import_Data"

Add the following Column Headers:
"SKU" "Product Description" "Target Price" Target gp"

The format the Columns as:

Sku - Text
Product Description = Text
Target Price = Currency
Target gp = Percentage

Public Sub createExcelFile()
    Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
    Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
    Dim i As Integer, j As Integer
 
    Set XL = New Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add
    Set WKS = WB.Worksheets(1)
 
    WB.SaveAs Filename:="C:\Users\BR1\DESKTOP\Report1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook
 
    
 
    WB.Close SaveChanges:=true
    Set WB = Nothing
    XL.Quit
    Set XL = Nothing
End Sub

Open in new window

Thanks
fordraiders

8 Comments

Hi Guys, I am trying to do a Do Loop where I loop down Column E in a spreadsheet and if the number in the cell starts with "93",
it populates the cell 2 columns right of it in column G as a negative, eg: if Range "E2" = 937019784 and "G2" = 1000. It populates G2 as -1000.
If I had a Formula it would be =IF(LEFT(E2,2) = "93", G2*-1,G2).

4 Comments

Hi Guys, I am looping on column L in an Excel spreadsheet & I have a long chain of automated letters of which I only need the 7 characters
after the 24th position in the sequence of the cell as if I was using a Mid (L2, 24, 7) Formula. How do I do this? I want to strip out the data either side.

9 Comments

Introducing Cloud Class® training courses

LVL 12

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 have a sheet with multiple column and want to convert into multiple rows.

Example:-

Input

Mat Plant1 Plant2 Plant3 Plant4
123 A B C 4
456 A B

Expected ouptput

Mat Plant
123 A
123 B
123 C
123 D
456 A
456 B

Thanks

Nitin

6 Comments

I am trying, unsuccessfully, to edit the Description of a Form listed in the Database Window. When I create a new Form by copying another Form and editing the data fields, the Description of the new Form is the same as the old Form. I need to change that information in code. I can change it by right-clicking on the new Form and selecting Properties, but I would like to do it programmaticly. Thank you.

I am using the MS Office Professional 2003 SP3 version of Access. In VBA I am copying a Form named Test1 (with a Description in the Database Window of Test1) using CopyObject command. I am naming the new Form Test2. The copied Form appears in the Database Window correctly as Test 2, but with a Description of Test1. In code, I need to change the Description of Test2 to read Test2. Is this possible? I can perform this task manually by right-clicking the Test2 Form , selecting Properties and changing the Description.

2 Comments

Question by

fskilnik

2018-08-03Solved

Cell with dynamic formula

Hi there!

If the cell A1 has the number 0.2 in it, and cell A2 has the number 0.3 in it, then if I type =LN(A1/A2) in cell A3, I will get the natural logarithm of the ratio A1 to A2 , of course.

Suppose I want to have something dynamic, say the possibility of changing the digit 2 in the =LN(A1/A2) formula, to another positive integer, to be obtained using some formula.

In other words, I want to calculate =LN(A1/A someformulathatgives2)

I thought about changing the cell to that: = LN(A1/"A"&2) so that I am able to put a formula in the place of 2... it gives an error.

How this should be done, please?

Thanks,
fskilnik.

4 Comments

how do i reference the values in sheet 1 from sheet2? i know its a simple formula, i just never learned it!

6 Comments

Question by

Euro5

2018-08-02SolvedPrivate

VBA for text replacement

I need a VBA code to complete the following:
* unknown number of rows
* For column J
if column BR = "Fedex Letter" and column J = "Priority Overnight", then column J = _POL
if column BR <> "Fedex Letter" and column J = "Priority Overnight", then column J = _PO
if column BR = "Fedex Letter" and column J = "Standard Overnight", then column J = _SOL
if column BR <> "Fedex Letter" and column J = "Standard Overnight", then column J = _SO
if column BR = "Fedex Letter" and column J = "2 Day", then column J = _2DayL
if column BR <> "Fedex Letter" and column J = "2 Day", then column J = _2Day
There will be more conditions, but I can modify if I get a idea of how to format.

Thank you!

5 Comments

Question by

icdl101

2018-08-01

Add Columns to existing worksheet

Employee-Annual-Leave-Calculator-Mo.xlsmi would like to add Additional Columns to an existing Worksheet.
Since formulas are attached i cannot add additional columns without offsetting the whole worksheet.

For example at the moment Where "NAME" column is, i would like "FirstName" "LastName" and "EmployeeID" columns to be added.
Please see the attached Excel Sheet.

15 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft Applications

44K

Solutions

Content

38K

Contributors

Microsoft Applications

Related Topics

Microsoft Applications

Related Topics