Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Microsoft Office

60K

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 All,
I am in need of your assistance with Excel.

I have an Excel spreadsheet with two worksheets, Shee1 and Sheet2

In Sheet1, I have the following columns
System
Table
Field
Field Mapped
Table Mapped

In Sheet2, I have the following columns
System
Table
Field

The differences between the two sheets is Field Mapped and Table Mapped in Sheet1

How could I populate Sheet 1.Table Mapped with text Y where
Sheet1.Table
match
Sheet2.Table  

Could this be done with a formula please? I am afraid I do not have access to VBA or to run macros due company policy.

This question is very similar to the question below
https://www.experts-exchange.com/questions/29057789/Columns-matches-from-two-seperate-worksheets.html

Many Thanks.
0
Office 365 Training for Admins - 7 Day Trial
LVL 2
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Excel Find Distinct Values within Column and sum another column per distinct value
0
Count the number each item appears in a selected column and produce those values in a new tab.

Column A of New Tab = Original Value (text or number)
Column B of New Tab = Number of Times Value Appeared

Assistance is greatly appreciated.  A sheet might contain as many as 15,000 rows.
0
Not sure the best way to get what I need.

I would like total of hours for dates in the past in column D

I would like total of hours for dates in the future (including current day) in column E

See attachment
Planning.xlsx
0
I would like to have the following formula show the totals if the date is in the past

=SUM(APJ6:APU6)     /    total for dates in the past

Also show the total it the date is in the future.

=SUM(APJ6:APU6)   /   total for current day and future dates
0
Hi All,

I am in need of your assistance with Excel.

I have an Excel spreadsheet with two worksheets, Shee1 and Sheet2

In Sheet1, I have the following columns
System
Table
Field
Mapped?

In Sheet2, I have the following columns
System
Table
Field

The same columns in both sheets except for the mapped column in sheet1.

How could I populate Sheet 1.Mapped with text Y where
Sheet1.Table  and Sheet1.Field
match
Sheet2.Table and  Sheet2.Field  

Could this be done with a formula please? I am afraid I do not have access to VBA or to run macros due company policy.


