Tags: XLM 4.0 MACRO

Sum data from a particular cell of last n sheets only

{0 Comments}

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets - x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed up.  So if the number entered in cell B2 of sheet is 3, then sum numbers from cell A1 of last three sheets.  Worksheets can get added, removed or renamed.

You may refer to my solution in this workbook.

Worksheet formulas in a newly copied worksheet should point to the previous worksheet

{11 Comments}

Assume a worksheet with formulas referring to the previous worksheet.  When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet.

In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by Move or Copy > Copy of sheet2), the formulas in sheet3 should refer to sheet2.

You may refer to my solution and Instructions in this workbook.

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{8 Comments}

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions - .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start from cell B3 (first header cell), there can be a variable number of rows and columns from cell B3 onwards.

The task is to consolidate data from sheet1 of all Excel files to multiple worksheets of one Excel file.

The Question and Instructions have been clearly explained in the "Question and Instructions" worksheets of this file.

To see the solution work,

1. Save the following zipped folder on our desktop.  Unzip the folder and place Data Files folder (with all its files) on your desktop; and
2. Change the path in cell B2 of worksheet "Query and file listing" of Destination.xlsx file to the path of your folder saved in step 1 above.
3. Click on the blue button on worksheet "Query and file listing" of Destination.xlsx file.

Extract text from a custom formatted cell

{21 Comments}

In range G5:G44, some numbers are entered.  These numbers are custom formatted with General" Dr" for debit entries and General" Cr" for credit entries.

One may want to extract the Dr and Cr in range H5:H44.

The following process will get the desired result:

1. Press Ctrl+F3 > New and in the Name box, type cell_format
2. In the Refers to box, enter this formula and click on Close

=GET.CELL(53,data!$G5)

3. In cell H5, enter this formula and copy down till cell H44

=RIGHT(cell_format,2)

Dr and Cr should now get appear in range H5:H44.

Consolidate data from multiple closed Excel files with different number of columns

{6 Comments}

Assume a folder on the desktop named Test.  Within this folder there is a another folder named Monthly Data.  Within this folder are MS Excel files received every month.  The number of columns in each file is not the same.  Therefore in the January .xlsx file, there would be 100 columns, in the February.xlsx file, there could be 115 columns and so on.  The number of columns in every month's file will always be greater than the number of columns in the previous month's file i.e. If at all the number of columns across files are different, there will only be additional columns, not fewer columns.  Therefore for calendar year 2012, if all files have been received for January to May 2012, the columns in May.xlsx file will always include the columns in other four MS Excel files.  One may very well assume the following:

1. Spelling of headings across all files are the same; and
2. Headings are always in row 1 across all files (from cell A1); and
3. Worksheet tab in which information is present in all files in named "Sheet1"; and
4. In the monthly data folder, there are only those Excel files from where information needs to be consolidated; and
5. The files could be saved in any Excel format i.e. .xls, .xlsx, .xlsm; and
6. The number of rows in each file is variable

In a Summary workbook, one may want to consolidate information from sheet1 of all workbooks lying in the Monthly Data folder.  Given different number of columns in each file, even if one had the patience to copy and paste information, it will not be possible to do so because of different structures.

You may try the following steps to consolidate information:

1. Open the most recent file, say May.xlsx, and copy the headings from row 1 to this new file (paste in cell A1)
2. Press Ctrl+F3 > New.  In the Name box, type FL.  In the Refers to box, type the following:

=FILES("C:\Users\Ashish\Desktop\Test\Monthly data\*.xls")

3. In cell EE2, enter =IFERROR(INDEX(FL,ROW()-1),"") and copy down till blanks start appearing.  This step will list down all file names from the Monthly Data folder
4. Save the attached Excel file on your desktop, press Alt+F8 > Macro1 > Run

You may refer to my solution in the this workbook.

Generate a list of all Excel files from a specific folder without using VBA

{54 Comments}

Assume a folder named Test on the desktop.  In this Test folder, there is another folder named Survey reports.  In the Survey reports folder, there are Word documents, PowerPoint files, Excel files, PDF files and all sort of other files types.

In a new workbook, one may want to list down all Excel files from the Survey reports folder (saved on the desktop) without using VBA.  Furthermore, for new Excel files added to the folder, the list should keep updating.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In Power Query, there is a feature to append data from multiple workbooks into one running range.  The detailed process to do so is described below:

1. Go to Power Query > From File > From Folder
2. Choose the Test folder on the desktop and click on OK
3. Filter the extension column on "Contains" .xls
4. Right click on the Name column heading and choose "Remove Other columns"
5. Click on Apply and Close

This should now list down all MS Excel files lying the Test folder.  This is a dynamic list i.e. add or remove Excel files from the folder and just right click on any cell and select Refresh.

Solution for all versions of MS Excel

Try the following procedure to get a list of all Excel files from the folder:

1. In the new workbook, press Ctrl+F3 > New.  In the name box, type FL
2. In the Refer to box, enter =FILES("C:\Users\Ashish\Desktop\Test\Survey Reports\*.xls")&T(NOW()).
3. Click on OK and Close

In cell A3 of sheet1, enter the following formula and copy down

=IF(ISERROR(INDEX(FL,ROW()-2)),"",INDEX(FL,ROW()-2))

If you add another Excel file to the Survey report folder, just press F9 anywhere on sheet1 and the file will appear in the list.

Change C:\Users\Ashish\Desktop\Test\Survey Reports\ to your actual path from where you want to extract the Excel files.

To generate a list of all tab names from in an Excel file, you may refer to the following post.

Generate a list of all tabs names without using VBA

{241 Comments}

Assume an MS Excel file has 4 worksheets - Sheet1, Sheet2, Sheet3 and Sheet4.  Insert a sheet before Sheet1 and name that tab as Summary.  On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards.  Furthermore, the sheet names so generated, should be dynamic for the following changes:

1. Sheets added
2. Sheets deleted
3. Sheets renamed
4. Sheets repositioned

While this can be accomplished by using VBA, you may refer to my formula based solution here.

To generate a list of all Excel files in a specific folder, you may refer to the following post.

Conditional testing without lengthy nested IF functions

{0 Comments}

Many a times writing nested IF() statements become cumbersome, lengthy and highly prone to errors. 

In worksheet named "Question", one would like to avoid using spare columns for computation of landed value for the three product sources ("CKD", "LMP", "IMP").  While one can use a nested IF() statement, it would be unmanageable because of the length of the formula.

One can have the computations for "CKD", "IMP" and "LMP" reside in a table and then refer to that table within formulas?

You may refer to my solution in this workbook.

You may refer to another example in this workbook.