We help IT Professionals succeed at work.

Microsoft Excel

137K

Solutions

251

Content

i
The collection of articles, videos, and posts on this topic.

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.

Excel - I have a column where I am using military time -example: 0645

I have it formatted as Text but I have to constantly tell it to ignore the error to get rid of the little green flag in the corner.

Is there a way to not have the error flag show up all the time?
0
9 Comments
Experts,

I need VBA code to shade cells purple.
The condition is:
If column E = "Company A" or "Company B" then shade the cells in Column  K purple

How can I do that?
The conditional formatting does not work in this file.  
I think there could be some corruption so I want to test the formatting through VBA

Thank you
0
11 Comments
Hello Experts,

I have recorded a macro and I need to tweak it. but not sure how.
What I need to do is change the selected cells to end of previous month but the code is basing it off the value of what is in the cells.
I need the code to forget about what dates are in the cells and instead change the dates in the selected cells it to end of previous month based on current dates.
example:
if the cells contain 1/1/2020 I need them to change to end of previous month based on the current date of 3/28/20 which will change the cells to a value of 2/29/20 (leap year in this case).  The code below is changing the value to 12/31/19 (end of previous month based on the 1/1/20...the date that is in the cells. )

this is the part that I need to change:
 Range(Selection, Selection.End(xlDown)).Select
   
     Dim rCell As Range

    For Each rCell In Selection
        If IsDate(rCell) And Not rCell.HasFormula Then
            rCell.Value = WorksheetFunction.EoMonth(rCell.Value, -1)
        End If
    Next rCell

Below is the entire code: 

Sub Closing_Banco_Change()
'
' Closing_Banco_Change Macro
'

'
    ActiveSheet.Range("$A$1:$AB$2211").AutoFilter Field:=7, Criteria1:= _
        "BANCO BOLIVARIANO"
    Range("B1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "11/1/2018"
    Range("D1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    
     Dim rCell As Range

    For Each rCell In

Open in new window

…
0
7 Comments
Hi Experts!

Praying & Hoping u guys dealing with this pandemic with the best of spirits! Experts-Exchange has to stay up and running!  

I just got a great help from Tom in a PowerBI question and wanted to ask if PowerBI have some sort of templates that by setting up the data accordingly I can use said template? or PowerBI is just create & execute not re-use designs?

let me know please and thank u guys!
0
13 Comments
Excel - I need to be able to automatically sort by either Month or Location.  I was thinking a button for each sort.

Also I need it accommodate adding additional columns and rows. The user will be adding new columns and new rows on a continual basis and will need to sort as new information is added.

The first sort button should sort by  MONTH > START DATE > LOCATION

The second sort button should sort by LOCATION > MONTH > START DATE
Events.xlsx
0
7 Comments
I have a spreadsheet where I have multiple rows of different data where I would like to consolidate and summarize on a single line for each ID.  It would be great to have some sort of macro that can take the data from the source tab and generate the data in the output tab (see attached spreadsheet).
ee_help.xlsx
0
12 Comments
I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
0
10 Comments
I want to populate two cells in a table as rows are added, The  first column is each row has a drop down which references all the staff records on the pr tab. e.g.if Staff number 33 is selected from the dropdown on the absence table, the name will then populate from the PR table.
Employee-PR.xlsx
0
5 Comments
Fix Win10 VBA Excel 365 loop / variable logic for conditional format.
If the row is a Total row and either (current month and current %, prior month and prior %, current ytd and current ytd%)  are higher than standard, highlight entire row
In test example line 34 should highlight, but none other

 I tried a few things with the conditional formatting variables and this compiles, but something is not working.
previous versions have highlighted every total row and the below one does not highlight any.
lastrow appears working, logic for is it is a total line  appears working.
cr (current row is probably working

Sub ConFormat()

Dim LastRow As Double
Dim myCell As Range

Dim cr As Long
' sv - startvar  sp startperc
Dim sv As Range
Dim sp As Range
' Set sv = Range("F2")
' Set sp = Range("G2")
Set sv = Sheets("Chk").Range("$F$2")
Set sp = Sheets("Chk").Range("$G$2")
cr = ActiveCell.Row
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

For Each myCell In Range("C11:C" & LastRow)
    If Left(myCell.Value, 5) = "Total" Then
'   If OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & cr)>sp), AND(ABS(O & cr)>sv,ABS(P & cr)>sp)) Then
' If (Abs(F & cr) > sv And Abs(G & cr) > sp) Or (Abs(J & cr) > sv And Abs(K & cr) > sp) Or (Abs(O & cr) > sv And Abs(P & cr) > sp) Then
     
      myCell.EntireRow.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
 "=OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & …
0
1 Comment
After running a macro to query a database that changes daily, I get two worksheets with formatted reports that may randomly vary between 10 and 15 rows each day.  

I would like to copy the rows from Sheet1, and paste it below the last row of data on Sheet2, leaving a blank row between the two reports.  I would add this code at the end of the macro to generate the merged report.
Example-of-Merged-Reports.xlsx
0
4 Comments
Determine the financial year based on a date column

Surface Date      Financial Year
30/06/2017      2016/2017
1/07/2017      2017/2018

The financial year goes from July 1 to June 30.
0
4 Comments
I have a table with about 5 million records.
I would like to use MS Excel's Get External Data to retrieve records for a specific day (only a few thousand records).
The challenge is that Get External Data wants to return the entire table.
How do I create a query with Get External Data and pass a date parameter so that only the records for that day are returned.

Thanks.
0
1 Comment
Hello again Experts,

Attached is sheet that contains more date but as a preview some lines are quoted below....
I have used Excels 'weekday' formula,  but I need if 1->7 (Monday through Sunday) then translate as "everyday", if 1->5 (Monday through Friday) then translate as '"Mon-Fri", where there's only 3 translae as Wednesday, etc.



SESSION_NAME      PROCESSING_DATE      day number      cycle name

ACDC0010      23-12-2019      1      
ACDC0010      24-12-2019      2      
ACDC0010      25-12-2019      3      
ACDC0010      26-12-2019      4      
ACDC0010      27-12-2019      5      
ACDC0010      28-12-2019      6      
ACDC0010      29-12-2019      7      everyday
ACDC0010      30-12-2019      1      
ACDC0010      31-12-2019      2      
ACDC0010      01-01-2020      3      
ACDC0010      02-01-2020      4      
ACDC0010      03-01-2020      5      
ACDC0010      04-01-2020      6      
ACDC0010      05-01-2020      7      everyday
ACDC0010      06-01-2020      1      
ACDC0010      07-01-2020      2      
ACDC0010      08-01-2020      3      
ACDC0010      09-01-2020      4      
ACDC0010      10-01-2020      5      
ACDC0010      11-01-2020      6      
ACDC0010      12-01-2020      7      everyday
ACTU0010      06-01-2020      1      
ACTU0010      07-01-2020      2      
ACTU0010      08-01-2020      3      
ACTU0010      09-01-2020      4      
ACTU0010      10-01-2020      5      mon-fri

I hope it's clear and not too hard to achieve this, it would be great help for me.
Thanks in advance
sesprocdate.xlsx
0
11 Comments
I have question regarding copying a value from excel cell. I know how to paste value or formula or formatting and so from one excel cell to another cell, that is not issue.

I want to know why I am getting different results  if I highlight only cell content :  text or number(I do not have formula in cell) and copy and  paste it to other location(it could be notepad) or if I click on cell( select it) and then do copy and paste it.
I have issue that sometimes when I select - click on cell( not just highlighting  content in) and copy it to other application not excel  I got different results.

Is it possible that clicking on cell instead of selecting(highlighting)text or particular number in the cell  I could copy some extra not visible values from that cell( e.g. line return I got this when I copy cell value in notepad ) or additional space after the end of text or  end of number in the cell .

How to avoid it?  I what to copy only visible content of cell?  Highlighting content every time then coping is time consuming. Is there other way to do that and to be sure that I copy only visible content of cell (letters of numbers no line return)?
0
10 Comments
With the help of you experts I solved my last question 29175699 on Bilinear interpolation on missing zvalues excel.

Pure reasoning makes me think it would be possible to take an other element out of the equation.
Would it be possible to get the y value through the input of a z value and x value.

My mathematical skills are near to none. How to go about to make this work. Any suggestion to get me started are welcome.
0
6 Comments
I have account number in this format 1-50101-007 say in cell a1 in cell c1 I enter the formula =right(a1,3) to get 007 what would I enter in cell b1 to get just 50101?
0
4 Comments
Hi,

Any expert out there experience in working Microsoft PowerBI?  I am searching for PowerBi videos or tutorials that are straight forward minimal talk.  Something that would show me the essence of the tool and maybe another small vid of how-to create a dashboard.
0
4 Comments
I have a worksheet that runs a macro to copy the data from one sheet ("WAV_Activity_Recap") to another ("Impact") if the values in column E + F do not exist in the WAV_Activity_Recap sheet. All works fine. My issue is I only want to copy the highlighted columns on the WAV_Activity_Recap worksheet to the Impact sheet (I do not need the extra columns on the Impact sheet).

Again, the macro works fine for copying if all the columns counts are the same.

Suggestions?
ImpactXfer.xlsm
0
24 Comments
I have a spreadsheet that contains a column for dates, countries, new cases, new deaths, total cases and total_deaths for corona. I would like to create a second tab that lists the date, has a column for each country and within this column contains the total cases.

I need a formula for these new cells so that when I over write the 1st tab, the values on the second tab are updated automatically.
I'm going to be using this for graphs, etc and dont want to manually do anything but overwrite the main tab.

I've included the sheet to give an idea of what I'm looking for on the tab Sheet 1Country_Data.xlsx
0
16 Comments
Excel 365 Windows 10 VBA Conditional formatting
PLTest.xlsx is small sample with data values changed
Corporate monthly #s  tab usually < 1000 rows
place 10000 in F2 10% in G2
format each qualifying row yellow from row2 to last row
Part 1  Is it a total line?     If Left(c2,5) = "TOTAL"  
Part 2 Is it an account line?  isnumber(b2) Account
Macro would run with these default numbers
I would like for the user to be able to change numbers and if necessary rerun the macro

Sub CF_Var()
'
' Macro1 Macro
'
    Dim Lastrow As String
   Lastrow = CStr(wksht.Cells(2, "C").End(xlDown).Row)
   
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "10000"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "0.1"
    Range("G2").Select
    Selection.NumberFormat = "0.00%"
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
    End With
    Range("F2").Select
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
     End With
     'IF left(c27,5) = "Total") start with c2 and check to last row.
'NEED LOGIC HERE to check all rows for TOTAL  
'Version TWO check if column B is a number
  Rows("12:12").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(AND(ABS($F27)>$F$2,ABS($G27)>$G$2),AND(ABS($J27)>$F$2,ABS($K27)>$G$2),AND(ABS($O27)>$F$2,ABS($P27)>$G$2))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Open in new window

…
0
7 Comments
How would I write a formula to check a cell and if it falls in between and including 10/1/18 and 9/30/20 then write lapsed in the corresponding cell next to it
ie
date                     new cell
10/1/18                 lapsed
10/15/18               lapsed
0
1 Comment
Hi,

I am attaching a file. It has a column Dealer Code which contains second char as N for North, and so on for all Regions. Towards right in the sheet, from cell G9 onwards I have a summary where I want to get the count and sum from Dealer Code column - Count on number of Dealers in each Region, and, Sum on Deposit.

Yes I can, but do not want to create a new column getting Region using IF function. So without creating any column in main data, how can I get Count of each North, South, East and West, and, Sum of Deposit.

Thanks,
San.Count--Sum-Built-in-Char.xlsx
0
5 Comments
In the attached sheet currently is calculating a 3% match for employee 401K contributions in cell F5, I need to change this calculation to a 3% match if column CYOS is 5 years or less, a 6% match if column CYOS is between 5 to 10 years, and a 9%  match if column CYOS is 10 years or more
Book1.xlsx
0
10 Comments
I have a file with a list of names and what I want to happen is to create a button on the Registration page e.g. in cell F5 that would automatically copy the details for Larry into the Apples sheet onto a new row.
Then I want a similar button in the Apples sheet that would MOVE his details into Bananas if so required and ditto for Oranges.
Copying_rows.xlsx
0
7 Comments
I am trying to find an excel formula to lookup an amount in one column based on a date that is between a date range in two columns.  For example, column A has a start date, column B has an end date, and column E has the amount I need to find.  Is there a way to use XLOOKUP for this?  What would the formula look like if XLOOKUP will work?  Or do I have to use a really long nested IF statement?  I have Excel 365.
0
6 Comments