Excel/formulas

Advertisement


Question
QUESTION: Hi Tom

i need some help
hope the holiday has been good and safe!!

i have a range A1:A10 with text, they have formulas to return the contains of a certain cell or leave blank....i am having a moment cause i cant seem to get the code right.

if cell a5 is the only cell that returns the contains of the specific cell, A5 returns Lone star housing, then i want
A10 to show that result.  only one cell in that given range is ever going to show up.  so if a5 shows something the others are all blank....

can u help me out?

Tony

ANSWER: Tony,

Based on what you describe, I would use this:

A10:  =Trim(Concatenate(A1,A2,A3,A4,A5,A6,A7,A8,A9))

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: that worked fine Tom,

have another question -

i have workbook with macro to enter password to go to another worksheet.  The first code places asterisk while typing in the password, which is what i want.  On this sheet there is nothing on the view code page its all blank...This code entermaster is as follows: and works like i want it to....

Private Sub entermaster()

   Application.ScreenUpdating = False


   Select Case InputBox("Please enter your password", _
       "Enter Password")
   
      Case Is = Sheet271.Range("W1").Value
         With Worksheets("Customer")
         .Visible = xlSheetVisible
         .Activate
         .Range("A1").Select
         
 
    .Range("II6").Select
         
   Sheets("00").Select
   
   On Error Resume Next
   ActiveSheet.Protect Password:="noah2000"
         
   ActiveWindow.SelectedSheets.Visible = False
   Worksheets("00").Visible = xlSheetVeryHidden
 
         
         End With
       Case Else
         MsgBox "Sorry, password is incorrect or AUF is not paid", _
         vbCritical + vbOKOnly, "You are not authorized!"
   End Select
         
   Application.ScreenUpdating = True
   
End Sub


=========================


this code "entercode" is what copied but just changed the workseets but when i type in the password it shows the letters while i am typing entering them, instead of showing asterisks.  I cant for the life of me figure out why....This is the code i used....

Private Sub entercode()

   Application.ScreenUpdating = False


   Select Case InputBox("Please enter your password", _
       "Enter Password")
   
      Case Is = Sheet19.Range("D1").Value
         With Worksheets("Code")
         .Visible = xlSheetVisible
         .Activate
         .Range("D3").Select
         
 
    .Range("II6").Select
         
   Sheets("Customer").Select
   ActiveWindow.SelectedSheets.Visible = False
         
   ActiveWindow.DisplayHeadings = False
         
       End With
       Case Else
         MsgBox "Incorrect Password!", _
         vbCritical + vbOKOnly, "Unauthorized!"
        
   Application.ScreenUpdating = True
   
End Select
End Sub

suggestions?

Thanks Tom!!!

Answer
Tony,

the VBA inputbox which you show in your code does not have the ability to display an asterisk instead of displaying the character that the user typed.  

If you are seeing such behavior, I can't explain it.  I ran your code and it did not put asterisks in the inputbox.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.