Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Using a product that uses Excel for designing and creating financial reports. The basic process is that I have a worksheet named T-Income that at run-time is duplicated and renamed Income and processed. The various formulas on the Income worksheet are processed at this time with real values from a SQL Server Database. During design mode all of these functions have default values they return because there is no connection to the SQL Server DB during design mode. The duplication of the T-Income worksheet is done so that the program (An Excel Add-In) never does anything to the original template; T-Income.

    Here's the problem, when trying to reference the value on Income from another worksheet I must wrap those cell refs within the Indirect function else the end result of the cell ref is always #REf after the report is processed during run-time. This ref is changed to #Ref because the Excel Add-In will delete any existing worksheets named Income before it duplicates T-Income and renames it to Income. The use of the Indirect function allows for referencing cells on the Income worksheet and not get the #Ref problem.

    While the cross worksheet issues is solved via the use of Indirect() additional issues have come into play that would not otherwise be an issue. I'm speaking about Excel's default ability to automagically update cell refs when copying & pasting and using either relative only cell refs or a mix of relative & absolute cell refs. When placing a cell ref within Indirect the ref is always absolute. While this is the desired mode during run-time it kills the report creation process at design time. Imagine that I have a worksheet with 100 cells that each will reference a different cell on the worksheet Income. If I enter the Indirect function in the first cell I can not use the copy & paste feature in Excel and get Excel to automagically update the row & column refs. That sucks but what can I do?

    Solution 1: I decided that I could address this via a custom macro. The general logic would be for each cell in range set cell.formula = "Indirect(" + Cell.Formula + ")". I didn't test this simply because this would not always work. If a cell on this summary worksheet is referencing multiple cells on the Income worksheet like this:

    =Income!B10 + Income!C10

    then the macro would not work because each cell ref would need to be surrounded by the Indirect function. SO what I'm looking for is a way to have code search thru each cell ref within a cells formula and wrap that cell ref within the Indirect function. One additional thing to note is that all the cell refs will be relative prior to adding the Indirect function so there should not be an issue with how to strip out the '$' within the ref before wrapping it within the Indirect function.

    Any ideas?

    PS - If anyone has an idea of another way to deal with cross worksheet cell refs as I am doing that do not require the use of the Indirect function please speak up.

    Thanks
    Ed

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Wouldn't it be much easier to change the formulas to values before removing the worksheet?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    I'm sorry Jan, but I don't follow what your saying. I will tell you this and it may answer your question, I have no control over the process itself meaning I can't control how the reprt is created in YSL. I can only change the 'Template' (T-Income) that the final worksheet (Income) is based on. In other words I can't change how the Excel Add-In works.

    Ed

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    I see two options other than your indirect trick.

    1. Use a link to an external workbook with an income worksheet and after the processing, redirect the external link to the workbook itself.
    2. Have an extra worksheet in the template (not named income) and do a search and replace, replacing that worksheet's name with "income".

    I suspect #1 might be easiest.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Thanks for the tips Jan but neither will work. Thsi is because the solution used must be automated so that no user input is required during the processing. This is because ther are times when the rpeort is processed and saved automatically by a batch reporting tool. The only time a solution that inivolves direct user input can be used is when that user input is during the design mode of the report and not run-time. Thanks again though. Your tips have given me some good ideas on how to deal with some other Excel related issues seperate form this one and the Excel Add-In I speak of.

    Ed

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Sounds like this add-in has a bad design.
    Can your template workbook contain macros?
    If so, you could have a macro that only runs once do the trick.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Jan,
    The Workbook is allowed Macros but the problem there is when to trip the macro. In fact there is nothing preventing the inclusion of code modules of both regular and class modules. Again though the problem is how to call these macros or procedures when the report is being automatically processed? I can't call them until the Add-In is done and when the report is automatically processed the end result is a new Workbook that is based on the orginal after it has been processed. This unfoortunately results in missing customo code. When the new workbook is generated the way this is done does not allow for the copyiong over to the new workbook of any custom code in the workbook. And so if my workbook has macros they are not a part of the final workbook that is generated when the Batch Reporting product is used. Now when the user runs the report to screen custom macros are an option. Unfortunately it's not for me because whatever I do it must work under both scenarios.

    Thanks
    Ed

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    What happens to code behind worksheets?

    If those do copy over, you could use an event macro behind a worksheet somehow.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    I did some qucik testing and it appears that code behind the sheet will make it all the way thru. The problem now is what event to use for the code?

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    OK, here goes.

    - create a temporary workbook containing the setup of the "income" sheet
    - make all formulas point to that workbook, making sure the cell references are all correct
    - Select a worksheet that is going to be in the new workbook (after the processing)
    - Create a local name pointing to any cell, call it
    'Worksheet name'!Temp
    (note the single quotes!)
    - Rightclick the sheet's tab and choose view code.

    Paste this in:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>

    <font color=blue>Dim</font color=blue> bDone <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>


    <font color=blue>Private</font color=blue> <font color=blue>Sub</font color=blue> Worksheet_Calculate()
    <font color=blue>Dim</font color=blue> oNm <font color=blue>As</font color=blue> Name
    Dim vLinks <font color=blue>As</font color=blue> <font color=blue>Variant</font color=blue>
    <font color=blue>If</font color=blue> bDone <font color=blue>Then</font color=blue> <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>Resume</font color=blue> <font color=blue>Next</font color=blue>
    <font color=blue>Set</font color=blue> oNm = Me.Names("Temp")
    <font color=blue>If</font color=blue> oNm <font color=blue>Is</font color=blue> <font color=blue>Nothing</font color=blue> <font color=blue>Then</font color=blue>
    bDone = <font color=blue>True</font color=blue>
    <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    <font color=blue>Else</font color=blue>
    bDone = <font color=blue>True</font color=blue>
    <font color=blue>With</font color=blue> ThisWorkbook
    vLinks = .LinkSources(xlExcelLinks)
    ActiveWorkbook.ChangeLink vLinks(1), _
    .FullName, xlLinkTypeExcelLinks
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    oNm.Delete
    bDone = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    End <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>

    Make sure at least one cell has the NOW() function in it, so the workbook calculates on open.
    You could even remove that cell's formula somewhere in the code above.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indirect Function & Relative Cell Refs -Copy/Paste (2000/2002/2003)

    Jan,
    First let me say thansk for all yoru efforts. I appreciate you giving it as much as you have. Unfortunately I can't use your suggestion. It's not possible to know in adanace what the name of the 'New' workbook will be as the name changes and is based on the person who runs it combined with what criteria they use and what date and time they run it as of.

    Thanks anyway. [img]/forums/images/smilies/sad.gif[/img]

    Ed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •