Re: Excel - Urgent - Magic Cells!

Hi,

If say cell A1 = text XYZ, is it possible for a set text in eg, B1:C1
to appear? Or if A1 = ABC, for B1:C10 to contain a different set o
text.? Or would a macro have to take the user automatically to 
different sheet?

Really appreciate any help.

Thanks

Andre

--
Message posted from http://www.ExcelForum.com

0
5/5/2004 5:00:13 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
364 Views

Similar Articles

[PageSpeed] 51

Yeah, it's possible.  But you're going to have to give some more details.

How does A1 change (calculation or typing)?
Where do the values for B1:C10 come from for both ABC and XYZ?

And what happens when I put QRS in A1--empty B1:C10???

"ajw150 <" wrote:
> 
> Hi,
> 
> If say cell A1 = text XYZ, is it possible for a set text in eg, B1:C10
> to appear? Or if A1 = ABC, for B1:C10 to contain a different set of
> text.? Or would a macro have to take the user automatically to a
> different sheet?
> 
> Really appreciate any help.
> 
> Thanks
> 
> Andrew
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/6/2004 1:25:25 AM
OK, thanks.

Here goes:

A1 could be the result of a data validation, - so a calculation.

The data for what would go into B1:C10, i.e XYZ and ABC, could coul
from set locations, i.e D1:E10 or F1:G10.

And if a different string went into A1, then yes, either blank or erro
message should come up.

Hope I have answered it.

Thank

--
Message posted from http://www.ExcelForum.com

0
5/6/2004 4:06:19 PM
Result of data validation means either typing or selecting from a list to
me--not a calculation (like in a formula).

Anyway, right click on the worksheet tab that should have this behavior.  Select
View code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myFromRng As Range
    Dim myToRng As Range
        
        
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
    
    On Error GoTo errHandler:
    Application.EnableEvents = False

    Select Case LCase(Target.Value)
        Case Is = "xyz": Set myFromRng = Me.Range("d1:e10")
        Case Is = "abc": Set myFromRng = Me.Range("F1:G10")
        Case Else
            Set myFromRng = Nothing
    End Select
    
    Set myToRng = Me.Range("B1:c10")
        
    If myFromRng Is Nothing Then
        Target.ClearContents 'remove the change to A1!
        myToRng.ClearContents
        MsgBox "Wrong response"
    Else
        myToRng.Value = myFromRng.Value
    End If
            
errHandler:
    Application.EnableEvents = True

End Sub

But if you're using xl97, there's a problem with worksheet_change events not
firing when using data|validation.

From Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html

      In Excel 97, selecting an item from a Data Validation dropdown list
      does not trigger a Change event, unless the list items have been typed in
      the Data Validation dialog box. In this version, you can add a button to
      the worksheet, and run the code by clicking the button. To see an
      example, go to the Sample Worksheets page, and under the Filters
      heading, find Product List by Category, and download the
      ProductsList97.xls file.

"ajw150 <" wrote:
> 
> OK, thanks.
> 
> Here goes:
> 
> A1 could be the result of a data validation, - so a calculation.
> 
> The data for what would go into B1:C10, i.e XYZ and ABC, could could
> from set locations, i.e D1:E10 or F1:G10.
> 
> And if a different string went into A1, then yes, either blank or error
> message should come up.
> 
> Hope I have answered it.
> 
> Thanks
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/7/2004 12:02:21 AM
Thanks Dave,

I was wondering if this can be taken one step further. 

Is it possible to replicate the original formating of the selection
i.e in D1:E10? 

Also, if in D1:E10 for example, there are Data Validation Cells, i
there a way of making them work in the new selection, B1:C10 too?

Thank

--
Message posted from http://www.ExcelForum.com

0
5/8/2004 5:03:41 PM
You could copy|paste the range.

Change this portion:
        myToRng.Value = myFromRng.Value
to:
        myFromRng.Copy _
            Destination:=myToRng

