Tags: NAMED RANGES

Create all possible combinations from different ranges without using VBA

{13 Comments}

Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10.  The objective is to create all possible combinations in column E from these two ranges without using VBA (macros).  This combined range of all possible combinations is called a Cartesian product.

You may refer to the following steps to create all possible combinations in column E

1. Type a heading in cell B2, say Data Set1.  Format cell B2 to Bold (Ctrl+B).
2. Type a heading in cell C2, say Data Set2.  Format cell C2 to Bold (Ctrl+B).
3. Select range B2:B10, press Ctrl+F3 > New and in the name box, type Data_set1
4. Select range C2:C10, press Ctrl+F3 > New and in the name box, type Data_set2
5. Select range B2:B10 and press Ctrl+T > OK
6. Select range C2:C10 and press Ctrl+T > OK
7. Save the file at your desired location, say on your desktop
8. Select cell E2 and go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
9. In the Select Workbook dialog box, navigate to the folder and select the file which you just saved on the desktop (in step 7 above) and click on OK
10. With Data_set1 selected, click on the > symbol
11. Select Data_set2 and click on the > symbol
12. Click on Next > OK
13. Go to File > Return Data to Microsoft Excel
14. In the Import Data box, select cell E2 in the Existing Worksheet box and click on OK.  Just in case, you do not see the Import Data box, after step 12, press Alt+Tab.
15. You should now see your desired result in range E2:F66.

Furthermore, since you had converted the ranges in column B and column C to Tables individually, if you add additional rows of data to column B and/or column C, just refresh while you are clicked on any cell of the result range.  This will update the table in the result area.

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.

Calculate a unique count with conditions in a Pivot Table

{2 Comments}

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week - November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week is about 8,400 rows.

There are three questions to be answered from this data:

1. The Circle wise, count of ID's which went faulty more than twice between November 26, 2012 and December 2, 2012; and
2. The Circle wise, count of faulty instances more than twice between November 26, 2012 and December 2, 2012; and
3. Determine individual sites for 1 and 2 above

The difference between 1 and 2 above is "If a certain ID goes down 4 times, then for question1, the answer should be 1.  For question2, the answer should be 4."

The first question basically boils down to "Count of unique ID's by Circle which went faulty more than twice."

There are two ways one can go about answering the questions above:

Solution A - For Excel 2010 and higher versions - This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.

Solution B - For all versions of MS Excel - This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.

The steps for creating a pivot table under Solution B for answering both questions above are:

