Improve company productivity with a Business Account.Sign Up

x

Microsoft Excel

133K

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,
Can you help me with a Vlookup formula for 2 conditions
example:

                           lookup table (value)
                                         
                               cat      0     25
                               cat      1     50
                               cat      3     80
                               dog     0     11
                               dog     1     27
                               dog     7     56

what is value for cat 1      answer 50
what is value for dog 0     answer  11

Many thanks

Ian
0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have around 20 Excel files with around a total of 5000 rows.  I have a further list with around 400 rows.

I need to delete the rows in the 20 excel files which contain the list of 400 rows.

example:

delete all rows in excel with data where row b contains one of the values in excel with criteria

excel file with data
---------------------------------
row a    | row b | row c
---------------------------------
martin  | boy     | young
roy        | boy     | young
steve    | boy     | old
anna    | girl      | young
afgi       | unknown | boy


excel file with criteria
row a
---------
boy
unkown




thank you for the input on how to delete the rows in excel with data which contain the values in excel with criteria.

thanks!!
0
In the following code mail will be generated if the cell value is grater than 200.
But i need to send mail if the cell value is less than 200.  

Option Explicit

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 200

    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("B8")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub
0
4n to matching by week

1week,  2week , 3week  

13 set is one week
29095479--3-_4n_matching_W_to_.xlsm
0
I want to enable the gridlines w/o enabling the sheet.  Do I have to activate the sheet before I update the gridline property ?

'this works.
    ThisWorkbook.Sheets(1).Activate
    ActiveWindow.DisplayGridlines = True

'why can't I just do this ?
ThisWorkbook.Sheets(1).DisplayGridlines = True
0
2n to matching by week

1week, 2week  , 3  week  

13 set is one week

b2222.PNG29095680_2n_matching_by_week_to.xlsm
0
What I'm trying to do is to attach a macro to a button (which I can do) that changes the contents of a range of cells based on whether or not they contain numbers.  Like ticking off a to do list, in a sense.  The cells in range D11 through W15 (5 rows of 20) are each numbered 1 through 100.  Upon clicking the button, I would like to make a script that checks the first cell is still a number and if so, change the cell value to "X" instead.

I have no experience with java but plenty with VBA and am now in a new job where I can only use google sheets instead of excel.  I can imagine ways to do this with a for next loop or a while wend loop but I don't even know the syntax for java, let alone what commands and functions it has to do this.  Thanks, experts!
0
original_question.xlsx

A solution to a similar question to this one has previously been provided:
https://www.experts-exchange.com/questions/29094062/Lookup-data-based-on-values-in-another-column.html

This time, what is needed is the average value of the 30 years of "n" data leading up to and including the year listed in the "year" field (Column B). This result should be included in the "n_30yr_avg" field (Column C).

The first 52 values for "n_30yr_avg" have been solved manually in the attached worksheet.

Is there a solution that would provide this calculation for all records?
0
I had this question after viewing Nslookup in Excel.

I have domain name in A1 and the formula "=GetStringFromIPAddress(GetIPAddressFromHostName(A1))" in B1, using the macro found above along with the authors update.  However no matter what domain I use I get 0.0.0.0 instead of the IP.  Any help would be appreciated.
0
Hi

I am creating multiple invoices for multiple client, on each month using Service invoice template which is avaiable in excel, i am creating multiple invoice using this template, each excel file for each client, when ever i am receiving request i will create a template and save into seperate folder, at end of the month i will conver everything into PDF and send it to client,

Intitally i was done this only for 10 clients, not it become 40+ so it's consuming more time for me, please help me to simplify this job, i tied with few online convetors, but it's not working properly,

Thanks!
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Dear all,

I am trying to create a small program on excel for my friend since he is on budget. He wants the TOTAL field to SUM or SUBTRACT from the previous AMOUNT.
I have created everything except the last field. I tried many times and google it a lot.
Please assist me #excel_expert.
0
Excel Row Texture Design
How to add the texture in rows?
0
Hi,

i have 2 files in the below mentioned cells, and i have given below code to open, i want activate file1, i have given below code its not working.
Could anyone please suggest
File1 = Sheet1.Cells(7, 3).Value
File2 = Sheet1.Cells(12, 3).Value

Workbooks.Open Filename:=File1
Workbooks.Open Filename:=File2

workbooks("file1").activate

Thank you in advance.
0
Trying to convert Excel, with Macros, to Google sheets.

I can’t find a GoalSeek Equivalent in Sheets .

Sub Calculate()

Sheets("Amort").Range("N22").Offset(rowOffset:=Sheets("Amort").Range("J5").Value, columnOffset:=0).GoalSeek Goal:=0, ChangingCell:=Sheets("Amort").Range("P10")

Sheets("Amort").Range("W22").Offset(rowOffset:=Sheets("Amort").Range("J5").Value, columnOffset:=0).GoalSeek Goal:=0, ChangingCell:=Sheets("Amort").Range("P12")

Sheets("Amort").Range("AY22").Offset(rowOffset:=Sheets("Amort").Range("J5").Value, columnOffset:=0).GoalSeek Goal:=0, ChangingCell:=Sheets("Amort").Range("P14")

