Cannot modify or delete arrays in shared workbook

[SaraWitch]

Hi peeps,

Is there any way to allow an array formula to be overwritten in a shared workbook?

p.s. found an old link without any solutions, so I'm presuming it probably can't be done! Cannot modify or delete arrays in shared workbook - Solution

 

[RoryA]

Given that you have 365, why do you need an array formula at all?

 

[SaraWitch]

Hi Rory,

In a lookup sheet I have a list of works in one column with their costs in the next (and have named the whole section, "Works").

In my main sheet, I have a multi-select data validation in one column and a formula to add up all the costs of the works selected in another. This formula should allow an overtype for additional works not listed, or if they are different to the suggested cost. It works great before I share it, but get the title error upon sharing. My formula is (I can replicate the whole scenario if needed):

=IF($A1="","",SUM(IF(ISNUMBER(FIND(Works,$A1)),Works,0)))

If there is an easier way...?

 

[RoryA]

If Works is two columns, why are you using all of it in FIND? Can you share an example of the results of the multiselect validation? It may be that you can just use SUMIF with a textsplit to break out all the relevant criteria, if I've understood the layout correctly.

 

[SaraWitch]

Sorry, Rory - incorrectly typed the formula in my previous post (last named range should have been "Costs"). Here's an example table that works fine all the time the workbook isn't shared:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Works	Costs (ability to overwrite in a shared workbook)								Works	Costs
2	Air source heat pump Bathroom	£ 12,000.00								Air source heat pump	£ 8,000.00
3	Kitchen Bathroom	£ 10,500.00								Bathroom	£ 4,000.00
4	Rewire	£ 2,000.00								Kitchen	£ 6,500.00
5	External doors	£ 500.00								Level access shower	Install costs
6	Air source heat pump Level access shower	£ 10,000.00								Radiators	£ 2,500.00
7										Rewire	£ 2,000.00
8										Solar panel	£ 8,500.00
Shared Wbk Array

Cell Formulas
Range	Formula
B2:B4,B7:B8	B2	=IF(A2="","",SUM(IF(ISNUMBER(FIND(Works,A2)),Costs,0)))

Named Ranges
Name	Refers To	Cells
Costs	='Shared Wbk Array'!$K$2:$K$8	B2:B4, B7:B8
Works	='Shared Wbk Array'!$J$2:$J$8	B7:B8, B2:B4

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	List	=Works

 

[RoryA]

It looks like you are using line feeds to split items in the DV list? In that case, something like this should work:

=IF($A1="","",SUM(SUMIF(Works,TEXTSPLIT($A1,CHAR(10)),Costs)))

 

[SaraWitch]

Thank you, Rory. However, your formula does not correctly calculate for me (and it also doesn't allow overtype when workbook is shared).

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H
1	Works	Costs (ability to overwrite in a shared workbook)	RoryA alternative formula				Works	Costs
2	Air source heat pump Bathroom	£ 12,000.00	£ 4,000.00				Air source heat pump	£ 8,000.00
3	Kitchen Bathroom	£ 10,500.00	£ 4,000.00				Bathroom	£ 4,000.00
4	Rewire	£ 2,000.00	£ 2,000.00				Kitchen	£ 6,500.00
5	External doors	£ 500.00	£ -				Level access shower	Install costs
6	Internal doors Radiators	£ 3,500.00	£ 2,500.00				Radiators	£ 2,500.00
7	Level access shower	£ 1,200.00	£ -				Rewire	£ 2,000.00
8	Air source heat pump Solar panel	£ 16,500.00	£ 8,500.00				Solar panel	£ 8,500.00
Shared Wbk Array

Cell Formulas
Range	Formula
B2:B4,B8	B2	=IF(A2="","",SUM(IF(ISNUMBER(FIND(Works,A2)),Costs,0)))
C2:C8	C2	=IF($A2="","",SUM(SUMIF(Works,TEXTSPLIT($A2,CHAR(10)),Costs)))

Named Ranges
Name	Refers To	Cells
Costs	='Shared Wbk Array'!$H$2:$H$8	B2:B4, C2:C8, B8
Works	='Shared Wbk Array'!$G$2:$G$8	B2:B4, B8:C8, C2:C7

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	List	=Works

 

[RoryA]

I just copied that to a workbook and my formula returns the same values as yours, not the values you show.

But it seems that the old shared workbook feature converts those into array formulas automatically anyway. Why do you need to use that rather than co-authoring?

 

[SaraWitch]

Very strange it's not working for me

There was some issues co-authoring where some links weren't working as well as there being some viewing/save problems. I need to investigate further before putting the (very large!) workbook back into OneDrive. Just to confirm, you don't need to password protect the workbook when it's shared in this way, do you (because you are setting the permissions when sharing the link)?

 

[RoryA]

Correct.

How large is "very large"?