Microsoft Excel Select a value from a predefined list of values with dropdown option to validate the data

Hi friends,

When I was preparing a sheet by referencing some pre-defined names as list to a field/cell. I was thinking the feature of Microsoft Excel dropdown list. After exploring the option and using, I thought of sharing with you all.

Objective: Selecting a cell value from a list of pre-defined values

Procedure:

1. Open blank excel workbook
2. Prepare you template with headers
3. Create a new sheet and rename it. Ex: "Names"
4. Enter the values from Column A from cell A1 in vertical direction
5. Don't use any other columns
6. Don't leave any empty cell in between the values
7. Leave first cell as empty, if you want to include the empty option in a dropdown
8. Select the cell which contains the values
9. Right click and choose the option "Define Name"
10. Give a name to the list of values. Ex: NamesList
11. Click on "OK" button
12. Now, move to the sheet where we want to use the list of names as a dropdown option
13. Select the cell(s), where we want to have the dropdown list
14. Click on "Data" Menu
15. From "Data Tools" ribbon, click "Data Validation" from "Data Validation" submenu
16. In Settings tab, Validation Criteria section, for the option Allow, select "List"
17. Choose the options
• Ignore blank: Ignore when user no selected any option
• In-cell Dropdown: Show the dropdown list in cell
18. In "Source" field, type the defined name for the list preceded with = (Equal to) symbol. Ex: =NamesList
19. Now, you should be able to see the dropdown with empty values
20. Select the predefined values from the dropdown list of names.

Please send your comments and feedback to psrdotcom@gmail.com

Add (append or prepend) text along with excel functions/formula in a single cell

Hi friends,

Today I have come across a situation where i want to have the cell to descriptive and to have summation of some cells. I have got information on how to do it. Please find below

Input Data:

3

4

2

I want a cell to be on top those with description and summation value

Expected output data:

Sum = 9

3

4

2

To get the output, I have written the following function

Syntax:
="Text"& function

Example:
="Sum = "& SUM(A2:A4)

We can append and prepend the text to excel function to combine text and numbers as well.

Please send your comments and feedback to psrdotcom@gmail.com

Cell Broadcast and Push Messages Blocking

Hi friends,

I have re-installed my OS and done the "Reset Factory Settings"

From that day onwards, I am continuously getting "CB Message" --> "Cell Broadcast" messages

To get rid of this spam kind of messages, please follow the below steps

1) Go to message settings (Open Inbox->Settings)
2) Uncheck the "CB activation" under "Cell Broadcast (CB) settings" section

You can block the push messages from network also, by doing the following
1) Go to message settings (Open Inbox->Settings)
2) Uncheck the "Push messages" under "Push message settings" section

Hope you found the above content useful.

Please send your feedback and comments to psrdotcom@gmail.com

File copying based on cell value to different directories on some condition

File copying based on cell value to different directories on some condition


Description:
I have lot files to copy into two different directories. I sorted those file names in a such a way that odd row numbered file should go to the directory named "Odd" and even row numbered file should go to the directory named "Even".

I am checking whether the file exists or not in the particular directory before copying. If exists it will ask the user for confirmation and stored with suffix1.

References:
http://msdn.microsoft.com/en-us/library/cahdzfxw%28v=vs.80%29.aspx
http://www.techotopia.com/index.php/Working_with_Directories_in_Visual_Basic
http://www.mvps.org/dmcritchie/excel/strings.htm