Hi all!
I'm looking for code that would allow me to 'activate' 1000's of concatenated formulas that have been trimmed via another formula, the resulting displayed formulas copied and pasted as 'values'. Unfortunately, the pasted 'formulas' don't activate and act as 'real' formulas. Yet, they truly do work! Read on...
Let me try to break this down in an effort to make what I just said more understandable.
For simplicity: cells D2:I2 each contain a text portion of a formula. Cell C2 contains the CONCATENATE formula assembling cells D2:I2. The final concatenated formula is a formula containing links to cells in closed files (100's). The assembled concatenated formula is of the basic form:
'=IF(ISBLANK('fully qualified path hyperlink to a single cell in a closed workbook'),"",'fully qualified path hyperlink to a single cell in a closed workbook')
All syntax in the displayed concatenated formula is correct, EXCEPT for the leading apostrophe which was necessary because cell D2 contained the '=' sign followed by the beginning fragment of the final formula. So, it needed to be viewed as text by Excel.
Cell B2 contains the following formula which strips off the leading apostrophe, so that the displayed formula begins with the '=' sign:
=RIGHT(C2,LEN(C2)-1)
The 'value' displayed in cell B2 is a perfect syntax assembly of the final concatenated formula. So, to commit that value to B2 (and 1000's of other similarly constructed cells), I copy the cell and paste back into the same cell as a value, since pasting as a forumula would have just pasted
=RIGHT(C2,LEN(C2)-1), which obviously isn't what I want.
Now, you might be thinking that I could just copy the displayed 'value' formula and paste as a formula and all would be well. Well...no. It doesn't work that way...
However, to PROVE the resultant formula actually was correct and would work, I clicked anywhere in the resultant formula in the formula bar and pressed 'Enter'. Voila! The value in the external closed workbook displayed correctly. Yay!
Here's the catch. I'd have somewhere in the neighborhood of 30,000 cells plus to click in each cell's formula bar and press 'Enter' in order to see the linked value!!! Pressing 'Enter' alone does NOT work. Can you say Carpal Tunnel Syndrome!?
Naturally, I'm hoping someone has already been faced with a similar situation and knows exactly what to do to 'activate' the contents of these 10,000's of cells. If not, I'm hoping someone knows some clever code where I could specify the range of cells to 'activate' and have them do their 'thing'!
BTW: I've tried saving/closing/re-opening the workbook to see if Excel would just 'do it'. No...it won't.
NOTE: I didn't use the INDIRECT function as it won't work on external, closed workbooks. Yes, I've heard of INDIRECT.EXT, but this is a shared workbook and I didn't want to try to train managers on how to install the add-in.
Bottom Line: the final 'formulas' have valid syntax and DO work as long as I was of a mind to do all the manual formula bar clicking and pressing 'Enter'. I'm not!
Please let me know if this is still unclear. I'll be happy to elaborate, if necessary. Thanks for any and all suggestions/code, etc.!!!
I'm looking for code that would allow me to 'activate' 1000's of concatenated formulas that have been trimmed via another formula, the resulting displayed formulas copied and pasted as 'values'. Unfortunately, the pasted 'formulas' don't activate and act as 'real' formulas. Yet, they truly do work! Read on...
Let me try to break this down in an effort to make what I just said more understandable.
For simplicity: cells D2:I2 each contain a text portion of a formula. Cell C2 contains the CONCATENATE formula assembling cells D2:I2. The final concatenated formula is a formula containing links to cells in closed files (100's). The assembled concatenated formula is of the basic form:
'=IF(ISBLANK('fully qualified path hyperlink to a single cell in a closed workbook'),"",'fully qualified path hyperlink to a single cell in a closed workbook')
All syntax in the displayed concatenated formula is correct, EXCEPT for the leading apostrophe which was necessary because cell D2 contained the '=' sign followed by the beginning fragment of the final formula. So, it needed to be viewed as text by Excel.
Cell B2 contains the following formula which strips off the leading apostrophe, so that the displayed formula begins with the '=' sign:
=RIGHT(C2,LEN(C2)-1)
The 'value' displayed in cell B2 is a perfect syntax assembly of the final concatenated formula. So, to commit that value to B2 (and 1000's of other similarly constructed cells), I copy the cell and paste back into the same cell as a value, since pasting as a forumula would have just pasted
=RIGHT(C2,LEN(C2)-1), which obviously isn't what I want.
Now, you might be thinking that I could just copy the displayed 'value' formula and paste as a formula and all would be well. Well...no. It doesn't work that way...
However, to PROVE the resultant formula actually was correct and would work, I clicked anywhere in the resultant formula in the formula bar and pressed 'Enter'. Voila! The value in the external closed workbook displayed correctly. Yay!
Here's the catch. I'd have somewhere in the neighborhood of 30,000 cells plus to click in each cell's formula bar and press 'Enter' in order to see the linked value!!! Pressing 'Enter' alone does NOT work. Can you say Carpal Tunnel Syndrome!?
Naturally, I'm hoping someone has already been faced with a similar situation and knows exactly what to do to 'activate' the contents of these 10,000's of cells. If not, I'm hoping someone knows some clever code where I could specify the range of cells to 'activate' and have them do their 'thing'!
BTW: I've tried saving/closing/re-opening the workbook to see if Excel would just 'do it'. No...it won't.
NOTE: I didn't use the INDIRECT function as it won't work on external, closed workbooks. Yes, I've heard of INDIRECT.EXT, but this is a shared workbook and I didn't want to try to train managers on how to install the add-in.
Bottom Line: the final 'formulas' have valid syntax and DO work as long as I was of a mind to do all the manual formula bar clicking and pressing 'Enter'. I'm not!
Please let me know if this is still unclear. I'll be happy to elaborate, if necessary. Thanks for any and all suggestions/code, etc.!!!
Last edited:
