Referencing only certain cells in a table

Hello,
I posted this question earlier in the execl.worksheet.functions group,
but did not get an answer. I apologize for the repost.

I need some help referencing certain cells from one worksheet in
another. Here is an example of what I am trying to accomplish:

Worksheet 1:
Item #    Name     Value
1           item1      100
2           item2      0
3           item3      0
4           item4      54
5           item5      0
6           item6      0
7           item7      70

I need to create a separate table in Worksheet2 that as only the NON
ZERO values from the table above in Worksheet 1. So the table in
Worksheet 2 should look like:

Item#   Name   Value
1         item1    100
4         item4     54
7         item7     70

I am looking for a function or formula to do this automatically for the
user of the spreadsheet I am creating. Doing a filter, then copying,
and pasting is not what I need. I need the data to appear in Worksheet2
automatically for the user based on the contents of the table in
Worksheet1. How do I accomplish this? 

Thanks in advance for your help.

0
yofnik (16)
9/27/2006 7:35:49 PM
excel 39879 articles. 2 followers. Follow

4 Replies
397 Views

Similar Articles

[PageSpeed] 35

There are two possibilities.

1.  Use this formula in the cell on sheet 2:
=IF(Sheet1!$C1<>0,Sheet1!A1,"").  No copy and paste it to the other
cells.  The formula will adjust as it is pasted.  For example,
"Sheet1!A1" will change to "Sheet1!A2", and so on.

This will give you a matrix with blanks where 0 appeared.


2.  Write a VBA module to do the trick.  This is probably the better
approach, and the one I would take.  But you need to know VBA to get
the job done.

Dom




yofnik@comcast.net wrote:
> Hello,
> I posted this question earlier in the execl.worksheet.functions group,
> but did not get an answer. I apologize for the repost.
>
> I need some help referencing certain cells from one worksheet in
> another. Here is an example of what I am trying to accomplish:
>
> Worksheet 1:
> Item #    Name     Value
> 1           item1      100
> 2           item2      0
> 3           item3      0
> 4           item4      54
> 5           item5      0
> 6           item6      0
> 7           item7      70
>
> I need to create a separate table in Worksheet2 that as only the NON
> ZERO values from the table above in Worksheet 1. So the table in
> Worksheet 2 should look like:
>
> Item#   Name   Value
> 1         item1    100
> 4         item4     54
> 7         item7     70
>
> I am looking for a function or formula to do this automatically for the
> user of the spreadsheet I am creating. Doing a filter, then copying,
> and pasting is not what I need. I need the data to appear in Worksheet2
> automatically for the user based on the contents of the table in
> Worksheet1. How do I accomplish this?
> 
> Thanks in advance for your help.

0
9/27/2006 7:50:41 PM
The whole point of doing this is to remove the blanks where 0 appeared.
So I guess my only option is to do this in VBA. Time to roll up my
sleaves I guess. Has anyone done anything similar that they can share
to help get me started?


dolivastro@gmail.com wrote:
> There are two possibilities.
>
> 1.  Use this formula in the cell on sheet 2:
> =IF(Sheet1!$C1<>0,Sheet1!A1,"").  No copy and paste it to the other
> cells.  The formula will adjust as it is pasted.  For example,
> "Sheet1!A1" will change to "Sheet1!A2", and so on.
>
> This will give you a matrix with blanks where 0 appeared.
>
>
> 2.  Write a VBA module to do the trick.  This is probably the better
> approach, and the one I would take.  But you need to know VBA to get
> the job done.
>
> Dom
>
>
>
>
> yofnik@comcast.net wrote:
> > Hello,
> > I posted this question earlier in the execl.worksheet.functions group,
> > but did not get an answer. I apologize for the repost.
> >
> > I need some help referencing certain cells from one worksheet in
> > another. Here is an example of what I am trying to accomplish:
> >
> > Worksheet 1:
> > Item #    Name     Value
> > 1           item1      100
> > 2           item2      0
> > 3           item3      0
> > 4           item4      54
> > 5           item5      0
> > 6           item6      0
> > 7           item7      70
> >
> > I need to create a separate table in Worksheet2 that as only the NON
> > ZERO values from the table above in Worksheet 1. So the table in
> > Worksheet 2 should look like:
> >
> > Item#   Name   Value
> > 1         item1    100
> > 4         item4     54
> > 7         item7     70
> >
> > I am looking for a function or formula to do this automatically for the
> > user of the spreadsheet I am creating. Doing a filter, then copying,
> > and pasting is not what I need. I need the data to appear in Worksheet2
> > automatically for the user based on the contents of the table in
> > Worksheet1. How do I accomplish this?
> > 
> > Thanks in advance for your help.