End Sub
0
I have a EXCEL file w/ 3 fields: "Emp-Num", "Emp-Name", "City Coverage"

"City Coverage" has 1 to many names of cities covered by an employee.  If more than 1 city, the cities are separated by a comma.  I need to copy each record to another EXCEL file.  If an employee covers more than city, then that record is copied N times; once for each city.  The output file has an additional column; "City Total", which has the count of cities covered by the employee.  

I am very familiar w/ VBA, but have not used VBA w/ EXCEL.  Please help. Thank you, very much. Enclosed, is an EXCEL file w/ 2 tabs:  "Original" shows the data.  "Final" shows what the data needs to look like when completed.Test_Data1.xlsx
0
2n convert 0 to
2n_convert_0_to.xlsm
0
0
The worksheet contains column A that lists a name (A2) and under that name, 11 cells containing labels. Column B lists corresponding data on the same row, for each label in A.
In the original worksheet, this is repeated a few hundred times.
In the example, the 11 cells have been manually transposed to the same row as the "Name" label, starting from Column E, and extending to Column O.
And the data from column B has been manually transposed across these columns, on to the same row as the name.

Required: a script or formula to do this for several hundred entries.
Marina-listEE-request.xlsx
0
3n to adjust repeat to ord

then show
29094839--1-_3n_ord_pair_0_to.xlsm
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

3n to adjust repeat to ord

then show
29094839--1-_3n_ord_pair_0_to.xlsm
0
Hello there, i have a macro that copies content from Word and organizes them in Excel. The macro copies the text in the shaded cell in the Word file, and then it copies the text in the cell beside it to the right. My problem is that it can't copy the displayed text in a Drop Down Form Field, some of which are in the cells that are beside the shaded cells. I tried to implement this code:

Dim strText As String
Dim ix As Integer

ix = objDoc.FormFields("Dropdown1").DropDown.Value
strText = objDoc.FormFields("Dropdown1").DropDown.ListEntries(ix).Name

Open in new window


On to the macro:
Option Explicit

Sub WordToExcel()

    Dim sh As Excel.Worksheet
    Dim strFolder As String
    Dim strFile As String
    Dim strFullName As String
    Dim r As Integer
    
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    'Insert Folder Link
    strFolder = "C:\MyFolder\"
    strFile = Dir(strFolder & "*.doc*")
    r = 2
    Do Until strFile = ""
        strFullName = strFolder & strFile
        CopyTableFromDocx strFullName, sh, r
        strFile = Dir()
        r = r + 1
    Loop

End Sub

Sub CopyTableFromDocx(strMSWordFileName As String, sh As Worksheet, r As Integer)

    Dim objDoc As Word.Document
    'Dim lngTableIndex As Long
    Dim objWordTable As Word.Table
    Dim objWordCell As Word.cell
    Dim strLabel As String
    Dim strData As String
    Const mtrDsc As String = "? "
    Dim c As Integer
    Dim t As Integer
    Dim bFound As Boolean
    'Dim lngRowIndex As Long, 

Open in new window

0
I have these two Excel files attached. One is a chart I saved out of PPT. The other is the content of that chart saved to a separate Excel file. The problem I am having is that the file size of the Chart file is much larger than the manually created Book1 file that I made with the content I wanted from the Chart file. I have a PPT file that has several several charts like this, so I am trying to clean the chart files in PPT of whatever is creating this extra space so that it will in turn reduce the size of my PPT file. I have already tried the embedded font steps and everything else suggested to reduce it (these charts must remain Excel editable too as opposed to pictures). So can someone tell me what is creating more size on the Chart file compared to the Book1 file and how to get rid of it? We are using Office 2013.
Chart-in-Microsoft-PowerPoint.xlsx
Book1.xlsx
0
I have a column with various names on column A1. I want to put a formula on column B1 that looks at column A1 and if A1 has the words "Dorm" return the value "Dorm" else leave blank. Below is an example of what is con column A1 and A2. Do you have an Excel formula that can do this?

8858T17 - 2017
8858DORM
0
I am not sure if this can be done.  I have an Excel sheet with various information, one of the columns is a follow up date.  I have put some conditional formatting so that today's date appears in read in that column.  However, the user would like to have all the rows that need to be followed up today appear on a separate sheet.
I was thinking that since I can do a formula such as =Sheet1!A3 is there no way to have an IF... THEN... formula that would do the =Sheet1!A3 IF C3 = today's date?
Thank you for your help
Jeannie
0
Looking for a Powershell solution to convert all Excel documents in a specific folder to CSV files.  Also it needs to convert a specific worksheet within all the Excel documents.  All the Excel documents are formatted exactly the same.  I found this solution but it does not convert a specific worksheet (see below).  It only converts the last opened worksheet.  

https://gallery.technet.microsoft.com/office/How-to-convert-Excel-xlsx-d9521619#content

Anyone have this Powershell solution?  I prefer a solution that does not require Excel installed but any solution would be helpful.
0

Microsoft Excel

133K

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.