1.  Count of downtime sites.xlsx is saved in a folder on the desktop;
2. Open the workbook, select the data on the Base_Data sheet (including the first row as the header row – it will be range A1:C8741.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select range A1:C8741 of the Base_Data sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the “My Table has headers” box is checked.  Save the workbook.
5. Open a new worksheet and go to Data > From Other Sources > From Microsoft Query
6. Under Databases, select Excel files > OK
7. In the Directories dialog box, navigate to the folder on the desktop where the workbook file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where the workbook is saved.
8. In the left hand side window, select the Count of downtime sites.xlsx file and click on OK
9. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box 10. Click on Next three times
11. Select the option of View Data or Edit Query in Microsoft Query
12. Click on the SQL button, delete the contents in the white space there and paste the following SQL Query

SELECT ucase(dummy.Circle) AS 'Circle', ucase(dummy.Indus_Site_ID) AS 'Indus_site_ID', Count(dummy.Indus_Site_ID) AS 'fault_frequency'
FROM `C:\Users\Ashish\Desktop\Count of downtime sites.xlsx`.dummy dummy
GROUP BY ucase(dummy.Circle), ucase(dummy.Indus_Site_ID)
HAVING (Count(dummy.Indus_Site_ID)>2)

13. Click on OK and on the message box which appears, click on OK
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data in a tabular form, then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  For this example, select Pivot Table and in the cell reference box, select any cell where you would like to the result to appear, say cell A1.  Click on OK
16. A counter will run at the bottom left hand side with the title of Reading Data
17. Drag Circle and ID to the to the Row Labels
18. Drag Fault Frequency to the Value Area twice
19. Right click on any one number in the fault frequency column and under Summarise Value by, select Count
20. Right click on any value in the ID column and under Expand/Collapse, select Collapse Entire Field.

Create a Pivot Table from multiple worksheets in different workbooks

{33 Comments}

Assume two workbooks named Book1.xls and Book2.xls.  In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb.  In both the worksheets - Jan and Feb, the following exist

1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets

As you can observe in zipped file, all three aspects mentioned above are in place.  One may want to perform any of the following tasks:

1. Create one pivot table from both worksheets (which are in different workbooks); and/or
2. Consolidate data from both worksheets (which are in different workbooks) one below the other

To curtail the workbook size, I have deleted rows of data.  In each workbook, there were 65,000 rows of data on each Jan worksheet and Feb worksheet.

While one simple way would be to copy and paste data from Jan and Feb worksheet in a third worksheet and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple workbooks into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original workbooks will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well.  So this leads to duplication of effort.

Both problems outlined above can be resolved by using MS Query.  The technique mentioned below will work as long as the three conditions mentioned above are satisfied.  Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets (Jan and Feb) should not exceed 65,536.  The combined rows in both worksheets could be any number.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

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 order for the Power Query solution to work, all files will have to be saved as CSV files in the same folder (Since a CSV files can have only one worksheet, the Jan and Feb worksheets in the other workbook will have to become two workbooks - Jan.csv and Feb.csv).  In Power Query, there is a feature to append data from multiple CSV files lying in a specific folder into one running range.  Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.

Solution for all versions of MS Excel

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Both workbooks - Book1.xls and Book2.xls are saved on the desktop.
2. Open Book1.xls, select the data on the Jan sheet (including the first row as the header row - on the Jan sheet, it will be A1:S4.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the "My Table has headers" box is checked.  Save and close Book1.xks
5. Repeat steps 2 - 4 for the Feb sheet on Book2.xls as well.  In step 3, just change the name to Dummy1.  Save and close Book2.xls
6. Open a new workbook and go to Data > From Other Sources > From Microsoft Query
7. Under Databases, select Excel files > OK
8. In the Directories dialog box, navigate to the folder on the desktop where the Book1.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Book1.xls file is saved
9. In the left hand side window, select the Book1.xls file and click on OK
10. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
11. Click on Next three times
12. Select the option of Return Data to Microsoft Excel
13. At this stage, if you wish to get data in a Table form then select Table.  If you wish to create a pivot table, select the second option button - Pivot Table.  Select any cell where you would like to the result to appear, say cell A1.
14. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
15. If you had selected pivot table in step 13 above, then the pivot table grid/layout will appear
16. You may now drag fields to create a pivot table

So the Table or pivot table which you have created so far is only from Jan sheet of Book1.xls.  We have to also include data from Feb sheet of Book2.xls.

17. If you had created a Table in step 13 above, then click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you had created a Pivot Table in step 13 above, then select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.
18. In the Command text box, you see something like this

SELECT dummy.Invoice_number, dummy.Date, dummy.`Article Number`, dummy.`Article Name`, dummy.`Tax Type`, dummy.Brand, dummy.Category, dummy.`Sports Code`, dummy.`Product Type`, dummy.MRP, dummy.`Customer #`, dummy.`Customer Name`, dummy.Region, dummy.`Area Code`, dummy.Quantity, dummy.`Line Discount`, dummy.`Sales Value`, dummy.`Before Tax`, dummy.`VAT Amount` FROM `C:\Users\Ashish\Desktop\Book1.xls`.dummy dummy

Replace this lengthy statement with the following shorter one

SELECT * from `C:\Users\Ashish\Desktop\Book1.xls`.dummy
Union all
SELECT * from `C:\Users\Ashish\Desktop\Book2.xls`.dummy1

19. Click on OK and the Table/Pivot Table should update
20. Save the file as Consolidated.xls and close it.

Update Table/Pivot Table for addition/editing in existing named ranges

To see the effect of a dynamic Table/pivot table, edit data in Jan and/or Feb sheets of Book1.xls and/or Book2.xls.  Save and close the file(s).  Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh).  At the bottom right had side the counter will run again and (step 14 above) and once it has read all data, the Table/pivot table will update.

Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now want to add data from a worksheet titled March in Book3.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Book3.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy3

3. Save and close the workbook

4. Open Consolidated.xls

5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 13 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:

Union all
SELECT * from `C:\Users\Ashish\Desktop\Book3.xls`.dummy3

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.

Create a Pivot Table from multiple worksheets in the same workbook

{65 Comments}

Assume a workbook with two worksheets - Jan and Feb.  In both the worksheets, the following exist

1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets

As you can observe in this workbook, all three aspects mentioned above are in place.  One may want to perform any of the following tasks:

1. Create one pivot table from both worksheets; and/or
2. Consolidate data from both worksheets (one below the other)

To curtail the workbook size, I have deleted rows of data.  In the actual workbook, there were 65,000 rows of data on each worksheet.

While one simple way would be to copy and paste data from both worksheets into one and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple worksheets into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original sheets will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well.  So this leads to duplication of effort.

Both problems outlined above can be resolved by using MS Query.  The technique mentioned below will work as long as the three conditions mentioned above are satisfied.  Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets should not exceed 65,536.  The combined rows in both worksheets could be any number.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem.

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 worksheets into one running range.  Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.

Solution for all versions of MS Excel

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Suppose the workbook with the two worksheets (Jan and Feb) is named Monthly data.xls and is saved on the desktop.
2. Select the data on the Jan sheet (including the first row as the header row - on the Jan sheet, it will be A1:S4.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the "My Table has headers" box is checked.
5. Repeat steps 2 - 4 for the Feb sheet as well.  In step 3, just change the name to Dummy1
6. Save the workbook as Consolidated.xls (save as Excel workbook if the combined rows in Dummy and Dummy1 exceed 65,536 rows) and close it
7. Open a new workbook (Ctrl+N) and go to Data > From Other Sources > From Microsoft Query
8. Under Databases, select Excel files > OK
9. In the Directories dialog box, navigate to the folder on the desktop where the Monthly Data.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Monthly Data.xls file is saved
10. In the left hand side window, select the Monthly Data file and click on OK
11. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
12. Select Dummy1 and click on the > symbol to bring over all columns of this named range to the right hand side box
13. Click on Next and on the message box which appears, click on OK
14. Click on the SQL button and delete all content from the SQL Statement box
15. Type this SQL statement in the box there

Select * from dummy
union all
Select * from dummy1

16. Click on OK and on the message box which appears, click on OK
17. Under File, select the last option - Return Data to Microsoft Excel
18. At this stage, if you wish to get data from Dummy and Dummy1, one below the other, then select Table.  If you directly want a pivot table, select the second option button - Pivot Table.  Select any cell where you would like to the result to appear, say cell A1.
19. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
20. If you had selected pivot table in step 18 above, then the pivot table grid/layout will appear.
21. You may now drag fields to create a pivot table
22. Close the workbook

Update Table/Pivot Table for addition/editing in existing named ranges

To see the effect of a dynamic Table/Pivot Table, edit data in Jan and/or Feb sheets of Monthly data.xls.  Save and close the file.  Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh).  At the bottom right had side the counter will run again and (step 19 above) and once it has read all data, the Table/Pivot Table will update.

Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the Table/Pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now add a new worksheet, titled March in Monthly data.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Monthly data.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy2

3. Save and close the workbook

4. Open Consolidated.xls

5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 18 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:

union all
Select * from dummy2

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.

Update Table/Pivot Table when files are mailed to someone else

If you now mail both files - Monthly Data.xls and Consolidated.xls to someone else, that other person will not be able to refresh the pivot table because the path specified in step 9 above would be different for that other person.  To reestablish the path, follow the under mentioned steps:

1. Open Consolidated.xls

2. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 18 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

3. Click on the Definition tab and under Connection String, edit the following:

a. From DBQ= till the next ;, change the file path to the new path where the mail recipient has saved the file.  So, on my system, between DBQ= and the next ;, the path is C:\Users\Ashish\Desktop\Jan.xls.  This will change to C:\Users\John\Desktop\Data\Jan.xls

b. From DefaultDir= till next ;, change the folder path to the new path where the mail recipient has saved the file.  So, on my system, between DefaultDir= and the next ;, the path is C:\Users\Ashish\Desktop\.  This will change to C:\Users\John\Desktop\Data\

Removing dependent validation list from cell for one case

{4 Comments}

In cell B6, a user can select one of the following entries from a validation drop down list - Reason1, Reason2 and Reason3.  If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6.  However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should vanish thereby allowing the user to enter any value in cell C6.

You may refer to my solution in the this workbook.

Display text entries in the data area of a pivot table

{13 Comments}

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names in column A.

The task is to reorient this database to show all part numbers in column A and parts supplied by those vendors should appear in different columns of that row.  If one attempts to create a pivot table, the problem would be with the data area of the pivot table.  When one drags the part codes (alphanumeric string) column to the data area, count of part number would appear instead of the actual part number.  This is the very nature of a pivot table.  Since it is a summarisation tool, one can never see text entries in the data area of a pivot table.

You may download the workbook showing both solutions (using MS Query and Power Query) from here.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (minimal formulas) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  You may refer to Solution2 - Power Query worksheet.

Solution for all versions of MS Excel

You may refer to Solution1 - MS Query worksheet.  To reorient the data i.e. to view text entries in the data area of a pivot table, try the following approach:

1. In cell C1, type Count.  So the value in cell A1 is Vendor, in cell B1 is Part_code and in cell C1 is Count
2. In cell C2, enter =$B$1&" "&COUNTIF(A$2:A2,A2) and copy down.  Since the number of rows here are quite many, i would suggest copying in batches i.e. first copy down 5,000 rows, then another 5,000 rows and so on.  This is solely to prevent Excel from crashing and/or taking unduly long to process.
3. Select range A1:C20237 and press Ctrl+F3 > New.  In the name box, enter Dummy
4. Select range A1:C20237 and press Ctrl+T to convert to a Table.  This is to take care of data being added beyond row 20237.
5. Save the file and open a new worksheet (in the same workbook)
6. While on any cell in this new worksheet, go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
7. Navigate to the folder where you saved the file in step 5 above, select the file and click on OK
8. With Dummy selected by default, press the > symbol.  This will show you three column headings in the right hand side box
9. Click on Next three times
10. Select the second option button and click on Finish
11. Click on the SQL button and delete all contents that you see there.  Type the following in the blank white space

TRANSFORM first(Part_code)
SELECT Vendor
FROM dummy
GROUP BY Vendor
PIVOT count

12. Click on OK
13. Go to Data > Return Data to MS Excel
14. In the Import Data box, select Table and in Existing sheet, select cell A1
15. Click on Finish

Data should now appear in the desired format.

For any changes to data in range A2:B20237, right-click on any cell in the output range and select Refresh.  Furthermore, since the range has been converted to a Table, you may even add further rows of data and simply right-click to Refresh the output data range.

To improve performance of your workbook, you may want to copy the formulas in column C and paste them as Values (Paste Special > Values).  Once again, do not paste special all values at once - do so in groups of 5,000 rows to prevent Excel from crashing and/or taking unduly long to process.

Secondary validation cell entry to update when primary validation cell changes

{22 Comments}

Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list).  Instead the secondary validation cell remains the same as what was already selected earlier.

