An array in a cell used by formula in another cell

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
191
Office Version
  1. 2019
Platform
  1. Windows
CELL ENTRY
A1 array formula that outputs {5,6,7}
A2 {5,6,7}
A3 ={5,6,7}

I have a formula in a cell, and I want to use A1 in that formula. I can't figure out how to do it.

For example, let's say the formula is ={sum({5,6,7})}. That gives 18. The formula ={sum(indirect(A2))} also gives 18. But I want to use the output in cell A1 in my formula. I can't get A1 or A3 to output 18 in the array sum formula.

Does anybody have any suggestions on how to do this?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Can you have the array returned as a simple comma delimited string like 5,6,7?
 
Upvote 0
Can you have the array returned as a simple comma delimited string like 5,6,7?
I don't think so. If that is possible, it's beyond my abilities. The formula in A1 is an array formula that returns an array. Specifically, it's ={MATCH((B1:F1),RowOfHeaders,0)}. It returns an array (like {0,2,34,1,0}).
I had thought that maybe, if I could somehow convert the answer array (in A1) into a text string, then I could refer to A1 with Indirect with my formula in another cell.


Or maybe I'm going about this the wrong way? I have a very large workbook with long formulas that I am optimizing. Many of the formulas have the section "MATCH((B5:F5),RowOfHeaders,0)" repeating, and the answer array doesn't change when it's copied down (is always calculated to be {0,2,34,1,0} with the given input data). I thought that if I could move that section out to a single cell, and just refer to that cell in other formulas, I could significantly speed up my formulas.

For a simplified example (let's say it's in A5) is something like:
={sum(INDEX(Table1,MATCH((B1:F1),RowOfHeaders,0),1)+vlookup("Key2",Table2,MATCH((B1:F1),RowOfHeaders,0))*(MATCH((B1:F1),RowOfHeaders,0)*12))}

That formula is copied down.

So if I could replace my formula with something like:
={sum(INDEX(Table1,indirect($A$1),1)+vlookup("Key2",Table2,indirect($A$1))*(indirect($A$1)*12))}

My workbook should run much faster. In my workbook, the actual formula is much longer and copied down for many rows. But it's slowing down.


So I either would like to:
a) Refer to an array in a cell (generated by an array formula) used by an array formula in another cell, OR
b) Figure out some other way to make my array formulas more efficient by reducing repeating sections that output the same array (with given input data).
 
Upvote 0
About the only thing I can think of is if you were to post a SMALL sample file so we can see what you're trying to do.

Personally, I won't download files larger than 20kb. People tend to post very large files full of irrelevant data and that just makes it harder for us (the helpers) to find what we're looking for.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top