Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Microsoft Excel

132K

Solutions

37K

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

I am trying to calculate the total number of times an item is used when it is on the same invoice with 2 criteria.  First, the order numbers (column A) have to match and 2) the item number R1606 also needs to be on the same order.  I have attached an example using item numbers R1606 and 24410.  Any help would be appreciated.
SUMIFS_EE.xlsx
0
Free Tool: Site Down Detector
LVL 11
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

So I've viewed many similar cases, but still cant seem to make it work.
I have a simple on/off choice from a drop down menu. Cell "Q19".
When either on or off is selected I want to either show or hide columns.
The following script works fine when assigned to a control button, however I want this to be automatic once the selection has been made from the drop down.
Any ideas?


'To Hide and Show Columns for Custom Rubber Properties
Sub Hide()
        'Hide when Q19="OFF"
        If Worksheets("Quick Start").Range("Q19") = "OFF" Then
            Worksheets("Quick Start").Range("S:AF").EntireColumn.Hidden = True
        End If
        'Unhide when Q19="ON"
        If Worksheets("Quick Start").Range("Q19") = "ON" Then
            Worksheets("Quick Start").Range("S:AF").EntireColumn.Hidden = False
        End If

End Sub
0
Hi,

In the attached spreadsheet i need some match index formula in tab "Pnl" which is looking up tab "Data_Tab"

I need the totals for each weekending (i have manually entered some for guidance)

In row 11 i have put the names of the columns the totals need to match against

Can someone provide the formula so these numbers will fall out in the pnl tab

Thanks!
Seamus
HOWL-REPORT.xlsx
0
I have Excel 2010.  The following vbscript file is executed by a scheduled task.  It opens a file to update it, save and close.  The file has the "read-only recommended" option on and my script must answer this prompt with "no" which is not the default.

Option Explicit
Dim objXL
Set objXL = WScript.CreateObject("Excel.Application")
With objXL
      .DisplayAlerts = False      
      .WorkBooks.Open FileName:="S:\Schedule\EngDates.xlsm", ReadOnlyRecommended:=False, IgnoreReadOnlyRecommended:=True
      .DisplayAlerts = TRUE
      .Visible = TRUE ' Optional
      .Run "EngDates.xlsm!UpdateDash"
      .ActiveWorkbook.Close(True)
      .Quit
End With
Set objXL = Nothing

I have tried several different syntax formats, with and without parentheses, and nothing has worked yet.
When I run this script, I get an error as seen in the attached file.  Does anyone see the problem with my syntax?  
Thanks!
scriptError.png
0
I have a query that loops through a list of db schemas and outputs the results to individual pdfs. I would like to append the results to a single excel spreadsheet instead of individual files. How do I do this?

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String



Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("UsageReport")

ssql = "Set nocount ON; select * from air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

On Error GoTo UsageReport_Err

    
    DoCmd.OutputTo acOutputReport, "UsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint


UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit
Loop
Close 1
End Function

Open in new window

0
Hello:

We have an XML file that we would like to open in Excel.  We have tried two methods.  Both have failed.

First, we tried opening Excel, browsing out to the file, and simply opening the file.  But, upon trying to do so and using one of the suggested methods, we get an error saying "cannot load the specified XML".

Also, we have tried opening the XML file by opening Excel, choosing "Data", "Get Data", "From File", and "From XML", we get the error message "XML processing failed...Data at the root level is invalid".

How can we, then, open an XML file in Excel?

Thank you!

John
0
I have an Excel formula that looks like:

=IFERROR((MAX((((G11-$M$5)*F11)*$M$6),0)),"")

The corresponding Access form fields are:

G11 = txtMST
M5 = txtMoistureMax
F11 = txtCleanCWT
M6 = txtDisSchd1

How do I convert this to Access?
0
Hello All,

I have workbook A, B, C, D etc in a folder...
each workbook has four sheets... sheet 1, sheet2 etc.

I want to combine the data from sheet1 of all workbooks into sheet1 of master workbook (which already exists) and similarly sheet2 etc.

The workbooks are from different users... they all have same headings.

Your help will be much appreciated.
0
What is the fastest and most stable VBA code that will import a range of data from another workbook without opening it.

Example:
Copy Ranges B2:B2000 from a closed workbook located at: C:\Files\Samples\Test.xlsx and past it to Cell C200 in Sheet1 of the current workbook.
0
Hi

With excel vba we are trying to read an excel worksheet (“sheet1”) row by row and access the specific cell contents (row/column) and write it to another worksheet (“sheet2”).  Please advice.
0
Get expert help—faster!
LVL 11
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Trying to find the true creator of an Excel file on the network.  This is a file going back to 2013 and the 'author' that shows up under the properties is not the true/original creator of the file.  

How can we verify the person that actually created this file?
0
I have a spreadsheet that gets opened automatically every day to pull data from other sources. I have a list of dates for every work day for the year in column A. In column B in the first row I have a formula to start. None of the other rows in column B have this formula yet. What I need to do is when the file is opened every morning automatically I need the formula to be copied down to the row that has the current date in column A and then paste the values in the row where the copy originated from. For example if A1 is 1/17/18 and the formula is in B1 I need to take that formula and copy it to B2 since A2 is 1/18/18. And then paste the value that is already in B1 so the formula is no more in B1 and is now in B2. How can this be done in VBA?
0
I have a data source for a excel sheet that I added via the admin account to the control panel => data source when Im logged into the computer with an admin account it opens just fine, but when I log in under a standard user it does not. Is there away to a permission so any user on the computer can access the data source.
0
I have the following excel SUMIFS which works well but now I also need to add to the sum column H When Q = Civilian OR column Z contains SOLD