0
yofnik (16)
9/27/2006 9:35:20 PM
This is a macro doing almost the same as what you did manually, doing a 
filter, then copy/paste etc.
copy the code below to standard module. after selecting your Worksheet 1, 
run myfilter, then this will create none zero value in Worksheet 2.
assuming item# in A1, Name in B1, Value in C1 and the name of Worksheet 2 is 
Sheet2.

Sub myfilter()
Dim srcsheet As Worksheet
Dim dstsheet As Worksheet
Dim n As Long
Const itemad = "a1" 'change - item# address
Const valad = "c1"  'change - value address
On Error Resume Next
Application.ScreenUpdating = False
Set srcsheet = ActiveSheet
Set dstsheet = Worksheets("Sheet2") 'change - Worksheet2
n = Range(valad).Column - Range(itemad).Column + 1
dstsheet.Cells.Clear
Range(valad).AutoFilter Field:=n, Criteria1:=">0", Operator:=xlAnd
srcsheet.AutoFilter.Range.Copy destination:=dstsheet.Cells(1, 1)
srcsheet.AutoFilterMode = False
Application.EnableEvents = True
End Sub

if you want to update table in Worksheet 2 automatically when you change 
values of table in Worksheet 1, copy the code below to Worksheet 1 
module(Sheet1?, not sure), not standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    myfilter
    Application.EnableEvents = True
End Sub

keizi

<yofnik@comcast.net> wrote in message 
news:1159392920.118209.90960@b28g2000cwb.googlegroups.com...
> The whole point of doing this is to remove the blanks where 0 appeared.
> So I guess my only option is to do this in VBA. Time to roll up my
> sleaves I guess. Has anyone done anything similar that they can share
> to help get me started?
>
>
> dolivastro@gmail.com wrote:
>> There are two possibilities.
>>
>> 1.  Use this formula in the cell on sheet 2:
>> =IF(Sheet1!$C1<>0,Sheet1!A1,"").  No copy and paste it to the other
>> cells.  The formula will adjust as it is pasted.  For example,
>> "Sheet1!A1" will change to "Sheet1!A2", and so on.
>>
>> This will give you a matrix with blanks where 0 appeared.
>>
>>
>> 2.  Write a VBA module to do the trick.  This is probably the better
>> approach, and the one I would take.  But you need to know VBA to get
>> the job done.
>>
>> Dom
>>
>>
>>
>>
>> yofnik@comcast.net wrote:
>> > Hello,
>> > I posted this question earlier in the execl.worksheet.functions group,
>> > but did not get an answer. I apologize for the repost.
>> >
>> > I need some help referencing certain cells from one worksheet in
>> > another. Here is an example of what I am trying to accomplish:
>> >
>> > Worksheet 1:
>> > Item #    Name     Value
>> > 1           item1      100
>> > 2           item2      0
>> > 3           item3      0
>> > 4           item4      54
>> > 5           item5      0
>> > 6           item6      0
>> > 7           item7      70
>> >
>> > I need to create a separate table in Worksheet2 that as only the NON
>> > ZERO values from the table above in Worksheet 1. So the table in
>> > Worksheet 2 should look like:
>> >
>> > Item#   Name   Value
>> > 1         item1    100
>> > 4         item4     54
>> > 7         item7     70
>> >
>> > I am looking for a function or formula to do this automatically for the
>> > user of the spreadsheet I am creating. Doing a filter, then copying,
>> > and pasting is not what I need. I need the data to appear in Worksheet2
>> > automatically for the user based on the contents of the table in
>> > Worksheet1. How do I accomplish this?
>> >
>> > Thanks in advance for your help.
> 

0
kounoike1 (60)
9/28/2006 7:11:31 AM
GREAT! Thank you so much.