Many Thanks.
0
Afternoon,
I am setting up a Hybrid Office 365 setup... and I have gotten 75% there.
Mail flow is working in the following way.
O365 <-> External - TICK
On-Prem -> O365 - TICK
O365 -> On-Prem - FAIL
The mail delays with the error...
Reason: [{LED=450 4.4.317 Cannot connect to remote server [Message=451 5.7.3 STARTTLS is required to send mail]
Here is the next part... I feel it is because I have Mail Marshal inspecting all incoming mail to on-prem. Does anyone know what I can do to let mail through or inspect it and let it through?
Thanks.
1
My VBA editor menus are grayed out.  The menus don't respond to mouse clicks or Alt key combinations.  I have tried uninstalling and reinstalling MS Office twice and no change.
0
column D has phone numbers in the following format

              D
123.456.6789

I would like to have the phone numbers moved to column K with the following format

            K
(123) 456-6789
0
Is there a way to offset from a dynamic named range to use the same number of rows as the dynamic named range does?
Dynamic.xlsx
0
What does it mean to be "Always On"?
LVL 4
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.

I had this question after viewing Excel 2010 disable a cell based on another cell.

I am using this to so that if one cell is populated then another is not updatable. Is it possible to have another change option in a worksheet?

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
'The code will be triggered if the cell being changed is in column D, starting from Row10
If Target.Column = 4 And Target.Row > 9 Then

    ActiveSheet.Unprotect Password:=PW
    If Target <> "" Then
        Cells(Target.Row, "E").Locked = True
    Else
        Cells(Target.Row, "E").Locked = False
    End If
    ActiveSheet.Protect Password:=PW
End If
End Sub
0
I have an excel spreadsheet that if something is selected in a dropdown in cell D10 then E11 will be disabled
0
I am sure this is a simple fix, but I cannot figure out how to get the scrolling window in the attached worksheet titled - Dashboard -  of the attached workbook to stop when it reaches the bottom of the referenced range.  Please see the attached screenshot also.

Thank you for your assistance!
Screenshot.docx
NCCU---Contracts-Inventory-Listing-.xlsm
0
I am working on a project which is Excel based but part of which links to another internal system, a Purchase Order creation platform.

I want to be able to open the PO Platform from the Excel sheet, ideally without going to the Intranet home page first.

Our Intranet home page has a button which links to the PO Platform and I can copy the link from it.

When I use that as a hyperlink in Excel it does not behave the same as when clicking it direct from the Intranet.

Using the link on the Intranet just takes you to the PO platform with single sign on authentication whereas using the hyperlink in Excel shows an error saying user hasn't logged off and then goes to the login window for the platform.  I have spoken to our IT department and apparently it is to do with multiple logins for the PO platform being disabled and we do not want to re-enable it; they have tried in the past and it caused other issues.

So, I am thinking I can maybe set a macro that runs when the hyperlink is clicked. The macro would navigate to the Intranet home screen and "click" the button. The button is stored within a table on the home screen and has a specific jpg as a symbol.

Can this be done?

Thanks
Rob Henson
0
eThe below code is meant to split row data that contains a special character "/" within the cell data for example "SEL/EHL" and populate single value into the row below by having row only for "SEL" and a duplicated row with the cell now with the "EHL" value and to remove the "/" from the metadata that was split.

However the code below for "&" works fine but the above fails to run and generates a run time 9 error message "Subscript out of range" message.

Can any of my peers advise why its happening on this "/" piece of code and not the "&" part of the code even though they are meant to be doing the same thing.

Option Explicit

Sub clean_pos_data()
    Dim rawData() As Variant
    Dim startRange As Range
    Dim v As Long
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim cleanData() As Variant
    Dim splitField() As String
    
    With ws_PosSeq
        Set startRange = .Range("a3")
        rawData = startRange.Resize(.UsedRange.Rows.count - startRange.Row, .UsedRange.Columns.count).Value
    End With
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x
        Else
            If InStr(1, rawData(i, 9), "&") > 0 Then
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    v = v + 1
             

Open in new window

0
Hi!

I have created 7 templates that I would like to write a Macro for that opens them all in succession for me to manually insert attachments and send.  After trawling a few forums I have cobbled this together however I am now struggling to see what I am missing in order to make this work once assigned to a toolbar button:

Dim template As String

Sub OpenTemplate1()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template1.oft"
MakeItem
End Sub

Sub OpenTemplate2()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template2.oft"
MakeItem
End Sub

Sub OpenTemplate3()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template3.oft"
MakeItem
End Sub

Sub OpenTemplate4()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template4.oft"
MakeItem
End Sub

Sub OpenTemplate5()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template5.oft"
MakeItem
End Sub

Sub OpenTemplate6()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template6.oft"
MakeItem
End Sub

Sub OpenTemplate7()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template7.oft"
MakeItem
End Sub

Private Sub MakeItem()
Set newItem = Application.CreateItemFromTemplate(template)
newItem.Display
Set newItem = Nothing
End Sub
0
Dear Respectable Experts,

i need help below code attached function Extract a number from a string value and returns numbers as string i need that it returns number as value or number please help me if it is possible.

Thanks.

Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = Result
 
End Function

Open in new window

0
Hi Experts,

my KMS host is WIN2012R2 server.
VAMT is installed.
WIN2016 KMS key is installed but after some days the office installations are not licensed.
Office 2010 KMS key is installed but after some days the office installations are not licensed.

How can I troubleshoot this ?
0
Hi Experts,

If you will see under attached file, some data seems indented, but I have not used the indenting. Why is that?
Goals-Template.xlsm
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

IT just installed Microsoft Word 2016 on my computer, so now I have both Word 2013 and Word 2016.  When I double click on a DOCX from within Windows Explorer it launches 2016 even though I:

Used Control Panel->Default Programs->Associate a file type or protocol with a program:

I associated both DOC and DOCX files with "C:\Program Files (x86)\Microsoft Office\Office15\WINWORD.EXE" which is Word 2013.

Any ideas on how to set a previous version of Word as the default?

Thanks in advance,
0
I want to use VBA to populate the cells in a single column with the following formula.

=IF(ISNUMBER(SEARCH("501020",A2)),"x","Y")

where A2 is the starting location of the text that I am searching for "501020", and C2 is the starting location of the result (x or Y).  Each subsequent row would have different text in column A and the appropriate result in column C.  The number of rows in the worksheet will vary ranging up to 500 rows.

I want to duplicate the same results that I get if I copied the above formula in C2, and pasted it down to the last row containing data in column A.
0
Any idea how to loop through pivot tables applying conditional formatting instead of doing it manually as below.

     ActiveSheet.PivotTables("Test1").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
      
         ActiveSheet.PivotTables("Test2").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With 

Open in new window

0
Hi,

I have a dashboard created but wish to link some of the information to the data behind it, for example, showing on the front sheet  I wish to look at points 6-11
1) Overdue tickets - done
2) Open Tickets - done
3) Problem tickets - done
4) Completed tickets - done
5) Unassigned tickets - done
6) The person who has been assigned the tasks most often - support on this
7) Which user has reported the most tickets - support on this
8) How to check your ticket (based on the data in the IT Action List) - is there an easy was of a user clicking on a button which then displays the tickets they have reported
9) Top 5 categories reported (number of instances) - chart
10) Top 5 subcategories (number of instances) - chart
11) Open tickets by category - pie chart


Kind regards, Stewart
IT-Log.xlsx
0
All,

I need a formula to make a chart dynamic. See attached. All I used is the grand total (column K) and Tech Names (column A).

Capture.JPG
0
We are thinking of putting our Microsoft office apps in the cloud but keep our exchange Server in house.
Has anyone rolled this out and working this way.?
Advantages or pitfalls you personally have experienced.
Thanks
1

Microsoft Office

60K

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.