Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Arrow Trying to capture a constantly updated value, Excel 2013

    Hi All,
    I have a column of figures that represent the value of production booked out during the day.
    I am trying to setup a simple chart that shows the value in the last row of the column (i.e. todays book out figure)
    A separate process updates the value from SAP throughout the day.
    I created a simple User Defined Function that is supposed to find the last row in the designated column and then put the value into the target cell.
    The chart is then driven from the value in the target cell.
    For some reason Excel thinks the target cell contains a Circular Reference.
    The first time I inserted the UDF into the target cell, the value was captured correctly, but since then... nothing, no updated value gets captured and Excel just whines that a Circular reference exists.
    I cannot see why it thinks a circular reference exists.
    If the target cell with the UDF is in cell B1 and the column of daily output figures is in Column C (on a different worksheet). The values in Column C do not have any dependencies at all, it is a simple dollar value only.
    The UDF is supposed to find the last row in Column C and then get the cells value.
    The macro is shown below.
    Can anyone figure out why this doesn't work?


    Function CurrentZPPR7_Value() As Double
    Sheets("Production Outlook").Select
    Range("C5").Select
    Selection.End(xlDown).Select
    CurrentZPPR7_Value = ActiveCell.Value
    Sheets("ZPPR7 Value").Select
    End Function



    This sub uses the function above and finds the value easily.
    But requires that the sub be run when required rather than the value just getting updated when the output is updated.

    Sub dailyValue()
    Sheets("ZPPR7 Value").Range("B1").Value = CurrentZPPR7_Value
    End Sub


    Thanks in advance for any help.
    Brian.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    As you are selecting a sheet in the function you may need to re-select the working sheet after calling the function. Alternatively use 'With Sheets("Production Outlook")' and don't Select anything.

    cheers, Paul

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Brian

    You can do this without using Custom Functions, without using VBA. Just by using a formula:

    Assuming..
    •your data is on sheet [Production Outlook]
    •you have a header row starting in row5
    •your SAP value is in column [C]
    •there are no 'blanks' between the values in column [C], THEN..

    in cell [B1] on your sheet [ZPPR7 Value] place this formula:
    =INDEX('Production Outlook'!C:C,COUNTA('Production Outlook'!C:C)+4)

    The +4 in the formula is for the four blanks above the header.
    If the data heading is in row1 rather than row5, then you can remove the +4.

    If your heading row starts in row10, then use +9 etc etc etc.

    If your data isn't in column [C], then replace the C:C in the formula with the required column.

    zeddy
    •Excel Spreader of Good Cheer
    .

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hi Paul,
    Thanks for the tips.
    tried to modify my function, but stuffed if I can get this to work for me.
    Oh, I had reselected the original sheet in the original function.
    Did not really know how to use the With Sheets... and NOT select anything.

    So the current mess is shown below...

    Function CurrentZPPR7_Value() As Double
    Dim ZPPR7_Value As Double
    With Sheets("Production Outlook")
    .Select
    .Range("C5").Select
    Selection.End(xlDown).Select
    ZPPR7_Value = Selection.Value
    Sheets("ZPPR7 Value").Select
    CurrentZPPR7_Value = ZPPR7_Value
    End With
    End Function

    Thanks
    Brian.

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by zeddy View Post
    Hi Brian

    You can do this without using Custom Functions, without using VBA. Just by using a formula:

    Assuming..
    •your data is on sheet [Production Outlook]
    •you have a header row starting in row5
    •your SAP value is in column [C]
    •there are no 'blanks' between the values in column [C], THEN..

    in cell [B1] on your sheet [ZPPR7 Value] place this formula:
    =INDEX('Production Outlook'!C:C,COUNTA('Production Outlook'!C:C)+4)

    The +4 in the formula is for the four blanks above the header.
    If the data heading is in row1 rather than row5, then you can remove the +4.

    If your heading row starts in row10, then use +9 etc etc etc.

    If your data isn't in column [C], then replace the C:C in the formula with the required column.

    zeddy
    •Excel Spreader of Good Cheer
    .
    Hey Zeddy,
    Only saw your answer after I replied to Paul's post.
    YOU DA MAN!
    Made one slight change to the formula in that I used Count rather than CountA, as all the values are numerical.
    The solution you proposed works a treat.
    Thanks for your help.
    Regards
    Brian.

Posting Permissions

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