Microsoft Office Help

Hi,

How can I set conditional formatting in excel to highlight a cell based on the value max/min of within it's own range? (I have included an excel that does this but I had to include additional formulas outside the conditional formatting in order to get the effect I want. I'm trying to get the same effect but only using conditional formatting.
ConditionalFormattingMAXMIN.xlsx

2 Comments

Why Diversity in Tech Matters

LVL 13

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Is there a way to PREVENT users from downloading and installing the Microsoft Teams app on Windows 10?

1 Comment

I have a date in cell A1.
It's the hire date of one of my employees: 9/12/2014.
Her paid vacation days -- if not used -- always expire 364 days later.
Today is 9/17/19.
How do I write a formula (not an array) to return the expiration date -- and for every future year as the year changes -- in my spreadsheet?
For example, in today's case the result cell A2 should return the date 09/11/2020.
In a few months on January 1, 2020, I would still want the expiration date to remain 09/11/2020.
On 9/12/2020, I would want the expiration date to change and then be 09/11/2021.

Hope this makes sense, and thanks in advance!

7 Comments

Hello experts,
I was wondering how to manage the following issue with excel tables:
I select row 2 and send Ctrl + to insert a new row above and I get format of header instead of having format of row 2.

If you have advice on this please let me know.
I attached dummy file.
Thank you for your help.
Table_20190917_225900.xlsx

5 Comments

Folks,
I need a way to designate that when a workbook opens cell G27 starts blinking. I've looked around and found some examples but I couldn't get anything to work.

15 Comments

I have a daily workbook that starts out with five blank worksheets with the same 7 cells designated for data to be entered in each worksheet. As a sample is tested and data is entered in the 7 designated cells, the worksheet is renamed with the lot number of the sample.

I would like to add a summary worksheet that will contain the data from the five worksheets, and list the 7 values from each worksheet on a single row.

In the summary worksheet, is there a way to address the different worksheets even though the worksheet name has been changed from Sheet1 to LotNo123, Sheet2 to LotNo456, etc.? The lot number will always be something different. Also, on some days, there may only be 4 samples, and Sheet5 would not be renamed.

7 Comments

LVL 1

Question by

SAM2009

2d agoSolvedPrivate

How to create time line with Excel?

Hi,

How to create a time line from Excel? I just want something simple like:

Contains:

Date in the horizontal line.
Should show event: from x date to x date with different color

Thanks

3 Comments

Excel: I have random dates in column A, with a format of 3/15/19
In column B, I would like the date converted to the day of the week. (I can do this part with formatting)
*Issue: There are rows where there is nothing in column A, so I need B to be blank if A is blank.

Example:

Column A Column B
3/15/19 Friday
blank blank
6/5/19 Wednesday

3 Comments

LVL 12

Question by

HuaMin Chen

3d agoSolved

Error in Excel file

Hi,
I don't know why there is "Type mismatch" error when I run 1st Macro (pressing Alt-F8), vs attached Excel file.
ZCOM-QRL-112N.xlsm

2 Comments

I am running Office 365 on my Windows 10 laptop, and work from an Outlook.PST file on my Desktop. I noticed there were a lot of "Deleted Items", so I right clicked on the Deleted Items folder and hit "Empty Folder". Somehow, for some reason, my Calendar was emptied ... and I've now lost all my Calendar items. Is there any way to recover my Calendar data?

Thanks,

Phil

5 Comments

Hello experts,
I have the following AutoHotkey which allows me to send Alt + Y1 to directly select my Add-in tab on Excel .
I don't understand why it works one computer equipped by Win 10 Excel 2019 and it doesn't on my other computer equipped by Win 10 Excel 2016.

!y::
SetKeyDelay , 10 ; time to wait between send
WinGetActiveTitle, Title
IfInString, Title, Excel
{
	Send, !Y
 Send, 1
}
Return

Open in new window

Any recommendation or advice?
Thank you for your help.

19 Comments

One of my associates has been uploading files once a week via FTP for the past 5 years from his home office to one of my websites. There's never been a problem of any kind.