Since you're using Data|Validation, you probably don't have to worry about
formulas adjusting when you paste.

And you may want to change this line:
        myToRng.ClearContents
to 
        myToRng.Clear

(but since you're validating A1, it might not be necessary.)


"ajw150 <" wrote:
> 
> Thanks Dave,
> 
> I was wondering if this can be taken one step further.
> 
> Is it possible to replicate the original formating of the selection,
> i.e in D1:E10?
> 
> Also, if in D1:E10 for example, there are Data Validation Cells, is
> there a way of making them work in the new selection, B1:C10 too?
> 
> Thanks
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/8/2004 7:48:32 PM
Many thanks Dave. Excellent stuff. One last thing, it is operating i
Lower Case at the moment, and wont work in anything else. Is there an
way to alter this.

Thanks again.

Andrew

:) ;

--
Message posted from http://www.ExcelForum.com

0
5/9/2004 9:31:56 AM
Are you sure?

This portion of the code:

    Select Case LCase(Target.Value)
        Case Is = "xyz": Set myFromRng = Me.Range("d1:e10")
        Case Is = "abc": Set myFromRng = Me.Range("F1:G10")
        Case Else
            Set myFromRng = Nothing
    End Select

uses the lowercase version of the value just for comparisons with the "case is
=" lines.  It should work for both upper/lower/mixed case.  (Any chance you
changed that "select case" line???)

"ajw150 <" wrote:
> 
> Many thanks Dave. Excellent stuff. One last thing, it is operating in
> Lower Case at the moment, and wont work in anything else. Is there any
> way to alter this.
> 
> Thanks again.
> 
> Andrew
> 
> :) ;)
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/9/2004 11:31:15 AM
Hi,

Thanks for that. Its the "xyz" or "abc" part, that I would like i
mixed case if possible?

Also - one final thing - if A1 werent a data validation but an =to cel
or a typed in cell, is there a way of making the activate / procedur
work?

Thanks

Andre

--
Message posted from http://www.ExcelForum.com

0
5/10/2004 5:23:45 PM
It should already work for mixed case.

This portion:

    Select Case LCase(Target.Value)
        Case Is = "xyz": Set myFromRng = Me.Range("d1:e10")
        Case Is = "abc": Set myFromRng = Me.Range("F1:G10")

Says to take whatever is in that cell and use the lowercase version for the
comparison.

lcase("AbC") = "abc"
lcase("ABC") = "abc"
lcase("abC") = "abc"
lcase("abc") = "abc"

So it wouldn't matter how you type in the value into the cell.

There's another worksheet event that can fire each time the worksheet
recalculates.  It's Worksheet_calculation.

If you keep track of the previous value in the cell, then you can get out of the
subprocedure if the worksheet was changed as the result of a different
calculation:

Option Explicit
Dim OldA1Value As Variant
Private Sub Worksheet_Calculate()

    Dim myFromRng As Range
    Dim myToRng As Range
    
    Dim myCell As Range
    
    Set myCell = Me.Range("a1")
    
    If LCase(OldA1Value) = LCase(myCell.Value) Then
        'do nothing
        Exit Sub
    Else
        OldA1Value = myCell.Value
    End If
    
    On Error GoTo errHandler:
    Application.EnableEvents = False

    Select Case LCase(myCell.Value)
        Case Is = "xyz": Set myFromRng = Me.Range("d1:e10")
        Case Is = "abc": Set myFromRng = Me.Range("F1:G10")
        Case Else
            Set myFromRng = Nothing
    End Select
    
    Set myToRng = Me.Range("B1:c10")
        
    If myFromRng Is Nothing Then
        myCell.ClearContents 'remove the change to A1!
        myToRng.ClearContents
        'MsgBox "Wrong response"
    Else
        myFromRng.Copy _
            Destination:=myToRng
    End If
            
errHandler:
    Application.EnableEvents = True

End Sub



