data sheet to indie worksheets

  • Thread starter Thread starter tmi!tmi!
  • Start date Start date
T

tmi!tmi!

I have a data worksheet that has 45 rows of server info, ie server name
serial number, OS rev, h/w type, etc.

Is there a way to generate 45 SEPERATE worksheets from this dat
worksheet? I will be linking these 45 worksheets to a Visio diagra
and posting the Visio diagram to my company website.

Any changes made to the data worksheet should show up on th
corresponding worksheets. I've created a few of the worksheets, and a
able to link worksheet cells to the data worksheet cells, but a
wondering if there is a easier way to do this.

Thanks in advanc
 
Yes;

You can group sheets by pressing CTRL and or/ SHIFT and clicking on sheets.

You can then enter a formula in one, and the formula will be entered in all.

Now you want to somehow make each formula dependent on the sheet number, so
that you can differentiate rows.

Say your original sheet contains the data from
A1:C45

Sheet 2, you'd want to pull from A1:C1;
sheet 3 you'd want to pull from A2:C2, etc.

Select sheet2 through sheet46, and enter this formula in A1. Then copy it
over through C1.

=INDIRECT("sheet1!"&CHAR(96+COLUMN())&--MID(MID(CELL("filename",$A$1),FIND("
]",CELL("filename",$A$1))+1,31),FIND("t",MID(CELL("filename",$A$1),FIND("]",
CELL("filename",$A$1))+1,31))+1,999)-1)
 
As a test, I copied the header and 3 rows of server info to a ne
spreadsheet. Sheet1 has the header/server info and sheet2, sheet3, an
sheet4 should hold the results of the formula.

I copied the formula

=INDIRECT("sheet1!"&CHAR(96+COLUMN())&--MID(MID(CELL("filename",$A$1),FIND("
]",CELL("filename",$A$1))+1,31),FIND("t",MID(CELL("filename",$A$1),FIND("]",
CELL("filename",$A$1))+1,31))+1,999)-1)


per instructions. I get a #VALUE in the cells where the formula ha
been pasted in the sheets where I want the info.


what did I do wrong? BTW, I'm running XP Pro and using Exce
2002(sp2)
 
If you provide your email address, I'll email you a sample of what worked;;
but I can't tell what you did wrong.


tmi!tmi! > said:
As a test, I copied the header and 3 rows of server info to a new
spreadsheet. Sheet1 has the header/server info and sheet2, sheet3, and
sheet4 should hold the results of the formula.

I copied the formula

=INDIRECT("sheet1!"&CHAR(96+COLUMN())&--MID(MID(CELL("filename",$A$1),FIND("
]",CELL("filename",$A$1))+1,31),FIND("t",MID(CELL("filename",$A$1),FIND("]
Click to expand...
",
CELL("filename",$A$1))+1,31))+1,999)-1)


per instructions. I get a #VALUE in the cells where the formula has
been pasted in the sheets where I want the info.


what did I do wrong? BTW, I'm running XP Pro and using Excel
2002(sp2).
Click to expand...
 
Actually, one thing is that the formula wasn't designed for a header. If you
want a header, select all sheets, and enter
=Sheet1!A1 in A1;
and copy over for as many columns as you have labeled with a header.
Then enter the big formula in column A of row 2 (or row 9999, it will be the
same).



Dave R. said:
If you provide your email address, I'll email you a sample of what worked;;
but I can't tell what you did wrong.


tmi!tmi! > said:
As a test, I copied the header and 3 rows of server info to a new
spreadsheet. Sheet1 has the header/server info and sheet2, sheet3, and
sheet4 should hold the results of the formula.

I copied the formula
Click to expand...
=INDIRECT("sheet1!"&CHAR(96+COLUMN())&--MID(MID(CELL("filename",$A$1),FIND("
]",CELL("filename",$A$1))+1,31),FIND("t",MID(CELL("filename",$A$1),FIND("]
",
CELL("filename",$A$1))+1,31))+1,999)-1)


per instructions. I get a #VALUE in the cells where the formula has
been pasted in the sheets where I want the info.


what did I do wrong? BTW, I'm running XP Pro and using Excel
2002(sp2).
Click to expand...
Click to expand...
 
Back
Top