Unhiding rows from another worksheet.

  • Thread starter Thread starter heartbreakkid
  • Start date Start date
H

heartbreakkid

I have a worksheet for Permanent items and worksheet for temoporar
items. I also have another worksheet which includes info from both th
above worksheets. In the third worksheet i have a table of permanen
items at the top followed by a table of temporary items. After the las
item, i have hidden a few rows so i can add any extra temp items i hav
later on. the problem is, since everything in the 3rd worksheet i
refernced fom the other wprksheet, i wanted to know if there is any wa
that when i add a temporary item in a temp worksheet, it unhides th
row in the the third worksheet and adds the info that i just put in th
temp work sheet
 
Hi heartbreakkid


I would suggest placing all the Permanent and Temporary items on the
one Worksheet. Simple have an extra Column headed "Permanent/Temporary"
Then base a Pivot Table (or 2) off this with the "Permanent/Temporary"
field in the Page area of the PT.

This will save yourself a lot of work and heartache in the long run.

If you are not familiar with PT start here;

http://www.ozgrid.com/Excel/excel-pivot-tables.htm

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
If you're hiding the rows in your third (Main) WS because there's more data
or information following the end of your Temp items list, this will *not*
work.

If, on the other hand, empty rows are not objectionable, you could revise
the type of link you're using between the Temp and Main sheets to become
*automatically* expandable to include *inserted or added* rows of Temp
items.

This type of formula creates links dependant on *rows* on the Temp sheet.
Inserting a row to the Temp sheet, while not actually adding a row to the
Main sheet, will move *all* the existing data down on the Main sheet, to
accommodate the additional row(s) in the Temp sheet.

For example,
Sheet3 = Main Sheet
Sheet2 = Temp Sheet
Temp Data starts in A3 of Sheet2
Link on Main Sheet to Temp Data list starts in A50.

A *normal* link on Sheet3, in A50 would look like:
=Sheet2!$A3
And copy down.

Try this formula in A50:
=INDIRECT("Sheet2!$A"&ROW()-47)

However, when there is no data in the cell on Sheet2 that this formula links
to, it returns a zero (0).
To eliminate the zero and leave an empty appearing cell, try this formula:
=IF(INDIRECT("Sheet2!$A"&ROW()-47)>0,INDIRECT("Sheet2!$A"&ROW()-47),"")

And copy down.

Now, whatever you do in the Temp sheet, that's covered by this formula in
the Main sheet, will be duplicated.
 
Back
Top