kounoike wrote:
> This is a macro doing almost the same as what you did manually, doing a
> filter, then copy/paste etc.
> copy the code below to standard module. after selecting your Worksheet 1,
> run myfilter, then this will create none zero value in Worksheet 2.
> assuming item# in A1, Name in B1, Value in C1 and the name of Worksheet 2 is
> Sheet2.
>
> Sub myfilter()
> Dim srcsheet As Worksheet
> Dim dstsheet As Worksheet
> Dim n As Long
> Const itemad = "a1" 'change - item# address
> Const valad = "c1"  'change - value address
> On Error Resume Next
> Application.ScreenUpdating = False
> Set srcsheet = ActiveSheet
> Set dstsheet = Worksheets("Sheet2") 'change - Worksheet2
> n = Range(valad).Column - Range(itemad).Column + 1
> dstsheet.Cells.Clear
> Range(valad).AutoFilter Field:=n, Criteria1:=">0", Operator:=xlAnd
> srcsheet.AutoFilter.Range.Copy destination:=dstsheet.Cells(1, 1)
> srcsheet.AutoFilterMode = False
> Application.EnableEvents = True
> End Sub
>
> if you want to update table in Worksheet 2 automatically when you change
> values of table in Worksheet 1, copy the code below to Worksheet 1
> module(Sheet1?, not sure), not standard module.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Application.EnableEvents = False
>     myfilter
>     Application.EnableEvents = True
> End Sub
>
> keizi
>
> <yofnik@comcast.net> wrote in message
> news:1159392920.118209.90960@b28g2000cwb.googlegroups.com...
> > The whole point of doing this is to remove the blanks where 0 appeared.
> > So I guess my only option is to do this in VBA. Time to roll up my
> > sleaves I guess. Has anyone done anything similar that they can share
> > to help get me started?
> >
> >
> > dolivastro@gmail.com wrote:
> >> There are two possibilities.
> >>
> >> 1.  Use this formula in the cell on sheet 2:
> >> =IF(Sheet1!$C1<>0,Sheet1!A1,"").  No copy and paste it to the other
> >> cells.  The formula will adjust as it is pasted.  For example,
> >> "Sheet1!A1" will change to "Sheet1!A2", and so on.
> >>
> >> This will give you a matrix with blanks where 0 appeared.
> >>
> >>
> >> 2.  Write a VBA module to do the trick.  This is probably the better
> >> approach, and the one I would take.  But you need to know VBA to get
> >> the job done.
> >>
> >> Dom
> >>
> >>
> >>
> >>
> >> yofnik@comcast.net wrote:
> >> > Hello,
> >> > I posted this question earlier in the execl.worksheet.functions group,
> >> > but did not get an answer. I apologize for the repost.
> >> >
> >> > I need some help referencing certain cells from one worksheet in
> >> > another. Here is an example of what I am trying to accomplish:
> >> >
> >> > Worksheet 1:
> >> > Item #    Name     Value
> >> > 1           item1      100
> >> > 2           item2      0
> >> > 3           item3      0
> >> > 4           item4      54
> >> > 5           item5      0
> >> > 6           item6      0
> >> > 7           item7      70
> >> >
> >> > I need to create a separate table in Worksheet2 that as only the NON
> >> > ZERO values from the table above in Worksheet 1. So the table in
> >> > Worksheet 2 should look like:
> >> >
> >> > Item#   Name   Value
> >> > 1         item1    100
> >> > 4         item4     54
> >> > 7         item7     70
> >> >
> >> > I am looking for a function or formula to do this automatically for the
> >> > user of the spreadsheet I am creating. Doing a filter, then copying,
> >> > and pasting is not what I need. I need the data to appear in Worksheet2
> >> > automatically for the user based on the contents of the table in
> >> > Worksheet1. How do I accomplish this?
> >> >
> >> > Thanks in advance for your help.
> >

0
yofnik (16)
9/30/2006 10:36:42 PM
Reply:

Similar Artilces:

What is the best way to create a conversion table in Excel ?
I want to create a conversion table where at the prompt of a given measured value, Excel will return the value in a (host of) new measurement(s). Example: If for gas, i want to convert 1 cubic meter into kcal, I would enter 1, choose cubic meter as my initial measurement, and then easily read off a table what this equals in kcal. see this url interestsing martin a. <martin a.@discussions.microsoft.com> wrote in message news:693B27D8-E553-4699-8295-63450807D1A7@microsoft.com... > I want to create a conversion table where at the prompt of a given measured > value, Excel will re...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

How do I connect cell color to bar chart color?
I have seen an excel spread sheet bar chart where the colors of the bars were connected to the color of the cell. I was not able to determine how that was accomplished and the author of the spread sheet no longer works for the company that sent me the data. Does anyone know how this can be done? Hi Bob, It's possible this was done using a vba macro. For a non VBA approach have a look at Jon's examples of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Bob Freerks wrote: > I have seen an excel spread sheet bar chart where the color...

empty cells in ranges
I am building a macro which will populate a range but I need to know ho to goto the next empty cell in that range Any suggestions, good people -- UniDav ----------------------------------------------------------------------- UniDave's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1684 View this thread: http://www.excelforum.com/showthread.php?threadid=32049 Hi UniDave, Perhaps you could use something like: Sub Tester03() Dim rng As Range Dim rng1 As Range Dim rngArea As Range Dim rCell As Range Dim i As Long, j As Long Set rng = Ran...