Today, as he went through the usual process, an error notice popped up. He was able to ascertain that the FTP program he uses, Filezilla, was denying access because of the username and password he was using.

He's been using the same one for a long time, and it does contain an assortment of letters, numbers, and special characters.

I'm guessing that there may be a variety of possible reasons for this problem, but I'd like to hear from the pros as to what steps I should take to address this.

Microsoft Office

6 Comments

How do you write a Worksheet Selection Change to Hide rows before a start time and hide rows after the end time.
My schedule starts at row 5 Column D. It contains entries in 15 minute increments starting at 12:00am. and extends 11:45pm, row 101.
In B7 and B8 I have Start time Row: and End time Row, calculations. So I know (form example) that if the start time is 6:00am then that is row 30 and If stop time is 19:00, then that is row 81.
So what I would like to do is have a change event, that will (in the example above), Hide rows 29 through Row6, and Hide Rows 82 through Row 101.

This is variable because AB5 (work Start Time) and AC5 (work end time) can change, but the start time Row Count and End time Row count B7 and B8 will automatically update when AB5 and AC5 is changed. (I have already done that).

I hope this makes sense...
Thanks,
Norm

21 Comments

Hello experts,
I have the following procedure reported at:
https://www.experts-exchange.com/questions/29156896/

Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row

Open in new window

…

5 Comments

I am trying to convert a csv file into a xls through a powershell script so that it will be viewable on sharepoint 365, without users having to convert the file, or open it in a thick client.

Any help is greatly apprenticed.

thanks

I am attaching a copy of one of the csv files i am working with.
SCORECARD-G3-MATH-RS.csv

3 Comments

Hello the experts :)

I'm working on a travel planning which is basically used by employees to fulfill the day's location then calculates different things like how much time they spent in each company's / client / supplier location and so on.

I'm tempted to merge & center some of those locations for easier reading.

May I expect some "serious" issues with formulas & vba or can I do that without worrying too much ?

Thank you very much & kind regards,

Magpie

3 Comments

I have a excel spreadsheet with two tabs, one tab (Bar Database) has a list of Locations and ID numbers. A:A is where the ID numbers sit and B:B is where the name of the location sits. The second tab comes from an online state database OData feed, the feed generates 100k+ results. Well in one of the columns is the same ID numbers from the list in Bar Database A:A. If it can filter just what is in the list it would only be 2900 rows (currently, next month it would be 2922, with an additional 22 results that I am pulling each month. )

I currently have the Power Query filtering the results down by manually searching and checking the box of the ID numbers I want, but a new location might pop up or one close and I figured it would just be easier to change the list on the spreadsheet then to edit the power query. Especially if someone from upper management uses it and just knows how to open excel.

3 Comments

I'm trying to assist a group that has an old Access application that will no longer run since "upgrading" to Office 365. The offending code lines are:

Private Declare Function GetLocaleInfo Lib "KERNEL32" _
Alias "GetLocaleInfoA" _
(ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long

Private Declare Function GetThreadLocale Lib "KERNEL32" () As Long
Private Declare Function GetSystemDefaultLCID Lib "KERNEL32" () As Long
Private Declare Function GetUserDefaultLCID Lib "KERNEL32" () As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" (ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long

I assume that the migration to the 64 Bit environment has caused problems with "KERNAL32" - and possibly "user32" as well. This is not code that I would use or that I understand, so I am hoping that simply changing the code or adding a missing object library will do the trick.

Any assistance appreciated.

2 Comments

Hi Experts,

What is the VBA Equivalent of the following Formula in Excel

=RIGHT(E2,FIND("_",E2,1)-1)

12 Comments

Build an E-Commerce Site with Angular 5

LVL 13

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hi!

I have this problem running for some days now. I want to get the total per month but based on week number of said month.

I used the following formula:

=SUMPRODUCT((MONTH($B$10:$B$258)=MONTH(C3))*$C$10:$C$258)

Open in new window

But when I compare to values per month's weeks, they don't match.

For example.
- the month of jan 2019 has 5 weeks where
- but the first week of jan has 31 of dic 2018 as Monday.
- and the 5th week of jan has a feb 2 and feb 2.
- another example, is july, has 5 weeks.
- the 4th week of july, has august 1 thru 4 as its week.

In the excel included notice that Division A for jan 2019 is 130, but we calculate it using the weeks that it has, the amount should be 245 (and so on to the other months).

I also tried to incorporate to the fomrula a calulation of "Mont's week number":

=WEEKNUM(G2,2)-WEEKNUM(DATE(YEAR(G2),MONTH(G2),1),2)

Open in new window

. But up to now, no success on how to combine them to calculate the total of the monsth based on the weeks that month has.

So how do I include these factors in my calculation?
SumMonthYears.xlsx

10 Comments

Hi Experts,

I'm having an issue with a macro in vba.

I have a "data" sheet in which we can find all the details related to task orders, such as ID, frequencies, categories, documentation, etc.

What I would like to have is a macro that creates a worsheet for every UNIQUE combination of Category and Procedure.

In each worksheet created, we would have all the related columns to that specific combination and that would act as a form to verify that everything has been done.

Is there any solution to this?

Thank you.
Formulaires_test2.xlsx

5 Comments

Two separate businesses using the same domain name have now merged into one.
This is the first time I've ran into this and hope someone could shed some light. We've recently acquired a new client who at one point had two domain controllers. Server 2008 and Server 2012. They moved Server 2012 over to a new location as part of a different business, but kept the same domain name. Server 2008 AD sees the 2012 as a DC, However 2012 doesn't see 2008 as a DC. They are now on different networks, but recently was configured to tunnel back to corporate to share resources.

What I'm trying to accomplish: Join a 2016 DC to their corporate to decommission 2008.

Error I'm getting when promoting 2016 to a DC: "Active Directory preparation failed. The schema master did not complete a replication cycle after the last reboot."

What I've gathered so far.

Server 2008 - DC - samedomain.local - Corporate Office

At one point was replicating to 2012.
Server 2012 - DC - samedomain.local - Remote Office

No longer replicating from 2008.
Recently a WatchGuard VPN was put in so the two locations could talk and share resources. Different IP schemes, and they don't know about each other.

My Question: Can I safely remove 2012 DC from 2008 to stop attemping replication and at the same time continue to operate both under the same domain names, but seperate?

Remote Office will still use 2012 to authenticate locally until we can sit down and plan out a migration plan several …

9 Comments

I need VBA macro assistance. I have an EXCEL workbook that i will be completing evaluations. Each evaluation will be its own worksheet. I want to have a user-form prompt that opens up when i open the workbook asking me how evaluations i want to complete. (please the example in the attached file). Once i click on the desired # of the evaluations, the worksheets should populate.

Example: If i select 4 from the dropdown list on that user-form, I want the EXCEL workbook to then insert 4 new tabs with the evaluation form in each of the tabs. Please see attached file.

SIDENOTE: The worksheet should have the same evaluation template (please see the attached file).
evaluation_example.xlsx

10 Comments

Hi Experts,

Is it possible to protect a DataTable without hampering its working ?

12 Comments

Hi Guys,

We are having an issue with two mailboxes in Office 365.
These are brand new mailboxes - not migrated and not linked to onsite AD.

We setup the mailboxes successfully on the mobile phones, but setting the profiles up in Outlook failed.
Logging into the mailboxes online, resulted in an error:
Something went wrong, please try again again ... err: Microsoft.Exchange.Data.Directory,ForestUnavailableException

After a password reset on one of the mailboxes, it asked for the time zone (logging on online)
After the time zone select we are getting "401- Unauthorized: Access is denied due to invalid credentials"

We spoke to Microsoft, but they are not of much help up to this point.

Any ideas will be appreciated,
There are unfortunately already new mail in these mailboxes, and deleting / recreating these mailboxes are not an option

2 Comments

Microsoft Office

Related Topics

Microsoft Office

Related Topics