Microsoft Excel

135K

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,

Is there a way to look for a specific header, say, "ReportAsOfDate"

And add a new value to the entire column? thanks
0
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

word document i made changes to its content by adding more pages and changing digrams, headings etc.

Table of Contents in first page is not reflecting the changes with changed page numbers, navigation etc. Please advise how to fix it
0
Hello,

I have a list of part numbers that I need to search through and compare the first two characters of the part number to another cell.  Then I have to use a multiplier in another cell in order to give a final sales price.  In the attached image I have three possible markups and a default markup.  So for Mark up 1 I want to search through column E for and part #s beginning with "45" for all those part #s I want to multiply the Costs X 1.5 and place the value in the Sale Price column G.  For Mark up 2  I want to search through column E for and part #s beginning with "S7" and for all those part #s I want to multiply the Costs X 2 and place the value in the Sale Price column G.  There is no field for Mark up 3 (for future use).  All other part numbers use a default mark up of 1.75.


I tried using VLOOKUP, but it's too limited.  I have tried to use INDEX, but can't get that to work either.


I'd appreciate any suggestions.

Thanks,

Larry
excel.JPG
1
I think I'm going crazy!!  
Using the excel function WORKDAY I want to check whether the date in a cell is a Weekend (Saturday or Sunday) and if so for it to give me the next date backwards that ISN'T a weekend. I also want to verify if it falls on a Bank Holiday which is in a separate sheet

I can get it to work fine if I am wanting to go back a number of days but NOT if I want to stay on the same date less the weekend where appropriate!!

I am specifying: WORKDAY(Q3,0,'Bank Holidays'!$A$1:$K$1)

 if I specify WORKDAY(Q3,-2,'Bank Holidays'!$A$1:$K$1)  then I do get the prior 2nd day back not on a weekend.

What am I doing wrong?
0
All,

I have a test sheet based off another sheet that I have. What I need to do is have the 3 columns display only the data that references what is in the lookup field. For example, if "xyz" is in the error column, I need the data for the 3 columns to display on the lookup sheet, and display all rows.
sampleBook1.xlsx
0
Hi

On my ASP.net web page I want a user to download certain information that pertains to their user ID
in an Excel spreadsheet.

I currently use the following code to allow a user to download an Excel file, but I want to rather populate
a spreadsheet using SQL code and have that open

    Protected Sub btnDownLoadExcelTemplate_Click(sender As Object, e As EventArgs) Handles btnDownLoadExcelTemplate.Click
        Try

            If True Then
                Dim FileName As String = "Steel.xls"
                Dim response As System.Web.HttpResponse = System.Web.HttpContext.Current.Response
                response.ClearContent()
                response.Clear()
                response.ContentType = "application/vnd.ms-excel"
                response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & ";")
                response.TransmitFile(Server.MapPath("~/Resources/Steel.xls"))
                response.Flush()
                response.[End]()
            End If
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

Open in new window

0
I'd like to query a REST API (for harvest, but I guess it is a pretty generic question) from Excel 2016.

I have all the parameters I need to pass, ie
curl -X POST https://api.harvestapp.com/v2/tasks \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Harvest-Account-Id: $ACCOUNT_ID" \
  -H "User-Agent: MyApp (yourname@example.com)" \
  -H "Content-Type: application/json" \
  -d "{\"name\":\"My New Task\"}"

Open in new window


But how do I use the Excel built-in wizard for this ?

Excel settings
0
format a  date/time   column
These dates are just examples
I have a column with these dates : "General Format"


01-AUG-18 01.17.52.000000000 PM

I need it displayed/formatted as  date/time ?

2018-08-01 01:17:52 pm


no matter what i do the cell data will not change and there is no tick mark in the cells ?


Any help appreciated.

Thanks
fordraiders
0
In the attached example, I need to copy range B2:E2 and then paste the values to the right of todays date that is located in column G.
Example-File.xlsx
0
I would like to do a function in SQL that uses a regex string to validate email addresses and which also is not going to bog things down.
0
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

My brain just froze. I would like the AND/OR formula for the following please
C1 MUST Contain "RED"
D1 can contain "APPLE" OR "PEAR"
Destination cell A1
Many thanks
Ian
0
I know how to use VBA code to import data from an excel spreadsheet to an access table.  All of it work great.
How do I tell the code that the headers are on row 3 and skip rows 4 and 5.  If its possible.
0
I have two pivot tables that I need to control with one slicer. One of the tables is current year data. The other is last year data. How do I get the pivot table to change the year to the previous year using just one slicer?
0
How can I set up a formula that calculates a total from a range based on matching a date from a dropdown. I've been experimenting with Sumifs but can't get it to work.. In the attached file I have entered what I want the answer to be depending on what I choose from the list. Thank you as always.
EE_Cumulative_YTD_Show.xlsx
0
Attached is an Excel document, Excel_EqualWidthCells.Xlsx that has two sheets, one labeled "Structure".
The other is labeled, "Header-Footers", and looks like this:
Headers	Footers	 -- There is inconsistent use of "…", so assume that "…Begin" and "Begin" are keywords
…Begin	…End	 -- Notice the "…" acts like it is a singe char
…If		         -- Assume case insensitivity, so "BEGIN" and "begin" are also matches
…For		

Open in new window