"ajw150 <" wrote:
> 
> Hi,
> 
> Thanks for that. Its the "xyz" or "abc" part, that I would like in
> mixed case if possible?
> 
> Also - one final thing - if A1 werent a data validation but an =to cell
> or a typed in cell, is there a way of making the activate / procedure
> work?
> 
> Thanks
> 
> Andrew
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/10/2004 10:52:12 PM
Reply:

Similar Artilces:

How do I make excel hide columns when it says cannot move objects
I select columns and try to hide them. Excel says it cannot shift objects off the sheet. I try goto objects and it says there are no objects on the sheet. I cannot hide or resize the column widths of certain columns. I cannot figure out why I can't get it to work and hide or resize the columns. Does anyone have any idea how to fix the columns so I can hide/resize the columns? It only does it on some of the columns, not all of them. I can't find any objects to delete. ...

Text & Date same cell
Hi All, Sheet1 has date in A1 (1/1/2005) formated as Date. Sheet2: '=IF(Sheet1!A1>1,"MyDate" & Sheet1!A1) <- formated as Date and '="MyDate"&IF(Sheet1!A1>1,Sheet1!A1) <-formated as Date Both display -> MyDate38353 How can i make it display -> MyDate1/1/2005 Any direction -- Desert Piranh ----------------------------------------------------------------------- Desert Piranha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=49296 =&q...

Re-arranging table using pivot-table?
I have a worksheet with data organized somewhat like this: New York New York New York Albany Florida Miami Florida Orlando Florida Tampa Florida Jacksonville California Los Angeles California San Diego I need to convert this to a list of states and each city in the columns to the right of their corresponding state. Something like this: New York New York Albany Florida Miami Orlando Tampa Jacksonville California Los Angeles San Diego Any help is greatly appreci...

Saving files when closing Excel
Excel does not prompt for saving changes when closing a file after changes to the file have been done. Hi have you checked that there's no event macro in your file? -- Regards Frank Kabel Frankfurt, Germany Jonathan Gold wrote: > Excel does not prompt for saving changes when closing a file after > changes to the file have been done. ...

can you convert an excel document to a powerpoint doc,
i have an organizational chart in excel and need to convert it to powerpoint. Is this possible. "dolphin" <dolphin@discussions.microsoft.com> wrote in message news:99472059-A5CE-4C59-B5CD-779DEA1A9D4F@microsoft.com... > i have an organizational chart in excel and need to convert it to > powerpoint. > Is this possible. Tried Insert-Object? ...

Generating Charts in Excel
I am trying to general a line chart that counts the number of items in a range (A23:A160), but will depend on the dates in range (C:23:C160). Basically, I want the display on which date the data in range (A23:A160) was at it's highest peak? -- TWarren ...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

Excel 2007 Line Chart
Hello, Is it possible to configure a line chart in Excel 2007 to ignore the intervals and graph straight to the next value. For example if I have the periods: Jan with the value 1000 Feb with the value 900 March with the value 500 April with the value 0 May with the value 0 June with the value 0 I want the March value to drop directly from 500 to 0 ignoring the interval to April, I do not want a curved line it must drop directly to zero then the line is straight across to April. I have no idea if this is possible, any ideas? Thanks, Brett On Tue, 11 Oct 2011 15:40:45 -0700 (PDT), TyreDu...

Excel Headers
Can I set up more than one header / footer in excel. I don't want the header to appear on all pages, just a few select ones. And on some pages, I want different headers, or footers. Regards, JH -- Posted via a free Usenet account from http://www.teranews.com You could change the headers, print the page, change the headers, print the page, .... Ron de Bruin has some sample code that may get you started (if you want to try using a macro): http://www.rondebruin.nl/print.htm#Header Solace wrote: > > Can I set up more than one header / footer in excel. > I don't want the ...

