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...