How can I pull Cell value from Detail worksheet into Summary sheet?

[John Kotuby]

Hi all,

I am inexperienced in using Excel but have been assigned a task which
requires designing a Workbook containing formulas. Our little company cannot
afford a consultant for this job.

I have some limited experience with using formulas to obtain values derived
from Cells on the same Sheet.

However, this workbook contains the first Worksheet as a Summary sheet with
line items of products ordered. At the end of each line item I wish to get
the Subtotal value for that product ordered. That Subtotal value resides in
a specific cell (always the same location) on the corrresponding Detail
worksheet.

Line items on the first Worksheet (Summary) relate to their corresponding
Detail worksheet in the following way:

The Line Items on the first sheet in the Workbook (Sheet1) always start on
Row 21, and additional line items follow on consecutive rows.
I can derive the Detail Worksheet number (index?) by using the formula
Row()-19, which for Row 21 resolves to 2. That is the correct sheet number
of the corresponding Detail worksheet. For example the first line item
relates to Sheet2. The 2nd line item refers to Sheet3, etc.

However, the Detail worksheets have been renamed to reflect the Product
name. The Product name resides on the Summary sheet in the first Cell in the
line-item row I am working with, but it is in a HyperLink.

Is there a way to get the value from the Detail sheet without using VBA
macros. I tried using a simple Macro (I do some progamming in VB) and when I
went to re-open the workbook, Excel 2003 disabled the Macro, because Macro
security was set to High. These workbooks will be going out to customers. I
have no control over how they set their Macro security. I just need whatever
Formula I use to work properly in Excel 2003 and 2007 without raising a
security risk.

I have been researching this for days and the answers I have found always
involve VBA macros. I am hoping that Excel has some combination of native
functions that I can use to get at a value on another worksheet.

I have programmed some complex modules before in Transact SQL and ASP.NET,
but this "simple" task in Excel 2003 is making me crazy!

Thanks for any help...

 

[Justin Case]

Hi John,

Can you clarify this...

"The Product name resides on the Summary sheet in the first Cell in
the
line-item row I am working with, but it is in a HyperLink. "

If the sheet names correspond directly a cell entry (i.e. the
hyperlink says "Cogs" and the sheet is named exactly the same) you can
use the INDIRECT function to get information from the detail sheets.
The formula would look something like this:

=INDIRECT(A3&"!A4")

If A3 contains the hyperlink COGS then the formula will return the
contents of cell A4 on the corresponding Detail sheet.

Hope I've interpreted the above sentence correctly....

 

[John Kotuby]

Justin,
Thanks for your response...
Yes you understood correctly.
The product name (from which the Sheet name is derived) is in column A of
the line-item.
Apparently even if it is part of a Hyperlink, the displayed text will be
returned according to what I understand.

That gets me much closer to a solution!

I found that in some cases there were very long product names that produced
duplicate Sheet names when the names were truncated at 31 characters. Since
duplicate Sheet names are not allowed I prefixed the Sheetname whith the
Line Item number and then performed Left(strSheetName, 31) to truncate the
name to 31 characters.

An example (just for discussion)....

Product 1: Maple Syrup Produced in Southern Vermont
Product 2: Maple Syrup Produced in Southern Maine

If these Product names were trimmed to 31 characters they would both be
"Maple Syrup Produced in Souther"

So (from within the VB application that creates the initial Workbook) I
created worksheet names like this.
Below the variable strName will hold the fianl worksheet name and
strProductName contains "Maple Syrup Produced in Southern Vermont"

strWSName = "(1) " + strProductName

strWSName = Left(strWSName, 31)

So it becomes a bit more complicated, but we are getting there.

Thanks again...