Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 14th 10, 09:28 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 7
Default Excel cell address from cell contents

After years with Quattro Pro, I'm diving into Excel 2007. One conversion
problem involves INDIRECT as a substitute for QPro's @@ function.

Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet

In SUMMARY, cell A4 has the value 1989, and
cell A41 is a string from A4's value (QPro) or just a value reference (=a4)
in Excel

In B41 I want the value in cell C18 of sheet 1989

In Qpro I have:
@@(cell("contents",$A41)&":$c$18")
In Excel I have:
=INDIRECT(CELL("contents",$A41)&"!"&"$c$18)

Question: Is this the most efficient way in Excel to get the results I want
in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have
B41 read c18 of sheet 1989.

I hope I have been clear.

Also, following this same example, suppose the source cell I want is not
always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same
name, and then use it in the formula put in B17 of the SUMMARY? I don't think
so.

Thanks for any advice, as I use this sort of reference in a lot of my
workbooks.


  #2   Report Post  
Old January 14th 10, 11:07 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Excel cell address from cell contents

Maybe...

=indirect("'"&a41&"'!c18")

And yes, you can use a formula like:

='1989'!myCell
='1990'!myCell

I named the cell on each sheet "myCell"
Insert|Names|define (in xl2003 menus)
Names in Workbook: '1990'!myCell
refers to: ='1990'!$A$1

You'll want to make sure that you use a local/worksheet level name (not
global/workbook level).




TQuestar wrote:

After years with Quattro Pro, I'm diving into Excel 2007. One conversion
problem involves INDIRECT as a substitute for QPro's @@ function.

Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet

In SUMMARY, cell A4 has the value 1989, and
cell A41 is a string from A4's value (QPro) or just a value reference (=a4)
in Excel

In B41 I want the value in cell C18 of sheet 1989

In Qpro I have:
@@(cell("contents",$A41)&":$c$18")
In Excel I have:
=INDIRECT(CELL("contents",$A41)&"!"&"$c$18)

Question: Is this the most efficient way in Excel to get the results I want
in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have
B41 read c18 of sheet 1989.

I hope I have been clear.

Also, following this same example, suppose the source cell I want is not
always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same
name, and then use it in the formula put in B17 of the SUMMARY? I don't think
so.

Thanks for any advice, as I use this sort of reference in a lot of my
workbooks.


--

Dave Peterson
  #3   Report Post  
Old January 14th 10, 11:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Excel cell address from cell contents

You'll want to make sure that you use a local/worksheet level name (not
global/workbook level).

And to do that, you specify it in the "Names in Workbook" textbox.

Dave Peterson wrote:

Maybe...

=indirect("'"&a41&"'!c18")

And yes, you can use a formula like:

='1989'!myCell
='1990'!myCell

I named the cell on each sheet "myCell"
Insert|Names|define (in xl2003 menus)
Names in Workbook: '1990'!myCell
refers to: ='1990'!$A$1

You'll want to make sure that you use a local/worksheet level name (not
global/workbook level).

TQuestar wrote:

After years with Quattro Pro, I'm diving into Excel 2007. One conversion
problem involves INDIRECT as a substitute for QPro's @@ function.

Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet

In SUMMARY, cell A4 has the value 1989, and
cell A41 is a string from A4's value (QPro) or just a value reference (=a4)
in Excel

In B41 I want the value in cell C18 of sheet 1989

In Qpro I have:
@@(cell("contents",$A41)&":$c$18")
In Excel I have:
=INDIRECT(CELL("contents",$A41)&"!"&"$c$18)

Question: Is this the most efficient way in Excel to get the results I want
in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have
B41 read c18 of sheet 1989.

I hope I have been clear.

Also, following this same example, suppose the source cell I want is not
always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same
name, and then use it in the formula put in B17 of the SUMMARY? I don't think
so.

Thanks for any advice, as I use this sort of reference in a lot of my
workbooks.


--

Dave Peterson


--

Dave Peterson
  #4   Report Post  
Old January 15th 10, 04:03 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 7
Default Excel cell address from cell contents

Thanks for your quick response, Dave.


"Dave Peterson" wrote:

Maybe...

=indirect("'"&a41&"'!c18")


It appears that the single quotes can be eliminated, leaving this:
=indirect(""&a41&"!c18")

This works, but I'm unclear why the outer (i.e. 1st and last) quotes are
required. This is certainly an improvement on my method! Thanks.



And yes, you can use a formula like:

='1989'!myCell
='1990'!myCell

I named the cell on each sheet "myCell"
Insert|Names|define (in xl2003 menus)
Names in Workbook: '1990'!myCell
refers to: ='1990'!$A$1

You'll want to make sure that you use a local/worksheet level name (not
global/workbook level).


Perfect. Thanks for the clear explanation of the local vs global name use. I
was only aware of global names before reading this.

Finally, a minor question. All the workbooks that I have exported from
Quattro Pro x4 into xls files lack grid lines when opened in Excel 2007, and
when I go View Show/Hide Gridlines, the gridline box is checked. I can
save the file in Excel into a newly named xls or xlsx file, but still no
gridlines. Do you know a workaround?

TQ
PS-I really appreciate your help, and though I'm going to be away for a few
days, you may be sure that I will read any response with interest and thanks.



TQuestar wrote:

After years with Quattro Pro, I'm diving into Excel 2007. One conversion
problem involves INDIRECT as a substitute for QPro's @@ function.

Suppose a workbook with a SUMMARY sheet (tab) and a 1989 sheet

In SUMMARY, cell A4 has the value 1989, and
cell A41 is a string from A4's value (QPro) or just a value reference (=a4)
in Excel

In B41 I want the value in cell C18 of sheet 1989

In Qpro I have:
@@(cell("contents",$A41)&":$c$18")
In Excel I have:
=INDIRECT(CELL("contents",$A41)&"!"&"$c$18)

Question: Is this the most efficient way in Excel to get the results I want
in B41? I want to be able to enter any sheet name (e.g. 1989) in A4, and have
B41 read c18 of sheet 1989.

I hope I have been clear.

Also, following this same example, suppose the source cell I want is not
always c18. Can I name a cell in each sheet (1989, 1990 ...) with the same
name, and then use it in the formula put in B17 of the SUMMARY? I don't think
so.

Thanks for any advice, as I use this sort of reference in a lot of my
workbooks.


--

Dave Peterson
.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return cell address of a cell based on contents of cell. Danny Excel Worksheet Functions 4 November 15th 08 03:11 AM
Comparing cell contents, know cell address molsansk Excel Discussion (Misc queries) 4 December 10th 07 04:44 PM
linking to contents of a cell vs cell address AinSF Excel Worksheet Functions 0 September 6th 06 06:57 PM
Insert Cell Contents in middle of web address Jetheat Excel Discussion (Misc queries) 2 March 5th 06 07:18 PM
How do I use cell contents as an address in a formula tomeck Excel Worksheet Functions 1 December 20th 05 05:46 PM


All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017