Linking Formats Across Cells
anyone know if I can link formats across cells? For example, if I fill a cell with a color, can I link it to another cell that will do the same ? thanks. Try the FormatPainter, the little PaintBrush icon up in the tool bar...........click on your colored cell, then click on the icon, then click and drag on the cells you want to be the same........... Vaya con Dios, Chuck, CABGx3 "RB" <RB@discussions.microsoft.com> wrote in message news:FE8ABE47-2671-447C-8CC6-2D784EEECE00@microsoft.com... > anyone know if I can link formats across cells? For example, if I fill a cell ...

Starting a macro by editing a cell #2
I would like a macro to be initiated whenever someone edits a particula cell, does anyone now how this can be done? Many Thanks, Yianni -- Message posted from http://www.ExcelForum.com Try something like this in your Worksheet_Change or Worksheet_SelectionChange : If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("A1:A1"), Target) Is Nothing Then runMyMacro jeff >-----Original Message----- >I would like a macro to be initiated whenever someone edits a particular >cell, does anyone now how this can be done? > >Many Thanks, > >...

How do I create an excel macro to append to a cell with existing i
How do I create an excel macro to append data to a cell with existing data? example one row has rs1006 and I want it to read rs1006.jpg there are 5000 records in each one of my sheets is why I am looking for a macro. Zola: You can prolly do this, much more quickly: insert a column and type the following formula (change A1 to the cell in which your first product number resides): =a1&".jpg" Copy it down using the Fill Handle: http://www.officearticles.com/excel/best_tips_for_using_microsoft_excel.htm Leaving all the cells selected, hit Copy, then immediately hit Edit-->...

How to read values from a table?
Hi, I am trying to redesign an old database. I have three forms start, entry, edit forms. Start form is like a switch board to enter into the forms. An audit is entered into the database through "Entry" form which has around 50 controls. All these controls gets stored in the main table. "Edit" forms allows you to edit the audit. The issue here is the "Edit" form doesn't read the values from the main table when the form is loaded. I have a logic to read the values from the main table in "start" form. Everything looks fine with the SQL statemen...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

Pivot table of data in more than one worksheet
Hi I have data whose numbers of rows exceed one worksheet.. ie. there are around 100,000 rows.. so I have this data in 2 worksheets.. the data is in continuation.. How can I pivot the data of both worksheets in one pivot table??? Thanks Hi Rehan this can be achieved using multiple consolidation ranges for your pivot table - check out http://www.contextures.com/xlPivot08.html for details Cheers JulieD "Rehan" <anonymous@discussions.microsoft.com> wrote in message news:0abe01c53053$ca11cc30$a401280a@phx.gbl... > Hi > > I have data whose numbers of rows exce...

In macros, an instruction that returns the position of the Active Cell
In a macro, how can I make it return the position of the Active Cell Colum & Line. Or how can I set that position as a Condition; e.g. �If ActiveCell i B7 Then�.� Or �If ActiveCell belongs to Column G Then�.� Thanks in advance for any help. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.msusenet.com/member.php?userid=385 View this thread: http://www.msusenet.com/t-187368645 Hi Sub test() Dim ws1 As Worksheet Set ws1 = Workbooks("Tester.xls").Sheets("Sheet2") With ws1 MsgBox ("Cell address is &qu...

Pivot Tables
Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% ...

Pivot Table
I've a column in a pivottable as 'running total'. I want to add another column 'percent' (not from the data in the source) which will give me percentage of the 'running total' column. That is, for instance: running total percentage 12 34.3% 24 68.6% 35 100.0% Thanks. http://www.contextures.com/xlPivot10.html there are instructions for running total -- Regards, Peo Sjoblom (No private emails please) "Pivot Table" <...

Quaters In Pivot Tables
Pivot Tables default to the first Quarter of a year commencing January. Is it possible to have the first quarter commencing July, as per Finacial rather than Calender year? Are you achieving your Quarters by Grouping a date field from your data source? If so, I don't think you can group dates in any other way--I've asked this question myself and done web searches and didn't find a different way to group. This might solve your dilemma, though (you'll have to ditch the Grouping): Create a Calculated Item that sums all dates in the appropriate range. Basic Instructions fo...

