• 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...

  • 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

Didson New Pereira

Didson New Pereira

New Member
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?
 
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 :)
 

Attachments

  • PROJECTS - ANDRÉ.xlsm
    630.9 KB · Views: 7
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!
 
SirJB7 said:
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!
Click to expand...

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,
 
Didson New Pereira said:
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!
Click to expand...
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!
 
SirJB7 said:
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!
Click to expand...

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 !!
 
Dear all,
I have worked on a model file that might help to show what I want to achieve.

In the file there are 3 sheets, the first two will bring data from another sheet through power query.
1st sheet: Data that define kinds of projects and their aspects.
2nd sheet: Data that define formating, lists, formulas and structure of the projects table.

3rd sheet: Is where operators will actually control their projects, aided by the formating, lists and formulas, while following the structure duly defined by the data brought throug Power Query to 1st and 2nd sheet.

My problem has been on how to make VBA substitute the formulas on their rightful place given by the 2nd sheet in Lists (L), Conditional Formating (F), and Cells (C).
Hope someone will be able to help
 

Attachments

  • MASTER DATA.xlsm
    24.5 KB · Views: 9
Hello Everyone,

I am still trying to work this out but without much success... A friend showed me a trick with Evaluate Function which seemed promissing, but I wasn't able to make it work with some longer and more complex formulas which use matrix references and functions combined.

I am almost giving up the whole formulas update through Power Query, and starting to look into some other option now that I have even been working quite a lot with VBA, and one that hit me today was of, making a procedure in a model blank file, which will ask reference to the old file where all the raw data of a given user is, and it will bring all data from there to itself.

Every time I need to release a new version, I would make it available blank of raw data, and the users can fill it with their own referencing their latest file.

Pros: There is one power query less to load everytime users open their files.
Cons: There are quite a few collumns (about 60) and lines (a couple hundreds) of raw data that would have to be correctly read and placed in the correct places.

I guess the heavy part will be to code this procedure the first time, but after that, every new change in the model file will require only a small add to it in order to make it bring all the raw data from previous files of each user. This adition would address exactly the raw data that was implemented in the imediate previous model file released before.

I am even starting to believe that this approach is actually better, as it allows me to work quite more freely in the possible changes the users files could have.

If you are reading this, please let me know your thoughts, and specially if you have seen, or done something like it, I would extremaly appreciate your help if you can direct me to some tutorial on it, or give some more hints on how better build it.

Thanks a lot!
 
You must log in or register to reply here.
Back
Top