If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I pull Cell value from Detail worksheet into Summary sheet?
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... |
#2
|
|||
|
|||
How can I pull Cell value from Detail worksheet into Summarysheet?
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.... |
#3
|
|||
|
|||
How can I pull Cell value from Detail worksheet into Summary sheet?
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... "Justin Case" wrote in message ... 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.... |
Thread Tools | |
Display Modes | |
|
|