Table Security Violation
We have a modified report called POP Purchase Order Blank Form. I'm able to print it out as sa, however, our purchasing manager is no longer able to use it. He gets the error "Table Security Violation". After trying all sorts of different combinations using Advanced Security, I finally just copied sa's security into our Purchasing Manager's account. Believe it or not, it still wouldn't work. We've reimported the report and didn't help either. I can't ease the security any more than I already have, what other options do I have? What am i missi...

Two different values in a single cell...
Hi, Is it possible to have two different values (coming from another sheet) in a single cell? For example, 0.87 (0.97) In that case 0.87 comes from a cell on sheet1 and 0.97 from a cell on sheet2. Thanks for the help. Patrick, Hi You can concatenate two results into one cell. This makes it inot a text cell, however, so you cannot do maths on it quite so easily. To join the info, just use the CONCATENATE function, or the & sign =A2&" " &A3 You may also need the TEXT funtion, depending on what format you want the result to be -- Andy. "Patrick" <an...

Database table relationships
In visio 2007 I can not connect the relationship heads and tails to tables - Any suggestions? Thanks ...

ADO Error Attempting to Link Table
Hi, I am attempting to link a table from an user-level security database to an unsecured databaes. The error I get is: Run Time Error -2147467259, Could not find installable ISAM. I have done much searching but I haven't come up with any solutions. Does anyone have any ideas? Thanks! Option Compare Database Dim adoCn As ADODB.Connection Dim adoCat As New ADOX.Catalog Dim adoTbl As New ADOX.Table Private Sub createlink_Click() 'Create Link.. Set adoCat = New ADOX.Catalog Set adoCat.ActiveConnection = adoCn Set adoTbl.ParentCatalog = adoCat adoTbl.Name = "LinkTable" adoTbl...

Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the 'Protect Sheet' option. When I do that, everything is protected. Any help much appreciated. Hi try the following: - first select all cells you don't want to protect - goto 'Format - Cells - Protection' and uncheck 'Locked' - now protect your sheet -- Regards Frank Kabel Frankfurt, Germany "Fred Exley" <fexly221@msn.com> schrieb im Newsbeitrag news:10hsd5t42ghdt65@corp.supernews.com... > The books I read seem to indicate so, but say you first must turn on the > &...

Append to MS SQL 2005 table
I'm getting a key violation error whenever I try and append records from a table in Access to a table on a MS SQL 2005 database. The SQL db's table has an IID field which is the primary key for the table (and the only indexed field in the table) and I am NOT trying to append records to the IID field with my INSERT INTO tImport(...) statement. It is a clustered index if that makes any difference. Since I can't seem to add the table and write to it without an index field, does anyone have any ideas why I am getting the error message Is the IID field an Identity/Autonumber t...

Using Pivot Table to display text values
I am trying to use an Excel Pivot Table to display the associated text data (there is only one value cell for each row and column combination). The data is currently displayed as zeros in the Pivot Table since it is trying to COUNT, rather than just display the text. Is there a way to turn off the Count function without using another formula such as SUM or AVERAGE? Text can't be displayed in the data area of a pivot table. You have to use one of the available summarize options (Count, Sum, etc.), but you could format the cells with white font, so the results aren't visible. ...

Pivot Table Not Refreshing Data From UDF
I have an Excel 2007 spreadsheet, which is hosted on a SharePoint 2007 server and accessed through the SharePoint Excel Web Access WebPart. On a 2nd worksheet, I have a range defined, which displays the results of a UDF that returns an object array. This portion functions correctly. On the 1st worksheet, I have a pivot table, which uses the named range from the 2nd worksheet as its data source. This doesn't work. All that the pivot table renders is a single line with no data. I have verified that the pivot table is configured to refresh upon opening the spreadsheet. Interesti...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

text in Bullets/points in cell
How can one write the text in bullets or points in the cell in excel 2007. Hold the ALT key, then on the NUMERIC KEYPAD, type 0149, then let go of the ALT key -- you'll have your bullet •<== works here, too! "kamal" wrote: > How can one write the text in bullets or points in the cell in excel 2007. ...

Cell Referencing #2
Hello All, If I have an excel spreadsheet open with two sheets. In Sheet1 in cell D2 is "Hello". In Sheet2 in cell F4 is the formula "=Sheet1!D2", so it also says "Hello". What I want is to have next to F4 in Sheet2 (in cell E4) a reference to which row in Sheet1 that the formula in F4 is referring to? Ie. Cell E4 should have the number 2 in it. Is this possible using the =INDIRECT function or something similar? With thanks Bruce Hi Bruce, use the formula =column(Sheet1!D2); Br, Dries. >-----Original Message----- >Hello All, > >If I...