Excel Tips - Getting List from Other Spreadsheet for Display with Data Validation List

You can create your own pull-down list or combo box by using the data validation feature in Excel but the main problem is the list has to be in the same spreadsheet as the combo box that you want to use. What most of us will do is just create a list somewhere else in the same spreadsheet and then hide it either by hiding the columns or rows of the list or change the font colour to white so it is invisible.

You can actually make use of the range name for the list that is in other sheet tabs and there is no need to copy it to the spreadsheet that you want to put the combo box.

Here's how...

1. Select the list that you want to use (it could also be from the table array you use for VLookup or HLookup. If it is, just select the list excluding the header which is the column one for VLookup table array or row one for HLookup table array). It is best if you can first sort your list in ascending order because the list taken in data validation will follow your actual list without sorting them for you.
   
2. With the list selected, click in the name box located to the left of the formula bar and type a name (the name has to be unique and shouldn't be separated if it consists of more than one word) and then press Enter.




3. Return to the other spreadsheet and select the cell(s) you want to create combo box.
4. With the cell(s) selected, go to Data tab and select Data Validation from Data Tools group (for Excel 2003 or earlier, go to Data menu and select Validation...).
   



5. On the Data Validation dialog box, go to Settings tab.
6. Select List from Allow combo box.
   
   
   
7. Type the name you have given for the list in step 2, starting with the equal sign (=) in the Source field (I used FruitList for my example).




8. Click OK or you can continue setting the Input Message and/or Error Alert tab if you want to.
   
   

You can combine the usage of this combo box with any lookup function or use it alone.

- MRBAR

Excel Tips - Hide Formula from Being Displayed in the Formula Bar

You may want to hide the formula that you are using in your Excel from being seen by other viewers who are using your spreadsheet.

To hide the formula, you will have to protect your worksheet but before protecting, you'll have to set the cell's format.

1. Select the cell(s) with the formula(s) you want to hide.
2. From Home tab, click Format from Cells group and select Format Cells (for earlier Excel versions, go to Format menu and select Cells).
   
   
   
   
3. From the Format Cells dialog box, go to the Protection tab.
4. Select the Hidden check box (leave the Locked check box selected to protect from user changing or deleting the cell content).
   
   
   
   
5. Click OK.

(If you want to allow entering or editing data on certain cells, select those cells and follow step 2 and 3. Then uncheck the Locked check box and click OK.)

Now you can protect your worksheet.

1. From Home tab, click Format from Cells group and select Protect Sheet... or you can also find Protect Sheet button in the Changes group from Review tab (for earlier versions, go to Tools menu, point at Protection and select Protect Sheet...).
2. Apply your password to protect (optional) and repeat the same password after clicking OK.

REMINDER! Password is case sensitive. To unprotect, you must type the password exactly like the one you've created. If you forgot the password then you can say good bye to your worksheet :).

- MRBAR

Excel Tips – Protect Certain Part of Spreadsheet from Changes

Sometimes we want to block users from making any changes to our important data area but elsewhere within the same worksheet they can make any changes they wish for. By default when we set the protect worksheet, the entire worksheet is being protected. How do we actually make the setting, so that only selected area within the same worksheet cannot be modified?

1. Click on the top left of the worksheet or press Ctrl+A to Select All
   
   
   
   
2. Press Ctrl+1 to open up the Format Cells dialog box
3. Click on the Protection tab
4. Clear the Locked check box
5. Click OK
   
   
   
   
6. Highlight the area in the data area.
   
   
   
   
7. Open the Format Cells dialog box again
8. This time around, check the Locked check box
9. Click OK
   
   
   
   
10. Next step is to protect the worksheet
11. Click on the Review tab, Changes group, Protect Sheet command button (for MS Excel 2003 – Click on the Tools menu, Protection, Protect Sheet)
    
    
    
    
12. Type a password to unprotect sheet
13. Reenter the password
    
    
    
    
14. Try to make changes on the selected data area
15. The message box below will come out
    
    
    

Now all is being set, users are not allowed to make any changes on the selected data area.

- Siti Suriani

Excel Tips – Copying or Cutting Formula

When you copy and paste formulas containing relative references, the references are adjusted.

Microsoft Office Excel 2003

1. Select cell with formula
2. Click Copy on the Edit menu
3. Select cell to paste
4. Click Paste on the Edit menu
   

Microsoft Office Excel 2007

1. Click Home tab
2. Click Copy command button under Clipboard group
3. Select cell to paste
4. Click Paste command button under Clipboard group
   
   
   

When you cut and paste formulas containing relative references, the references are not adjusted.

Microsoft Office Excel 2003

1. Select cell with formula
2. Click Cut on the Edit menu
3. Select cell to paste
4. Click Paste on the Edit menu
   

Microsoft Office Excel 2007

1. Click Home tab
2. Click Cut command button under Clipboard group
3. Select cell to paste
4. Click Paste command button under Clipboard group
   
   
   
   

- Siti Suriani

Excel Tips - If Colored areas on your worksheet do not print well

Microsoft Office Excel 2003

1. Click Page Setup on the File menu
2. Click the Sheet tab
3. Select the Black and White check box under Print category
   

Microsoft Office Excel 2007

1. Click Page Layout tab
2. Click Page Setup dialog box launcher button
3. Click the Sheet tab
4. Select the Black and White box under Print category
   
   

- Siti Suriani