Microsoft ExcelSponsored by Flatiron School





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


based on question asked in above link, i want exactly the same, but with small modification.
 if in the start, there is no enough balance, then for all next coming rows, it is showing as not enough balance for SELL. can we redefine the logic in such a way that if balance quantity is negative, then only say "not enough balance" else calculate based on buy and sell.

Let me know if any more questions required. Attaching sample data after running FIFO logic given in above link.

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.

solution don't work

when spaces are between numbers

in columns B

sometimes spaces will be
I have a spreadsheet with 2 worksheets and am trying to set a value on one worksheet based on a value in the other worksheet using an excel formula. An example follows:

Worksheet 1 (WS1) has three columns (1) WS1-ROWID, (2) WS1-EXTERNALID, (3) WS1-DESCRIPTION, and (4) WS1-QUANTITY
Worksheet 2 (WS2) has two columns (1) WS2-ROWID AND (2) WS2-EXTERNALID

There is a one to many relationship between the two worksheets. For every row in WS2 there are 1 or more rows in WS1
The worksheets are related by the WS1-EXTERNALID and the WS2-EXTERNALID

I am trying to populate WS1-ROWID in WS1 with the value found in the corresponding WS2-ROWID. So, if WS1-EXTERNALID = WS2-EXTERNALID, set WS1-ROWID = WS2-ROWID

I probably didn't explain that real well but I've also attached a sample spreadsheet that should describe what I am trying to do.

Any help will be greatly appreciated.
I would like to have one excel file open on Monitor 1 and another excel file open on Monitor 2. I would prefer to NOT have to expand the Excel program window across both monitors. Is there a way to open an excel file in a separate window?
copy from one spreadsheet to another

I download a report from Oracle,  data from "sheet 1" needs to be copied (Range A1: Y5000) without opening the file and needs to be pasted in 2nd sheet of destination file

Source File can be in any of the excel formats ( XLSX, xls, XLSM)

Destination file is excel 2007 ( paste in the same format of source file)

Please help