Excel Icon for files
I am running Windows 2000 professional on my PC and connected to a LAN. I am running the MS office 2000 Professional suite. When, I save a worksheet from Exel 2000 it does not save the file with the common Excel file Icon(green X). The file is displayed with the default Windows Flag Icon. When I double click on this default Windows Flag Icon the system tries to open the file with Word not Excel. Word and PowerPoint both save with their common Icons. The Excel program saved fine a couple of weeks ago. I have not installed any new software and our server takes care of Virus protection thro...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

formatting excel
I am trying to make score sheets for a dart league I play in, and I would like to find out if I can rotate the entire setup of excel so that the rows are parallel with the long side of the page. Is this possible? I think you want Copy, Paste special, Transpose. Gilles "pikeylover" <pikeylover@discussions.microsoft.com> wrote in message news:8E5D7479-B56D-47A9-951D-7DE9869B2A51@microsoft.com... >I am trying to make score sheets for a dart league I play in, and I would > like to find out if I can rotate the entire setup of excel so that the > rows > are parallel...

Word table to excel
Hi Guys Is it possible to copy a word table into excel. I have a folder with circa 200 files that I need to import into Excel. I have got the code to access word but am struggling to copy the tables. Cheers I found some macro in my archieves, but not sure if I ever got them fully running. You are welcomed to try them Sub Test() Set ExSht = ActiveSheet ' FName = "c:\temp\abc.doc" WordWasRunning = True On Error Resume Next Set WDApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Set WDApp = CreateObject("Word.Application&...

excel.exe has generated errors #2
Hello, Sorry for the cross post. I am receiving the following message: ""Excel.exe has generated errors and will be closed by Windows.You will need to restart the program An error log is being created."" when trying to open xls files that have been copied from another computer. Both Excel versions are the same and the file works on the source machine. I am copying it to and from a floppy which also works when opened on the source machine. Any ideas will be greatly appreciated. Thanks in advance. -- Mrich -- Mrich Have you copied the file from the floppy to your hard...

Printing labels from excel #3
Can I print labels from excel? If I can is it possible to merge address I have on a word doc to excel? I have 16 pages of labels that I want to put into excel so that I can use the filter and send out to just a certain city and so on. I also want to have the addresses in excel so I can sort. Can anyone help me? They need to be in a specific layout. What is their layout now, Linda? See if this helps: http://www.gmayor.com/convert_labels_into_mail_merge.htm ************ Anne Troy VBA Project Manager www.OfficeArticles.com "Linda" <Linda@discussions.microsoft.com> wrote in m...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Vlookups to Excel Version 3 files.
I use a system that on a daily basis creates an Excel version 3 file containing cost centers and their budgets. When I do vlookups from other Excel 97 files to this file it takes a long time for the sheet to calculate. If I save the Excel V3 file as Excel 97 its OK, and if the Excel V3 file is open before the 97 file its OK. I don't want to have to constantly save the V3 file as 97 (I may not be around to do that always) Does anyone know how to resolve this. Thanks Naz ...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

Calendar in Excel #2
Can someone help me with a calendar to use in Excel that can be formatted so as to show special dates, current holidays, etc. WARNING: I need explicit instructions. This sort of stuff is not intuitive to me. Thanks, Dot Hi Dot, Why not do your calendar in Outlook. Select which style you want....day/week/month. Then go to new input important dates. Save. Print "Dot Appleman" wrote: > Can someone help me with a calendar to use in Excel that > can be formatted so as to show special dates, current > holidays, etc. WARNING: I need explicit instructions. This > sor...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...

Excel Answer Wizard BROKEN
call this a flame if you want... for Years, I have tried to use the Excel Answer Wizzer, with extremely limited success. I have never found any source for suggestions about HOW to use it (including Excel Help) - for instance, I wantto UNLINK a workbook - can't find the answer anywhere. In the past I have found this usually means it can't be done, but GOD FORBID misero-soft tell me that. Have a great day. PS: return flames welcome at : nicboston@juno.com Hi Mark In the newer version you can use Edit>Links But I think you love this Add-in Try this Add-in to find the links ...

re re re ????
nefertiti ??? ...

Re:
...