Microsoft Office

62K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

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

Sign up to Post

Hi,
I would like a correction to attached formula and roundup to nearest 5
Please refer to sheet
many thanks
Ian
ROUNDUP.xlsx
0
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Hi guys, i got outlook 2010.
everything works fine. but when i search something in outlook, it always starts from old items and i need to wait till it gives me the latest results.
For eg: if i search, it brings up that keyword from 2010 first, then 2017 . it wastes a lot of time for me, as i search every time it searches from old. and i cannot say its searching from very bottom, because i got emails from 2006 as well. so it just randomly searches from middle, then brings the latest .
i did search indexing rebuild, did not help.
anyone else have any idea with this issue ?
thank you
0
how can i create and maintain internal email server free ..please suggest me any free downloads ... we have the office of 200 seater ..so i need to setup internal email server in local machines communication .. suggest me
0
Hello there,

I have 35,000 locations. I want a formula that will find the closest 3 points from the same list. There was a prior question and solution here, but I cannot get it to work:

https://www.experts-exchange.com/questions/28561261/Find-10-closest-x-y-coordinates-between-two-huge-x-y-data-set.html

Here's my data:

Unique identifiers:
$A$2:$A$35651

Latitude:
$J$2:$J$35651

Longitude:
$K$2:$K$35651

Based on the question above I modified the formula that looked like a solution:

=SUM(IF(SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)=SMALL(IF($J$2:$J$35651="","",SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)),2),$N$2:$N$35651,0))

This returns a #NUM! error.

Nearby locations are in LM, LN, and LO2. I doubt we'd need to reference those, but some people used them in the event of a tie. Breaking a tie is a nice feature, but ties in my dataset will be so rare that it's more of a "nice to have" if you see an easy solution.

It looks like they're mostly using the SMALL(array,k) to get the second-closest value, which I understand. I can easily change the K and lookup range, or you can use RIGHT(LM1,1)+1 to get 2 since the column is called NearbyPlace1, NearbyPlace2, etc. And I assume we'll start with 2 because 1 would just re-find the current location.
0
What kind of case is this?

I was at a clients office cleaning out his desktops and found a desktop that pulls the power supply out like a server. It’s made by dell, but can you get cases like this that aren’t marked Dell?

I would love to be able to replace power supplies like this in my builds.15E9959F-A9E3-4BD8-B076-4DD75D271E1.jpeg
0
i have vba code for sending emails---and so i already have one book with the form and code that prompts me to enter how many seconds i want to delay between sending emails.

i have a separate vba code for sending emails that is missing this form and prompt. please see book called has timer and port the code to add a timer to the book called missing timer.

thanks!
has-timer.xlsm
0
I am trying to make a report to capture "total days spent in a month" by team members on a project. I tried using formulas ( Sum if , Count if, also Tried PIVOT )  but I am not getting the desired result. I was hoping if experts can guide me an excel formula or if a VBA code will produce the report.

I am attaching the Excel sheet and more detailed requirement is mentioned on summary page of the excel sheet , any insight would be appreciated
Experts-assistance.xlsx
0
Hello,

I have content as mentioned below in a cell in EXCEL sheet. I wanted to split the content based on keywords "NAME", "EMPLOYER" and "DESIGNATION" into three different columns.

NAME: LOUIS GOERGE EMPLOYER: WALMART DESGINATION: MARKETING MANAGER.

Please let me know if this can be done using VB script or macro and help me with logic.

Thanks.
0
I need to replace commas "," with newlines in Excel.  There could be 1000 of these (in specific columns).

I can't find the mechanism for doing this via the Find & Replace.  I've seen things like Ctrl-Shift-j (but that doesn't work) as the newline.
0
I am trying to find a formula that returns the earliest date from a table called DC_Table that has 11 non-contiguous 'Date' columns, excluding blank cells or cells = 0.

All cells in the DC_Table are linked cells, and for the purposes of providing an spreadsheet example I have added the source spreadsheet to the workbook. It's called DC_WOs_Extracted.

There are several non-continuous columns in DC_Table that are date fields [Date1], [Date2], etc., and the alternating columns are 'Minutes' fields [Min1], [Min2], etc.

As shown on the worksheet tab called  'Dates', the following formula works but seems cumbersome. It references the first 3 [Date] columns (ultimately I will need to include columns Date4 through to Date11), and returns the earliest date found:

=IF(MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]])<>0,MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]]),"")