In the this workbook, i present the following three cases of dealing with the secondary validation cell value when the primary validation cell changes:

1. The moment the primary validation entry changes, blank out the secondary validation cell
2. The moment the primary validation entry changes, show the corresponding first entry of the secondary validation drop down
3. The moment the primary validation entry changes, perform the following:
a. If there is only one corresponding entry in the secondary validation drop down, then show that entry (no need to select); and
b. If there are multiple corresponding entries in the secondary validation drop down, then blank out the secondary validation cell

Conditional data validation

{6 Comments}

Assume that in range A2:A11, cells have been validated to allow a person to enter Y or N.  In B2:B11, cells should be validated based on the entry selected by the end-user in range A2:A11.  So, if a user selected Y in cell A2, then a drop down should appear in cell B2, allowing him to choose from Delhi, Mumbai, Chennai and Kolkata.  If the user selects N in cell A2, he should be able to enter any value in cell B2.

To accomplish this, try the following procedure

1. Type Delhi, Mumbai, Chennai and Kolkata in I2:I5 and assign it a name (Ctrl+F3), say source1

2. In A2, select Y

3. In cell B2, go to Data > Validation > Allow > List > Source > =IF(A2="Y",source1,B2)

With Y in cell A2, B2 will show four cities in the validation drop down.  When the value in cell A2 changes to N, the user will be able to enter any value in cell B2.