+ Reply to Thread
Results 1 to 6 of 6

How to refresh Formula in Name Manager?

  1. #1
    Registered User
    Join Date
    08-15-2014
    Location
    Germany
    MS-Off Ver
    2003 - 2010
    Posts
    3

    How to refresh Formula in Name Manager?

    Hey there!

    I've got a strange problem.
    I tried to have a picture change depending on what the value is in a dropdown list.
    I got that to work no problem.
    However, I now tried to write a makro which basically just adds another row of that feature to the sheet.
    My strange problem now is, that inside the NameManager where I have to put the indirect() formula I get the correct code, but I manually have to delete any character and add it back in so that the reference works (refresh it in a kind).

    Are there any suggestions on what I could have done wrong or how to automatically refresh that formula?
    Thanks in advance!

    Regards,
    Bobbiii
    (hopefully my english is good enough to explain my problem)

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to refresh Formula in Name Manager?

    Not sure i understand, i think you need to look at the .refersto of the named range.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    08-15-2014
    Location
    Germany
    MS-Off Ver
    2003 - 2010
    Posts
    3

    Re: How to refresh Formula in Name Manager?

    I will try to explain it to you better.

    My VBA Code for creating the formula looks like this:

    ActiveWorkbook.Names.Add Name:=Trim("Ampel_" & zaehler), RefersTo:= _
    "=" & referenz

    referenz is a variable to adapt to every row by counting up:

    ="Indirect(Sheet1!$A$" & F11 & ")" with F11 being the counter

    So if I run the macro, then the outcome of the formula inside the name manager is e.g

    =INDIRECT(Sheet1!$A$12)

    In theory that is exactly what I want, BUT the formula doesn't react to the Cell A12. It only does it when I re-type any character of the formula (refresh it in a way). Then it works perfectly fine..

    Hope this is a better explanation.

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to refresh Formula in Name Manager?

    HI

    I did it this way, with F38 being a count

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-15-2014
    Location
    Germany
    MS-Off Ver
    2003 - 2010
    Posts
    3

    Re: How to refresh Formula in Name Manager?

    It works!

    Thank you very much Nathan!

    [/SOLVED]

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to refresh Formula in Name Manager?

    No problems

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] problem copying images by name manager and match formula
    By dabaron in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2014, 08:48 AM
  2. Replies: 1
    Last Post: 02-04-2013, 11:37 AM
  3. Enable Auto Refresh - Wait for refresh to sinish before running a macro
    By tomlancaster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2011, 11:52 AM
  4. Replies: 3
    Last Post: 04-03-2008, 03:16 PM
  5. [SOLVED] How to replicate a Scenario Manager Formula?
    By Ted in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-05-2006, 05:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1