I am hoping for a formula that is simpler (ie., using the range as opposed to having to list each date column as above), as some of my spreadsheets will have up to 25 [Date] columns. Is that possible?

Thanks,
Andrea
Metrics_DCWOs_01FEB2018_EE.xlsx
0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi,
I have customized office installation file and saved it under updates folder, we are upgrading office from 2010 to office 2016.
When i run the windows installer file from the updates folder, i get the error which i have attached.
I have run .net framework package, it didnt made any difference.

Any ideas whats required?

Thanks.
upgrade.JPG
0
I am trying to calculate the total number of times an item is used when it is on the same invoice with 2 criteria.  First, the order numbers (column A) have to match and 2) the item number R1606 also needs to be on the same order.  I have attached an example using item numbers R1606 and 24410.  Any help would be appreciated.
SUMIFS_EE.xlsx
0
So I've viewed many similar cases, but still cant seem to make it work.
I have a simple on/off choice from a drop down menu. Cell "Q19".
When either on or off is selected I want to either show or hide columns.
The following script works fine when assigned to a control button, however I want this to be automatic once the selection has been made from the drop down.
Any ideas?


'To Hide and Show Columns for Custom Rubber Properties
Sub Hide()
        'Hide when Q19="OFF"
        If Worksheets("Quick Start").Range("Q19") = "OFF" Then
            Worksheets("Quick Start").Range("S:AF").EntireColumn.Hidden = True
        End If
        'Unhide when Q19="ON"
        If Worksheets("Quick Start").Range("Q19") = "ON" Then
            Worksheets("Quick Start").Range("S:AF").EntireColumn.Hidden = False
        End If

End Sub
0
I am using Office 365 and I sent a meeting invite to a Comcast email userr (******@comcast.net).  They received the meeting invite but when they hit accept they received the following message: “delegate” would not receive my email.”
0
Hi,

In the attached spreadsheet i need some match index formula in tab "Pnl" which is looking up tab "Data_Tab"

I need the totals for each weekending (i have manually entered some for guidance)

In row 11 i have put the names of the columns the totals need to match against

Can someone provide the formula so these numbers will fall out in the pnl tab

Thanks!
Seamus
HOWL-REPORT.xlsx
0
I have Excel 2010.  The following vbscript file is executed by a scheduled task.  It opens a file to update it, save and close.  The file has the "read-only recommended" option on and my script must answer this prompt with "no" which is not the default.
Option Explicit
Dim objXL
Set objXL = WScript.CreateObject("Excel.Application")
With objXL
	.DisplayAlerts = False	
	.WorkBooks.Open FileName:="S:\Schedule\EngDates.xlsm", ReadOnlyRecommended:=False, IgnoreReadOnlyRecommended:=True
	.DisplayAlerts = TRUE
	.Visible = TRUE ' Optional
	.Run "EngDates.xlsm!UpdateDash"
	.ActiveWorkbook.Close(True)
	.Quit
End With
Set objXL = Nothing

Open in new window

I have tried several different syntax formats, with and without parentheses, and nothing has worked yet.
When I run this script, I get an error as seen in the attached file.  Does anyone see the problem with my syntax?  
Thanks!
The error that occurs when I run my script
0
Hi, i need the countif formula for

one if a number is >10 or it is greater than -10 eg -11

Thanks
Seamus
0
I have a query that loops through a list of db schemas and outputs the results to individual pdfs. I would like to append the results to a single excel spreadsheet instead of individual files. How do I do this?

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String



Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("UsageReport")

ssql = "Set nocount ON; select * from air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

On Error GoTo UsageReport_Err

    
    DoCmd.OutputTo acOutputReport, "UsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint


UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit
Loop
Close 1
End Function

Open in new window

0
Somehow, a poweruser has messed up their site collection.  

The farm is connected to our Office Online installation and works with other site collections.  For this poweruser, when we click on a document library New button, the only option that displays is Excel and "magically" an previously created excel document pops up in excel (without clicking the excel popup link).  

Is anyone familiar with this behavior or how to fix it?

Attempts: I created a new document library, but get the same effect.  This effect happens on subsites as well.  I create a new site collection in the same farm and it works as expected.

---

Edit: I exported the site and imported it into a clean site.  The import log shows:
[1/19/2018 1:17:12 PM] [-List] [wfpub] [wfpub] [9851366e-5c8c-415d-8250-b413ddaa6742]   [-2147024809] [System.ArgumentException] [urlOfFile
Parameter name: Specified value is not supported for the urlOfFile parameter.]

I suspect that the poweruser was using a "Forms" library as if it were a Document Library.  The poweruser had not developed his site too much so we ended up destroying his site and creating it anew.  

He's satisfied for now, but I'm still curious what could have caused a user to get themselves into so much trouble.
0
[Webinar] Database Backup and Recovery
LVL 11
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Hello:

We have an XML file that we would like to open in Excel.  We have tried two methods.  Both have failed.

First, we tried opening Excel, browsing out to the file, and simply opening the file.  But, upon trying to do so and using one of the suggested methods, we get an error saying "cannot load the specified XML".

Also, we have tried opening the XML file by opening Excel, choosing "Data", "Get Data", "From File", and "From XML", we get the error message "XML processing failed...Data at the root level is invalid".

How can we, then, open an XML file in Excel?

Thank you!

John
0
We have a shared events calendar at work that several people have Editor access to. It's in Outlook 2016. If a colleague creates an event and a user updates it and clicks to send the update to her other colleagues she gets error - "This meeting is not in the calendar folder for this account.  Responses to this meeting will not be tallied. Do you want to send anyway?"  Then clicking yes gives she gets an error "The operation failed".

I have unchecked cached exchange mode. Have created a new outlook mail profile. Neither resolves the issue. The user is able to create and send a new event booked in the calendar.
0
I have an Excel formula that looks like:

=IFERROR((MAX((((G11-$M$5)*F11)*$M$6),0)),"")

The corresponding Access form fields are:

G11 = txtMST
M5 = txtMoistureMax
F11 = txtCleanCWT
M6 = txtDisSchd1

How do I convert this to Access?
0
I would like to migrate exchange server 2007 to office 356 .

What information  is needed from exchange server?
How to proceed to the migration?
 
I am looking for any step by step tutorial for these procedure
0
Hi,

Using just 2 PST Files.  One 14Gb archive one 2Gb live file.

With Outlook 2016 one is given 5 levels of searching:
Current Folder, Subfolder, Current Mailbox, All Mailboxes, All Outlook Items

Searches executing successfully.

Can sort search results by all the expected email parameters: sort by subject, flag status, to, from, folder...any of them - no problem.  Even hundreds of mails skitter into order instantly upon selection.

But, if I choose sort by date, the output hangs, and just shows "Searching..."

However, this ONLY happens when the Search Level selected is All Mailboxes, All Outlook Items.  Searches run at Current Folder, Subfolder, Current Mailbox levels can sort by date, instantly arranged upon clicking.

Image attached.

What do you think is happening?

I'm going to rebuild the Search Index and scan the PST files for errors next.

Thanks,

OT
0
My friend and I both have MS Word 2013.
When he Emails the attached Word file to me (and visa versa), it comes out fine .... however ...
When he Emails it to other people, the columns with the Year and corresponding text don't line up.
(I don't know version of word the other people have, but it could part of the explanation)

The real mystery is that when he emails the document to HIMSELF, the same corruption occurs.

Please speculate as to what may be happening.

EDIT: My attached file seems to have the same corruption!
Chronological-2018.docx
0

Microsoft Office

62K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.