Mirroring data from one sheet to the next
Posted by Chris Jones on May 18, 2001 9:40 AM
I need to know how to automatically have data from one sheet of a workbook appear on another sheet in the same workbook based on whether a column contains a value.
Posted by Tuc on May 18, 2001 9:59 AM
Re: Use a formula and an IF statement
Chris,
I'll answer the question in two parts: First you can set up a formula to "point" to the appropriate cells on the source worksheet. This would be in the format of "=Sheet1!$A$26" being the the formula contained in the cell located on Sheet2.
Second you can combine this with an IF statement using the ISBLANK function. Take the formula and expand it to read "=IF(ISBLANK(Sheet1!$A$26), "", Sheet1!$A$26)". This would evaluate the cell A26 located on sheet1. ISBLANK would return a true or false value that would be evaluated by the IF statement. If evaluated TRUE then the empthy string, "", would be returned. If ISBLANK returned FALSE, then the value contained in the Sheet1!$A$26 cell would be returned.
Does this answer your question?
Tuc
Posted by Dave Hawley on May 18, 2001 10:03 AM
Hi Chris
There are a number of ways to do this.
1. Select one sheet name tab, then holding down the Ctrl select the other. Now whatever you do to one sheet will be on the other.
2. On the second sheet in cell A1 put:
=IF(Sheet1!A1<>"",Sheet1!A1,"") and drag it down and across.
The other method is via VBA code placed in the Worksheet Module. to get there right click on the Sheet name tab and select "View Code", paste in this code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
On Error Resume Next
Sheets("Sheet1").Range(Target.Address) = Target
End Sub
Dave
OzGrid Business Applications
Posted by Chris Jones on May 22, 2001 1:26 PM
How do I create a drop down box
I want a spreadsheet to contain a dropbox with 3 different options that are accessible by pointing and clicking on it with a mouse.
Does anyone know how to do this?
Posted by Tuc on May 22, 2001 6:39 PM
Re: How do I create a drop down box
John Green describes this in his book, Excel 2000 VBA Programmer's Reference (WROX Press ISBN 1-861002-54-8) P 177. He uses the DropDown object which is a hidden member of the Excel object model. You can down load the sample file Controls.xls from the Wrox Press web site and look at the code he provides. I highly recommend the book.