=SUMIFS(Report!H:H,Report!Q:Q,"Civilian",Report!S:S,"06*")
0
Good day
I have no experience is javascript whatsoever (this is my first attempt)  but my aim is to run excel macros on our server from a web based environment . This will enable me to only give the user what I want him to see.
I am trying to use the office.js API calls to change a value in the spreadsheet containing the macro and a private sub will then run the macro based on the change which occured.
The code below which I have copied from Microsoft I get the error "office is not defined" when I am trying to run the javascript in the Chrome console.
I would also like to know how can I change the code in order for the macro which will always run on the server to execute from any webpage. In other words how do I define the workbook location dynamically?
Office.initialize = function (reason) {
    $(document).ready(function () {
        switch (reason) {
            case 'inserted': console.log('The add-in was just inserted.');
            case 'documentOpened': console.log('The add-in is already part of the document.');
        }
    });
 };
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Run macro");

    var range = sheet.getRange("B2");
    range.values = [[ 1 ]];
    range.format.autofitColumns();

    return context.sync();
}).catch(errorHandlerFunction);
0
In sheet 1 Copy the data if column A contains True and if false then leave that And paste the data in sheet2
Kindly check my workbook in sheet1 i have highlighted the data to copy with green colour and in sheet2 i have highlighted the pasted data
Example.xlsx
0
hi,

I have a long long excel to update everyday and I want to make the same cell/position can be directed automatically, like if I update cell A and then I will go to update cell B, I do it everyday, is there any configuration in eXcel can do that once I update cell A, it will JUMP/Switch to Cell B for me to update ?

also any way to make excel 2010 lock down some cells, just in case i update the wrong cell which I don't suppose to do it, the result is , when I modify that cell I don't allow/suppose to , I will find out that I can't update it.

any idea?
0
Hi,

I routinely get PDF files for review and analysis. What is the easiest way to search and filter for txt that starts with "Path:" and MD5: in my giant PDF report in order to export or extract ALL instances of these txt types i need to export from PDF and put into excel for analysis thru a table in excel?
0
There is a message " Due to high inflow of call talk times were impacted " .... As soon anyone opens my excel attachment I want this message to scroll from left to right and keep scrolling on the screen
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi

We use excel 2013  and Please see the attached spread sheet and within this is spreadsheet we have sheet 5 and sheet 6.
On sheet 5 there are 800 users with SURNAME and FORENAMES all the coloured colum have data and I have removed it.

On sheet 6 there is there are 750 users and i would like to copy all the corresponding data( as per their Surname and Forenames) from sheet 6 to 5
Please post me if there is any formula to do or do i need to manually copy the correponding user data from sheet 6 to 5.

Thanks  and any help will be great
Data.xlsx
0
IN COLUMN A, I HAVE A LIST OF ZIPCODES, IN COLUMN B, I HAVE A LIST OF COUNTIES OF THESE ZIP CODES. I NEED TO MATCH THE COUNTIES WITH MY OWN LIST OF ZIP CODES IN COLUMN E (THERE ARE SOME DUPLICATED ZIP CODES). I GUESS I NEED TO USE MATCH AND INDEX BUT I JUST CAN'T WRITE IT CORRECTLY?
0
This may seem like a dumb question and I may be stupid for asking this in the first place but I tried the obvious and still I can't get my worksheet to lock delete cell data only.

I made sure to activate locked cell protection on Format Cells:
Capture111.PNG
And I even select all the checkboxes in Protect Worksheet except for Delete Rows and Delete Column (since I don't want users to do that).
Capture110.PNG
But all I get from that is either the worksheet still allowing me to delete or the entire worksheet to stop responding and crash. Is there a reason for this? Or am I doing it wrongly?
0
I have a UserForm in PowerPoint with a button on it that opens the Excel file picker dialog. When the file is selected, PowerPoint VBA then opens the selected Excel file in a hidden instance, loads some data from it into the UserForm and leaves the file open whilst the UserForm remains loaded/visible. When this happens, the PowerPoint icon in the Windows taskbar pulses and a second PowerPoint icon hops up and down, indicating that PowerPoint no longer has focus. This is confirmed by the modal UserForm remaining in a defocussed state.

I added several variations of the AppActivate statement to the PowerPoint VBA code but none work, although no error is raised e.g.

AppActivate Application.Caption
AppActivate "PowerPoint"

Open in new window


However, if I use the same AppActivate command(s) from the Excel VBE when in the defocused state above, the command works as expected.

How can I get the PowerPoint VBE to return focus to PowerPoint in this case?
0
I am confused. When I export from Excel into CSV and I open the format of any of the records in a column (see attached) all it says is the format is GENERAL. Okay that is fine but what is it. A string, a number or what. I am having a terrible time importing into Salesforce.com as I get errors on import. Not knowing or understanding what is being sent out and what needs to be pulled in it is confusing to know just what General is. I have searched for a solution as I would think there would be an answer to that. What is the GENERAL format and how do most programs accept that. I would think it would be a generic older format that is acceptable by most programs easily. Not knowing whether a column with only numbers in it or another column with alpha numeric is handed off so that other applications can determine what value is assigned string or number is a mystery. Can someone solve this for me. Is the data type determined coming from an Excel CSV in GENERAL format or is the application doing the import determining the data type....? Any help is greatly appreciated.
SF_Tasks_Data_CSV.csv
0
I have an inventory excel sheet that has been combined with 2 serial numbers. I need to sort them so that column F and H match up and keep all of the other cells with the column H data.

Attached is an example, F and H need to match up, but I need the info in the rest of the sheet to follow H.
example.xlsx
0

Microsoft Excel

132K

Solutions

37K

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.