1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic update of formulas in cells, lists and conditional formating on several workbooks

Discussion in 'VBA Macros' started by Didson New Pereira, Jul 12, 2017.

  1. Didson New Pereira

    Didson New Pereira New Member

    Messages:
    6
    Hello everyone,
    It is my first experience with forums, and from what I have seen here in other posts, I am really hopefull someone will be able to help me on my task.
    I have a table of dynamic formulas in string format, some of them are array formulas, brought through Power Query to this workbook, and I want to make a macro that will replace the cells' values of a table in another sheet to these formulas according to their collumn "address".
    Is it possible to make a macro that will paste a string formula from one cell to another and make the destination cell interpret it as a formula and not a string anymore?
  2. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,718
    Hi, Didson New Pereira!

    Welcome to Chandoo forums!
    Checked this yet?
    http://chandoo.org/forum/forums/new-users-please-start-here.14/

    Consider uploading a sample file. It'd be easier to understand for people who might be able to help you.
    Include indication of the input data, the attempted solution (formulas, code) and the manually written expected output data as example. Thanks.

    Regards!
  3. Didson New Pereira

    Didson New Pereira New Member

    Messages:
    6
    Hi SirJB7!
    Thank you for the reply! I had read some of the material for new users yesterday, and took the time to read some more today just now before writing this reply :)
    I thought about uploading a file, but the files I was working with had quite a lot of confidential information, as well as the thing I am trying to acchieve, I don't even know it this is really possible. But as I managed to do a lot more already than I thought it was possible, it is worth giving it a try right? haha

    I prepared new "model" files, with the information to try make the picture of what I am trying to do.

    The file PROJECTS - 'user'.xlsm will load through power query the data on table 'tblFórmulas' in another workbook, to '[PROJECTS - ''user''.xlsm]Fórmulas!'. This tblFórmulas is kind of a Master Data of formulas, with and Excel collumn address, Title name of a collumn, formula in string format ('=), identifying letter for cell (C), list (L) or conditional formating (F), and the formating code that should be applied when applicable.

    Then, from this information, I am trying to make a function that will substitute any information in sheet PROJECTS of this file, on the specific collumn addresses, table collumn titles, and formulas whether they are in cells, dropdown lists, or conditional formating in tblProjects.

    I have been trying to use Application.Volatile / Evaluate function, but it did not worked for complex formulas such as the ones I have which use a lot of arrays, nor for lists or conditional formating. I tried to use the macro recorder also, but I cannot find a way out of the Selection.FormulaArray function which essentially re-writes my formulas, in english (I have a PT-BR excel version only at work) and then all the copying from the table brought from power query (PQ) is lost...

    I am really sorry for the long long post... I believe I will only need to explain in such detail once :p
    Hope all this makes sense to you and that you can help me, as updating these formulas everytime something needs to be changed, and in each and every single user workbook is too much work. I would like to do it once in my compilation file, and theirs get updated automatically.

    Thank you in advance :)

    Attached Files:

  4. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,718
    Hi, Didson New Pereira !
    You not only post long, your workbooks looks like an Access DB. :eek:
    Let me find some time to give something deeper than a quick look to the uploaded file and maybe I'd able to come back to you with a question at least.
    Regards!
    Didson New Pereira likes this.
  5. Didson New Pereira

    Didson New Pereira New Member

    Messages:
    6
    Thank you for your feedback SirJB7 !! I was afraid that would be the case that my workbooks are quite full and probably seem quite messy... Sorry for that... haha
    I am worried also about the fact that string formulas in tblFórmulas are in PT-BR format, and being so, even though your Portuguese looks great! I am not sure if you would be able to know exactly what is going on in some of these string formulas...

    Thank you again to take some time whenever you can to check this! It really means a lot for me, and if you have any questions, please feel free to ask :)

    Regards,
  6. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,718
    Hi, Didson New Pereira!

    Didn't have time this Friday, now weekend (when I try to practice the rule of a meter and a half -distance from my hands to any keyboard-)... sorry but nothing new until Monday/Tuesday.

    About your localised formulas, maybe you want to give a look at this old project:
    http://chandoo.org/forum/threads/excel-multilanguage-formula-translator-and-function-reference.4789/

    Regards!
    Didson New Pereira likes this.
  7. Didson New Pereira

    Didson New Pereira New Member

    Messages:
    6
    What a good idea this formulas translator is! quite often have to work in my projects from my personal computer which runs English Excel 2016 version, while my office runs with a Portuguese 2013, tell me about version and language problems!! hahaha

    I will surelly take some time to check it more in depth later :) Tks !!

Share This Page