Thank you
automation_t4.xlsmI am stuck on a macro. I am trying to use a data range that might change in number of cells used on sheet 1 as a driver to match to a set of values in a column (which might also move locations) as it comes out of a reporter that is used differently by each end user. If the values of the range on sheet 1 match and sheet 2 i want those rows hidden so that only the rows that match show, if the value shows up twice on sheet 2 (isn't unique) choose the first one.   I have used a search to find the row that i need to identify the column regardless of the location move for sheet 2, but i can't get the hide loop down. This is what i have. Any help you provide would be appreciated.

'Sheet 1 data range but again this length could change
Set Fin = WST.Range("B7:B15")


 ' first line of data*.
actC.Select 'starts from J3 on data sheet actC is the result of the search parameter
      ' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value = Cells(Fin, 2).Value Then 'causes mismatch error
        Fin = Fin + 1
    ActiveCell.EntireRow.Hidden = True
         ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
    End If

Open in new window


I want to use conditional formatting in an excel document to highlight:

Out of date dates - red
Will expire next calendar month (or in 30 days) - orange
In date - green

I have managed the red and green but the orange doesn't seem to work no what order I put the rules in.

I currently have my dates in format dd-yyyy as a specific date is not really relevant to my data

Any help greatly appreciated!

Many Thanks

Here is some of my code It is failing on the last line

oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";

ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

Examined all file permissions but still not working:
OLE Automation error in Workbooks.Open: ObjectDoMethod: Microsoft Excel cannot access the file '\\filepath\TDX_Template.xlsx'.

Any help would be greatly appreciated.
Hi There. I'm trying to produce a formula that when I type in a word into any cell in the A Column, it will search A Column on another sheet on the Spreadsheet and return the following 2 cells of information following that back into Column B on the first spreadsheet. Not sure if I have made this sound more complicated than it actually is
I have an excel spreadsheet with 950 patients each of which can have up to have procedures on one date.  Some of them have had more than one admission.  The column headings for example are Patient ID, Procedure Date, Procedure 1, Procedure 2, Procedure 3 etc. up to procedure 8. I need to count the average procedures for each admission.  What is the best way to set this out on a pivot table please?
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

EE Pros,

I'm looking for a macro that notifies a Excel WB User that two numbers are not =.  In the attached mock up, the "Imputed Tax Rate", calculated by dividing Income Tax by Net Sales, is different then the "Effective Tax Rate" which is entered as a number in a different cell.

I am trying to notify the user by a text box, a message or "comment" that only shows when the User selects the Effective Tax Rate Cell, or hovers the cursor over it (In the Mock Up, cell M19).  The only other conditions are; if the numbers are "=" there is no need to display the notice.  If the Income Tax cell is -0- or blank, the Imputed Tax Rate is then also -0- and may be displayed as compared with the Effective Tax Rate.

Hopefully this is an interesting challenge!  And as always, "thank you" in advance!

Hi EE,

I have the following spreadsheet see attached. I would like a formula to match the first name, last name columns on both sheets and insert the corresponding login name on sheet2 to the blank column on sheet1. What would be the best way to go about this is Vlookup or ISMatch I have done some reading online but can't determine which one would suit my needs best.

Any assistance is welcome.

Thank you.
Need someone help me automatically populate fields from hundreds of sheets (tabs) into a single summary sheet.

We replicate the Master sheet each time, rename the sheet to the latest number and then fill in the details. We need all those details to flow through to the 'Actual vs. Forecast' sheet.

Any questions please let me know.
I want to know what is being checked, within attached Excel file, as it was spammed, in Gmail.
How do i use macro to auto update my formulas and cell references? I have a pivot table containing sales data, with the columns stating months from January to March. But for every start of the month, i need to update the latest month (i.e. update to April when it's May) to show to a new column. I cant use getpivotdata because i need to track the cell reference, and the data will change for each month so i cant pre-count April's values. Beneath the pivot table, I have a normal table stating sales data total [i.e =sum(Jan-Mar)] which i have to manually update. Is there any way for macro to automate the normal table to update =sum(Jan-Mar) to Jan-Apr? Example like =sum(b1:d1) to =sum(b1:e1) and for subsequent months?
I have a excel file having 1 million rows and 2 columns. The first column contains all 0's and the 2nd column contains text along with some #tag.
I want to replace 0 by 1 in the 1st column where there is a specific tag called #true in the 2nd column.
Doing manually is not a good way. Is there any other way to achieve this?
I am trying to read an XML file like the one below into an Excel sheet
            <Filename>H:\\inbox\Company\= file216.txt =</Filename>
            <ShortName>= file216.txt =</ShortName>
I am using the following intructions for read the xml file and copy it to the working sheet

## all dim etc omitted to make this shorter

Set list = xDoc.SelectNodes("//Results/Item")
        For Each Node In list
            iRow = iRow + 1
            '***Note: node names are Casesensitive***
            On Error Resume Next
            mySheet.Range("A" & iRow) = Node.SelectSingleNode("Filename").Text
            mySheet.Range("B" & iRow) = Node.SelectSingleNode("Location").Text
            mySheet.Range("C" & iRow) = Node.SelectSingleNode("ShortName").Text
 My problem is that when the file name starts in "=", no shortname is copied
 That is, mySheet.range ("C"&iRow) is empty in cases 2 and 3 from the example above:
 Why is this happening?
 Other than equal sign, are there any other initial character that causes …
Hi There, i have a macro that copies and paste content from Word onto Excel. When the macro opens Macro-disabled Word documents, the Word files opens an alert, saying that the document does not have macros enabled. Every time that happens i have to click OK on the alert box for the macro to continue its tasks.

Here is 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

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, lngColumnIndex As Long
    'Dim strCleanCellValue As String

    On Error GoTo CleanUp

    'get reference to word doc
    Set objDoc = GetObject(strMSWordFileName)

    objDoc.Application.Visible = True
    t = 0
    For Each objWordTable In 

Open in new window

i have 300 rows

each row has 12-60 columns of data in it, but each row has a different starting column . i need to create a new column, B, and put the sum of the first 12 columns with data into it.

must ignore cells that have just a "0" in it.

for example, row 25, has 0 in columns 12-34. So I need column B to equal the SUM of B35-46

The 14th Annual Expert Award Winners
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

I have three ACCESS queries that i re-run from their excel SAVED EXPORTS. I have these excel files saved in a folder on my local drive. I then manually copy and paste the data from the three files into one workbook. Each excel file has its own worksheet. the master workbook has headers and i just copy the data from row A2 and onward.

I would want a macro that would run the saved exports from the folder and copy and paste the data from the files into the one master workbook.
sheet1.xls copies over to "cars" tab in master workbook
sheet2.xls copies over to "color" tab in master workbook
sheet3.xls copies over to "model" tab in master workbook

Your assistance is greatly appreciated!
I have an Excel workbook with a tab named "EPAdjustments" and a tab named "2015 TrialBalance". I need a fomula in 2015 TrialBalance cell G14. The formula should return the sum of Column G in EPAAdjustments if 1) column B in EPAdjsutments is equal to 2015 TrialBalance cell $F$2 2) EPAAdjustments column F is equal to 2015 TrialBalance Cell $A$14 and 3)  the name in EPAdjustments column C is equal to the name in 2015 TrialBalance cell G56. Do you have a formula that can do this? Attached is a sample of the data.

Thank you.

Is there a way to get a line break in place of the dash in the formula below?


I would want 1424-$6.3
 to look like
I am using the code below that I found online to delete records from a table, transfer new records into that table and then update data in a different table.  It prompts the user for the Excel file that needs to be transferred into the Access table for these operations.  Since this comes from a different department at different times the file name has a date as part of the name.  I didn’t have a problem with this until they added a password.  Now for the code to work the user has to open the Excel file first to enter the password and leave the file open.  Then they have to select the Excel file again when they are prompted from the code.  I don’t want the user to have to do this twice but don’t know what code to add or where to add it to make the file open and then continue with the transfer.  I would appreciate any help you can offer.

Private Sub cmdImport_Click()

Dim strXls As String
strXls = "\\corpfs03\SHARED\hcf&um\SHARED\Weight Watchers Database\" _
& "Weight Watchers - Reimbursements\Check Templates for AP\Current Year\"

    SendKeys "Y"
    SendKeys "Y"
    DoCmd.OpenQuery "qryDeleteChecksReceived"

    strXls = XLSFilePicker
    If strXls = "" Then
       MsgBox "Operation cancelled"
       Exit Sub
    End If
DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
    strXls, True
    SendKeys "Y"
    SendKeys "Y"
    DoCmd.OpenQuery "qryUpdateChecks"
End Sub

Function XLSFilePicker(Optional strPath As String) As …
I had this question after viewing Auto refresh webpage but maintain scroll position.

I love the assisted solution, but when using a .mht file it does not work.
Internet Explorer asks if I want to open the file and the mht file opens in a new window.

How can I open, refresh (and stay on scroll) of an excel generated mht file?
I have the below vba code for last modified date. However is there an equivalent code for Created?  

Also how do I test for if the last modified is “never”. Essentially I want to use the created date if last modified is never.

dtMD = ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)

Microsoft ExcelSponsored by Flatiron School





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.