Two Quick Function Questions

  • Thread starter Thread starter Chris Guimbellot
  • Start date Start date
C

Chris Guimbellot

Hello,

Excel 2000.

Question 1. I would like to place the text of the name of a given worksheet
at various places in that worksheet so that if I change the name of the
worksheet, those cells will change as well. Is this possible?

Question 2. I have a list of items that I have named as a range on one
sheet. The list is about 20 items (rows), each with about 10 elements
(columns). For each one of the items, I have created a separate worksheet,
with the name of the worksheet corresponding to the name in the first column
of the list. What I would like to do on each worksheet is copy that item's
row from the list to the worksheet. In that, each worksheet would have a row
of data at the top that would be a copy if that item's elements from the
list on the other sheet. I have managed to do this using the VLookup
function to get the first column of information from the other sheet. The
problem is that I cannot drag that formula across the other columns to get
the rest of the item's elements. When I try, it just keeps repeating the
value from the first cell. I was able to manually adjust the formula for
each column, but that is a pain. Here is how I have the formulas set up now:

A1 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,1)
A2 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,2)
A3 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,3)
etc

First of all, is this the correct formula to be using? Second, is there
another formula that would in essence give me the same information while
letting me drag the formula across the adjacent columns? Thanks,

Chris
 
Hi
see below

Question 1. I would like to place the text of the name of a given worksheet
at various places in that worksheet so that if I change the name of the
worksheet, those cells will change as well. Is this possible?
Click to expand...

Use the formula
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Note: the workbook has to be saved at least once!


[snip]
A1 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,1)
A2 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,2)
A3 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,3)
etc
Click to expand...

Not quite sure how your data should be transfered but try
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,ROW())
if you want to drag the formula down

or
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,COLUMN())
if you want to drag the formula to the right


Frank
 
Frank,

Thanks for the help. I didn't know about the COLUMN() formula. Also, it
seems like the formula to find the worksheet name is a workaround taking the
entire path to a cell and pulling of the fill and cell information, leaving
just the worksheet name. Is that right? If it is, then that's a great way to
get it. I just figured Excel would have a built-in formula to get it. I
appreciate the help. Thanks again,

Chris

Frank Kabel said:
Hi
see below

Question 1. I would like to place the text of the name of a given worksheet
at various places in that worksheet so that if I change the name of the
worksheet, those cells will change as well. Is this possible?
Click to expand...

Use the formula
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Note: the workbook has to be saved at least once!


[snip]
A1 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,1)
A2 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,2)
A3 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,3)
etc
Click to expand...

Not quite sure how your data should be transfered but try
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,ROW())
if you want to drag the formula down

or
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,COLUMN())
if you want to drag the formula to the right


Frank
Click to expand...
 
Hi

Yes this is correct. The parameter "filename" in the cELL function
returns the entire path + workbook name. The formula just parses this
value

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Guimbellot said:
Frank,

Thanks for the help. I didn't know about the COLUMN() formula. Also, it
seems like the formula to find the worksheet name is a workaround taking the
entire path to a cell and pulling of the fill and cell information, leaving
just the worksheet name. Is that right? If it is, then that's a great way to
get it. I just figured Excel would have a built-in formula to get it. I
appreciate the help. Thanks again,

Chris

Frank Kabel said:
Hi
see below

Question 1. I would like to place the text of the name of a given worksheet
at various places in that worksheet so that if I change the name
Click to expand...
of
the
worksheet, those cells will change as well. Is this possible?
Click to expand...

Use the formula
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Note: the workbook has to be saved at least once!


[snip]
A1 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,1)
A2 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,2)
A3 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,3)
etc
Click to expand...

Not quite sure how your data should be transfered but try
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,ROW())
if you want to drag the formula down

or
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,COLUMN())
if you want to drag the formula to the right


Frank
Click to expand...
Click to expand...
 
Cool. Thanks, I really appreciate it.

Chris

Frank Kabel said:
Hi

Yes this is correct. The parameter "filename" in the cELL function
returns the entire path + workbook name. The formula just parses this
value

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Guimbellot said:
Frank,

Thanks for the help. I didn't know about the COLUMN() formula. Also, it
seems like the formula to find the worksheet name is a workaround taking the
entire path to a cell and pulling of the fill and cell information, leaving
just the worksheet name. Is that right? If it is, then that's a great way to
get it. I just figured Excel would have a built-in formula to get it. I
appreciate the help. Thanks again,

Chris

Frank Kabel said:
Hi
see below


Question 1. I would like to place the text of the name of a given
worksheet
at various places in that worksheet so that if I change the name of
the
worksheet, those cells will change as well. Is this possible?

Use the formula
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

Note: the workbook has to be saved at least once!


[snip]

A1 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,1)
A2 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,2)
A3 - =VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,3)
etc


Not quite sure how your data should be transfered but try
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,ROW())
if you want to drag the formula down

or
=VLOOKUP($A1,'In-House Web Analysis.xls'!Pages,COLUMN())
if you want to drag the formula to the right


Frank
Click to expand...
Click to expand...
Click to expand...
 
Back
Top