I have a ton of cells that contain formulas with arrays. How can I update these without:
clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER
There's got to be a faster way of doing this. And I'm fairly new to this level of using Excel so please forgive me if it's an obvious answer.
Hi,
Can you use Find > Replace?
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Good afternoon josterfeld
What do you mean by update them ... ?Originally Posted by josterfeld
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
I get #value instead of the proper data from the worksheet. I know you have to use CTRL+SHIFT+ENTER to "activate" the array. But I have so many to do that I need a short cut. Here is the formula:
=SUM(--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="C")*--('1'!$K$14:$K$500="P"),--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="I")*--('1'!$K$14:$K$500="P"),--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="S")*--('1'!$K$14:$K$500="P"))
I'm using this in almost every cell on a tracking worksheet for a workbook that contains multiple worksheets.
Hi josterfield
Have you tried pressing Ctrl + Alt + F9 to force a recalculation?
Failing that could you zip and upload a small portion of the worksheet for us to have a look at?
HTH
DominicB
Forced recalculation does not work. I would like to upload the worksheets, but I would not have a job after that. I'll see if I can fabricate an example that encompasses all the variables in my formula. In the meantime if you can think of anything else, please post it.
Thank You!
Hi josterfield
I don't need to see all the variables - if I come up with a solution that will work with the simplest array formula, it will also work with the most complicated. What I want are a few examples of array formulae that show #VALUE, and then I go into the formula, press Alt + Ctrl + Enter and suddenly the formula works. I cannot recreate that scenario. The uploaded file need only have one sheet and a couple of array formulae that will are local (ie don't depend on outside files) - I don't need to see anything else.
DominicB
Did you see Oldchippy's suggestion?
If you want to replace, say the sheetname or specific ranges, etc... maybe doing a Find|Replace would work...that doesn't change the "array" status of the formulas.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Can you give me an example of how to replace the data.
I used that option for copying from one file to another, as I have to create multiple tracking sheets in different projects. They all use the same basic formula, they just reference different parts columns in each sheet.
How do I copy the formulas from one file to another without having to "Re-activate" all the arrays? This might be the solution to my problem.
Say for instance your existing formula was
{=SUM(--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="C")*--('1'!$K$14:$K$500="P"),--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="I")*--('1'!$K$14:$K$500="P"),--('1'!$B$14:$B$500=2)*--('1'!$H$14:$H$500="S")*--('1'!$K$14:$K$500="P"))}
And you want to change the range of the 500 to 600, then you can select the cells you want to edit, go to Find, type in 500, click the Replace tab, type in 600, then Replace All.
This will alter all the formulas without effecting the { }
I think you misunderstood. I don't want to edit the formula in any way. But when I copy it to another file I lose the fancy array brackets. So when I go to the new file and paste them in, they don't return a value, because they have no brackets. The only way to get the brackets back (that I know of) is to CTRL+SHIFT+ENTER on every formula in every cell. That process will take too much time.
Hi josterfield
Your last post gave me just the information I needed to recreate your problem!
Put this code into a standard module :
Select the cells you need to affect, and go to Tools > Macro > Macros, select "test" from the box and click Run.
Please Login or Register to view this content.
HTH
DominicB
That worked! Thank You! If I break it, I'll be back
The code worked, but then I tried it on a different file and it returned a "400" error in VBA. Any ideas why it did that? Nothing seemed to be different in what I was trying to accomplish.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks