Microsoft Excel

137K

Solutions

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.

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

Sign up to Post

Hi

I am using the following Excel formula where the result is sometimes #N/A. Can I format the cell
to rather show a blank? If not how would I adjust my formula to show a blank instead of this?

=VLOOKUP(Entry!B2,Lookup!A:B,2,0)


Thanks
0
Hi

I have an Excel sheet with simple formulas that point to another sheet eg cell A2 simply has the formula =Entry!B2
My problem, as per the following image, is that zeros are showing up for blanks on the source sheet. How do I adjust
my formula to show blanks? Thanks

1
0
In Excel what is the fastest method that will make it so whenever a user selects a cell the whole row is highlighted. Please show me methods that don't use conditional formatting.
0
I have a pivot table in excel with 3 columns. How do I make a 4th column appear in the pivot that repeats the maximum value in Column 3?

Example:
ID.  Name. Grade. MaxVal
2.    Billy.     89.        89
4.    Tom.    78.        89
9.    Carl.     82.        89

See how 'MaxVal' repeats the maximum value in the 3rd column? I've tried calculated fields, but can't get it to work. No guider columns please want to see if I can make a calculated field or something that is part of the pivot.
0
Hi Experts,

I'm trying to make a macro to fill an Excel file by opening an Excel containing data.
It should fill the column based on the month of the year and it should count if :
- the column "Date de réf." is in the current year
- the column "Désignation" contains the description string in the Calendar file.
- the column "Statut util." has ok

Is there a solution to this?
0
excel vbA  OFFICE 365

What I need:
I need to reformat some raw data on a sheet and create a new sheet with a formatted look based on a Column(a).
Basically grouping Column(a) into different sections
I have attached 2 screen shots-before and after look.

BEFORE FORMATTING
before formatting

and
AFTER FORMATTING
AFTER
0
There is something in the following code that is preventing my excel sheet from allowing me to use the undo button. Its greyed out whether or not the sheet protected.

Private Sub cboFloat_Change()
Debug.Print "Sheet1.cboFloat_Change" 'WARNING. DO NOT MODIFY OR MOVE THIS COMMENT.Sheet1.cboFloat_Change


End Sub

Private Sub cboFloat_Click()
Debug.Print "Sheet1.cboFloat_Click" 'WARNING. DO NOT MODIFY OR MOVE THIS COMMENT.Sheet1.cboFloat_Click

End Sub

Private Sub cboFloat_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)
Debug.Print "Sheet1.cboFloat_Error" 'WARNING. DO NOT MODIFY OR MOVE THIS COMMENT.Sheet1.cboFloat_Error

End Sub

Private Sub cboFloat_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "Sheet1.cboFloat_KeyDown" 'WARNING. DO NOT MODIFY OR MOVE THIS COMMENT.Sheet1.cboFloat_KeyDown
   'new
   On Error Resume Next
   Debug.Print KeyCode
   Application.DisplayAlerts = False
   
    If KeyCode = 13 Then ' Enter key
        'new
'        Range("B7") = cboFloat.Text
'        Range("B7").Activate
        Sheets("CITY_BUILDER").Unprotect Password:="Password1"
        Range("B7") = cboFloat.Text
        Range("B7").Activate
        Sheets("CITY_BUILDER").Protect Password:="Password1", UserInterFaceOnly:=True
    End If

'new
On Error GoTo 0

Open in new window

0
I need an excel formula...

I am trying to search the contents of a cell for "172" and return a "/27" if true and "/24" as false.

The problem I am having is there is the remaining full IP address in the cell of 172.16.x.x. Here is what I am trying:

=IF(ISNUMBER(SEARCH($H$21,"*172*")),"/27","/24")

Thoughts?
0
Hello experts,

The following procedure allows me to export range to a csv file:

Sub CSV_Export_Based_On_Range()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range

    Application.DisplayAlerts = False
    

    Set myWB = ThisWorkbook

    Set rngToSave = Range("A2:C30")
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    
    On Error GoTo Error_Routine
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        .SaveAs Filename:=ActiveSheet.Name & "_" & Format(Now, "YYYYMMDDMMSS"), _
        FileFormat:=xlCSV, _
        CreateBackup:=False, Local:=True
        
        .Close
    End With

   Shell "C:\WINDOWS\explorer.exe """ & ActiveWorkbook.Path & "\", vbNormalFocus
   Application.ScreenUpdating = True
    Exit Sub
Error_Routine:
    MsgBox err.Description, vbExclamation, "Something went wrong!"
    Application.ScreenUpdating = True

End Sub

Open in new window


I would like to add the following requirements:

1-Inputbox related to the range to export: "Please Enter range that you want to export, example: A;C"
2-Loop from first row till the last used range involved by range reported in first inputbox.

If you have questions, please contact me.
0
Experts,

Can someone assist me a removing all strings after the @(and including the @ symbol)

For example from the attached spreadsheet there is the following:

jordan@crowson.volkswagen.co.uk

I would like it turned into:

jordan

Thanks
sample.csv
0
Connecting VFP Database File to Excel (pivot table).
Dears,

I have a table in Visual Foxpro 9 that is being updated daily.
I would like to create a pivot table in Excel based on this table.

These are the steps I am following - but it is not working.. Please help.

1-Go to ODBC connection. Create a new one. Based on "Microsoft Dbase-Driver (*.dbf) _- Setup the name (DataCon) - Select version DBase IV - Select the directory where the Table is
2-go to excel - create a new document- Click on insert Pivot Table - Click on Choose connection -Browse for More-New source-ODBC DSN -Next - Select DataCon-Next- Select the table - raw-Name the connection (test.odc)-Finish
It gives me "ODBC DBase Driver - External Table is not in the expected format !
0
With column A having ABC, DEF, GHI
With column B having -5, 30, -100

Can I "in-place" sort by absolute value with a min/max array formula?
So that column D has, GHI, DEF, ABC
And column E -100, 30, -5

And also so that if I re-sort column A and B with new entries, column D and E will always recalc to be sorted by absolute value?
0
I have a csv file with 200 unique email addresses.  I have 15 emails that are included in the 200 original email addresses that I would like to find and delete from the original 200 emails. Is it possible to complete this in one search and replace?  If yes, I would appreciate your answer.  

Thanks
0
Dear ,

I have a sales chart from the beginning of the year until July, I put the label which reflects the value of the sales each month, I want to put down the graph icon working auto for total sales in all months (all period )
 for more details see the attached

Thanks
sales-v--2-.png
0
Hi

I have a qualification where students must select 6 study units from a list of 18. Each study unit has a different credit value (some are low credit and some are high credit) and the students must choose any 6 units to achieve a minimum of 372 credits.

Is there any way to do this on an Excel spreadsheet, so that 'ALL' possible combinations of 6 units are shown, using formulas or VBA? I have managed it, using formulas, when there are only 2 unit combinations, with a target of 121 credits, but can't quite wrap my head around 'any 6 from 18' with a target of 372.

I have attached a spreadsheet with the unit numbers, credit values, target and an example of OK and not OK combinations.

Any help would be much appreciated.

Regards

Terry
Unit-Permutations-Problem.xlsx
0
Hello experts,

The following procedure allows me to add specific string at the end or at the beginning:

Sub Add_Specific_String_Multiple_Columns()
    
    Dim sRng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
    
    On Error GoTo Error_Routine

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
    Case 1, 2
    Case Else
        MsgBox "Please enter '1' or '2'"
        Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    temp_rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)
    If VarType(temp_rng) = vbBoolean Then
        MsgBox ("No input!")
        

Open in new window

0
Hello experts,

I have attached a formula which allows me to random values from specific range. (I.e: Formula sheet)

I would like to take it as a reference to cover the following requirement:

1-InputBox: "Select the range in which is located your reference data."
2-Inputbox:  "Select the column in which you want to random the data"
3-Inputbox: "Report the last row in which you want to random values example: 16."
4-Random values as reported in formula sheet.

Reference data and column in which will be reported random values can be either in the same sheet or not.

If you have questions, please contact me.

Thank you for your help.
Dummy-file-random-data.xlsx
0
I am trying to write stock market price data to a table starting at 9:45 AM and every 15 interval thereafter during the trading day. At a minimum I want to write the time and closing price. It would also be nice to capture the High and Low price during that interval and write that data as well. I am reading the tick by tick current price from a cell on an Interactive Brokers dde spreadsheet.

Currently I am trying to do this via the Worksheet_Calculate event which pics up the price every time it changes. I would prefer to not use the Excel timer function if possible as I have had many issues in the past when it is running. If there were an external timer app that could be used that does not tie up Excel that would be nice.

Thank you very much!
0
Please can you help me with a formula to highlight duplicates within a range.


Column B contains Names of staff
Column C contains responsibilities of staff (example of days worked)

The duplicates are all to be found in column C, however I want the first need to look for the duplicates against the value they hold in B. example
Highlighted in yellow is what the formula would highlight "duplicate".

Column B will always be set out alphabetically. The formula will help me identify and then remove any duplicate instances in Column C.
0
I my Excel workbook 2016 I have two spreadsheet :
Detail_Data”  and “Pivot_Data”.
 Detail_Data has records from ms query that pull data from SQL Server.
I created pivot table from Detail_Data.
When creating pivot table I was check “Add this data to Data model”  as I need that features for some calculation.
I have issue that my pivot table is not refreshing when my Detail Data changed. I tried all combination nothing worked (Refresh All,  Refresh this connection on Refresh All ,unable background refresh, disable background refresh)

As on my Pivot Data not refreshed when I update Detail Data from data source I put this
Code in Detail_Data spreadsheet:
Name of my pivot table on Pivot Data sheet is PivotTable3.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WSd As Worksheet
    Set WSd = Sheets("Pivot_Data")
    WSd.PivotTables("PivotTable3").PivotCache.Refresh
   

End Sub
Still it is not refreshing pivot table.
0
In am on Excel 365 and I have two separate excel documents, i.e.
Document 1 has data entered in column "F"
Document 2 does not have data entered in column "F"

Wondering if there is an easier way other than manually to do the following:

If the data in Document 1 column "B" matches the data in Document 2 column "B" - populate column "F" on Document 2 with the data from column "F" on Document 2

Thank you!
0
Hi,

I have an excel file where i would like to count the number of time a "From A" and "From B" from list 1 exist in List 2 "From A" and "From B".

In below picture, in list 1 as an example, 3 times, the value "10" From B have an existing match on the "From A" value. The formula would count it.

So the formula i'm truing to build in column H2 is to count the number of match:
If From B values from both lists = cell G2 value
And List 1 "From A" have match in List 2 "From A"

How many match
I hope i'm clear on what i'm looking for.

How can i do that?

Please let me know if i'm not.

Thank you for your help
0
Columns A&B  have date ranges and C contains the week number for these ranges.  I would like to have a formula over in Column I that puts the week number in based on the service dates in Column H
Book1.xlsx
0
TestforExpert.xlsxTestforExpert.xlsxI need to pull out the employee name from a column of data. Attached is the file. On Sheet 1 Column A is a list of data (all similar format). On the 2nd sheet "Employees" is a list of our 7 employee's.

In column B on Sheet 1 I would like to have the Employee that is listed in column A. Note: There is always only 1 employee listed. As we grow as a company and add employee's, I would simply add the employee name to Sheet 2 and it would work for new employee's.
0
Hi. Whar Excel vba code would I use to save a single sheet as a separate csv file? Thanks
0