For simplicity, I can remove the "…" from the document. Other tables have different keywords for their headers/footers, so I'll just change the corresponding headers and footer keywords in the VBA code.

The attached Excel_EqualWidthCells---ManualInden.xlsx document shows the desired result after the indentation macro is run.

The Excel_EqualWidthCells.xlsx file is very flat looking, yet describes a complex structure using headers and footers. I manually indented sections of rows so that I could see the structure. This is tedious, but doable.

One suggested approach in a different question is found here.
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42769597
"This can be done with a stack structure, where you push a set of items on the stack when some condition is detected and remove them when another condition is detected.  In your case the first set of conditions is a cell that begins with ("loop", "if") and the second set of conditions is a cell that begins with ("end").  If you're working in Excel, you might use the stack 'level' (depth) as a parameter in an .Offset() method.

"With as simple a problem as this one, your stack structure can be an integer variable.  You might indent as you iterate the rows."
0
Hi,

Have Excel 2016 workbook, With an Entity Volume (here in column "C" sometimes other columns), problem is that the software I got the data from have added string " cubic m" to the values and to sum up the data in the column I need to remove the added string With a click of a button. How do I get round that?
0
Attached below is a Word doc with a sample table that I try to copy to Excel. One row in the Word table may become multiple rows in Excel. That causes other problems when processing the Excel table. Ideally, the copy to Excel gives one row for each ACTION FIELD entry as it does in this Word table.

Word Table showing first 8 rows:
Shows first few lines of Word Table
Excel Table after copying first 8 rows has 22 rows. I would like Excel to have only 8 rows, since that is what Word had.
Shows first few lines of Excel Table - too many rows
Both the full sample Word table, and the attempted copy to Excel (with 22 rows) is attached.
Word_to_Excel_Issue.docx
ExcelHasTooManyRows.xlsx
0
long list of rows like the one in the attached in row 1.

i need to split it into two rows as i did in the below cells. sometimes three rows if there are 3 entries in column C separated by col.

as you can see 1st entries in C,D,E go into their own new row. 2nd entries go into a new other row
Book27.xlsx
0
Hello experts,

I have the following value in range a: C:\Windows\test\folder, I need to rename the last level of the string in order to rename the full path as following:
C:\Windows\test\1.Newfoldername.
I was thinking to add an additional column related to last level and apply the concatenate formula however I need to recover the string C:\Windows\test\. What formula should I applied in order to identify this value? Probably a find of "/" with right formula?
I attached dummy file.
Thank you in advance for your help.
Full-paths.xlsx
0
CompTIA Cloud+
LVL 12
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Using Excel Office 365. Trying to create a lookup function that would pull only the top 5 records per location out of a large list even though more than 5 records exist. If only 3 records exist it would leave 2 fields empty.

Screenshot. Type store # in yellow box and it would auto fill the green boxes. screenshot
I've also attached the excel file.
0
I would like to add the following into the VBA code below.  If B6=Yes, then J32;J33=NA.  The below code works but I don't want to screw it up.  Any help is appreciated!



If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J19"
            If Target = "Select" Then
                Range("J20:J21").Value = "Select"
            ElseIf Target = "No" Then
                Range("J20:J21").Value = "NA"
                Range("J37:J41").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J20:J21").Value = "Select"
                Range("J37:J41").Value = "Select"
            End If
        Case "J20", "J21"
            If Target = "" Or Range("J19") = "No" Then
                Range("J19:J21").Value = "Select"
            End If
    End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J22"
            If Target = "Select" Then
                Range("J23:J24").Value = "Select"
            ElseIf Target = "No" Then
                Range("J23:J24").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J23:J24").Value = "Select"
                Range("J37:J41").Value = "Select"
            End If
        Case "J23", "J24"
            If Target = "" Or Range("J22") = "No" Then
              …
0
I would like to add the following to the below VBA code: if J19 or J22 is "Yes", then change J41:J45 to "NA".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J19"
            If Target = "<Select>" Then
                Range("J20:J21").Value = "<Select>"
            ElseIf Target = "No" Then
                Range("J20:J21").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J20:J21").Value = "<Select>"
            End If
        Case "J20", "J21"
            If Target = "" Or Range("J19") = "No" Then
                Range("J19:J21").Value = "<Select>"
            End If
    End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J22"
            If Target = "<Select>" Then
                Range("J23:J24").Value = "<Select>"
            ElseIf Target = "No" Then
                Range("J23:J24").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J23:J24").Value = "<Select>"
            End If
        Case "J23", "J24"
            If Target = "" Or Range("J22") = "No" Then
                Range("J22:J24").Value = "<Select>"
            End If
    End Select
End If
Skip:
Application.EnableEvents = True
End Sub
0
Excel 2010 and later: What is the best way to password protect an Excel file so that it is read-only
0
I want to make it so people can’t copy/paste from cell to cell?  I have all the cells locked accept for the cells that will have drop down menus and the sheet protected.  I want to prevent people from copying from one cell to another because each cell has a slightly different answer choices in the drop down menu.  

Thanks!
0
I'm sure there is an easier way to do what I'm attempting....  I want J19:J21 to default to "Select".  If J19=No, then NA for J20 and J21.  If J20 or J21 change from NA while J19=No, then change J19:j21 back to Select.  If J19:J21 are changed to blank, then default back to "Select".  If J19=Yes, then J20:J21 default to Select.
0