Microsoft ExcelSponsored by Flatiron School

133K

Solutions

220

Content

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.

Merge Data from Multiple Files

I need assistance in my current VBA (please see attached document).
I have 5 excel files saved in a folder on C drive. the macro is pulling all the data from all files saved in a .xlsx format and pasting it to the last empty row on the master workbook. I would want to switch this up and have each file from the folder to go to a specific tab in the master workbook. All the files in the folder have the same name and i would want them to go to the same specific tab. The files in the folder and the tabs have headers so i would want to extract the data from row A2. Another key request will be to have the data overwrite. i don't want it to look for the last row; always start from A2.

Ex. the 5 files in the folder are cars.xlsx, color.xlsx, model.xlsx, year.xlsx and type.xlsx

cars.xlsx should go to sheet1
color.xlsx should go to sheet2
model.xlsx should go to sheet3
year.xlsx should go to sheet4
type.xlsx should go to sheet5

Sub MergeTest2()

Dim SummarySheet As Worksheet
Dim NRow As Long
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim LastRow As Long

'Disable Events
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.DisplayStatusBar = True
.StatusBar = "!!! Please Be Patient...Updating Records !!!"
.EnableEvents = False
.Calculation = xlManual
End With

' this line of code forces to put all collected data into this …

0 Comments

Hi Experts

Im wondering if you can help

Im putting together a simplistic sheet (attached) that contains some data from the Media per publication date. The sheet is split into sections of five rows starting at A1 that contains the Date, with the subsequent rows 2 to 5 containing the data. In total the first range is A1 : L5. This goes on so the next section range would be A6 : L10 and so on, with the date in cell A6 and the data in 7 to 10.

I have managed to get some code to email the contents of the first range and, aside from being able to alter this manually to email each section separately, what im asking is if there is a way in which a selection box can be added to the code, and additional code to search in Column A for a Date and email the contents of the subsequent for rows beneath.
For example, if I chose 25/04/2018, then the contents of range A17 : L20 would be emailed to the desired address.

Is this possible? Any help would be much appreciated

Regards
J
Book2.xlsm

0 Comments

Question by

Adil Kh

5h ago

Stock cost error

Good morning everyone, hello Saqib Husain, Syed

I am refering to the discusion https://www.experts-exchange.com/questions/29084963/Excel-formula-for-calculating-the-Cost-Value-on-FIFO-basis.html

The formula gives a false calculation when i changed the price.

The bying price of AAA ligne 3 E:3 is 501,00. If i put 501,93, the formula calculate the same cost which is 12.525,00 as for 501,00 in I:9 but the correct result is 25*501,93 = 12.548,25 not 12.525,00. Thank you

Please see the file attached.

Thank you for reading

0 Comments

Question by

Rahul Bamola

10h ago

FIFO to calculate Cost

Sample.xlsb Sample.xlsbHello,

https://www.experts-exchange.com/questions/29084963/Excel-formula-for-calculating-the-Cost-Value-on-FIFO-basis.html

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.

Rahul

6 Comments

Question by

ADRIANA P

17h agoSolved

adjust eachD T

solution don't work

when spaces are between numbers

in columns B

sometimes spaces will be
eachDdigitT.xlsm

10 Comments

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.
C--Users-cameraop-Desktop-Spreadshe.xlsx

1 Comment

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?

2 Comments

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

4 Comments

Question by

Keki t

1d agoSolved

Loop between two ranges to hide data rows

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")


ThisWorkbook.Sheets("Data").Activate

 ' 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
    Else
    ActiveCell.EntireRow.Hidden = True
         ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
    End If
    
Loop

Open in new window

Microsoft Excel

11 Comments

Free Tool: ZipGrep

LVL 12

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question by

Leigh Macphee

1d agoSolved

Conditional formatting on dates

Hi,

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

8 Comments

Hello,

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");
&oWorkBook.Open(&fileTemplate);

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.

0 Comments

Question by

Ben Heath

1d ago

Formula Creating

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

4 Comments

Question by

Gwynneth Taylor

1d agoSolved

Pivot table layout

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?

11 Comments

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!

B.
Notice.xlsm

19 Comments

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.
Match-Excel.xlsx

6 Comments

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.
Copy-of-Costing-Form.xlsx

3 Comments

LVL 11

Question by

HuaMinChen

1d ago

Problem to send out Excel file

Hi,
I want to know what is being checked, within attached Excel file, as it was spammed, in Gmail.
BillofLadingSampleHELKA-MSZSE804259.xls

1 Comment

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?
Book1.xlsx

3 Comments

Free Tool: Subnet Calculator

LVL 12

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question by

yogesh bansal

1d ago

Excel

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?

15 Comments

I am trying to read an XML file like the one below into an Excel sheet
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\file214.txt</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>file214.txt</ShortName>
      </Item>
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\===file215.txt===</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>===file215.txt===</ShortName>
      </Item>
      <Item>
            <Filename>H:\MrX@gmail.com\inbox\Company\= file216.txt =</Filename>
            <Location>H:\MrX@gmail.com\inbox\Company</Location>
            <ShortName>= file216.txt =</ShortName>
      </Item>

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
etc
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:

Questions:
Why is this happening?
Other than equal sign, are there any other initial character that causes …

0 Comments

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
    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, 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

…

2 Comments

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

THANKS!

2 Comments

Question by

Gary Lens

1d ago

Re-run saved exports VBA Excel

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!

1 Comment

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.

Conernesto
SumIFS_2.xlsx

4 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft ExcelSponsored by Flatiron School

133K

Solutions

220

Content

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.

Microsoft ExcelSponsored by Flatiron School

Related Topics

Microsoft ExcelSponsored by Flatiron School

Related Topics