Tags: FIND

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


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.

Generate a list of all tabs names without using VBA


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.

Split data into multiple tabs


Assume a four column database with department as the second column.  Entries in the department column may be repeated.  A person may wish to create one worksheet tab for each department.  Therefore, as and when a person names a worksheet tab as one of the departments, all rows of that department should appear on this new sheet.

You may refer to my solution in this workbook.

You may also refer to a similar article at the following link

Extract numeric data and dates from string


In column A, there are some alphanumeric entries.  Somewhere in the alphanumeric entries (beginning, middle or end) there are the following:

1. Dates
2. Numbers (without decimals)
3. Numbers (with decimals)

Furthermore, there may or may not be spaces between numbers/dates and text entries.  In Excel 2010 and prior versions, the only way to solve this problem would be write a formula or a VBA code.  In Excel 2013, one can solve this problem by using the "Flash Fill" feature.  Flash Fill is identifies patterns in your sample output data which you type (for a few entries) and suggests the output for the remaining cells of the range.

You may refer to my solution in this workbook.