Update excel formula at once on multiple cells/sheets

0
votes

I have a table in Sheet1 that looks like this

SPORTS
Basketball
Basketball
Basketball
Volleyball
Volleyball
Football
Football
Football
Football
Football
Football
Hockey
Hockey
Hockey

I have managed to transpose the unique sport names (see example below) into sheet 2 using the following formula: =TRANSPOSE(UNIQUE(FILTER(Sheet1!$A$2:$A$15,Sheet1!$A$2:$A$15<>"")))

outcome:

SPORT Basketball Hockey Football Volleyball

PROBLEM:

I need to now apply this same formula to multiple cells in this excel sheet.... eg cell B1, B10, B15, B20, B25, B40

I want to be able to do this so that I only have to type the formula once and it will automatically update in those specific cells (rather than pasting it individually into every specific cell)

whats the easiest way to do this on Excel please?? so that when I paste this formula into cell B1 cell - THE RESULTS WILL automatically be applied to these specific cells I want B1, B10, B15, B20, B25, B40

2
excelexcel-formulaarray-formulas
it would be possible to do this if you wanted the other cells to be blank. You could just add in an if statement that looks up the row() against a table of the rows you want, and if true does your formula and if false leaves it blank – Hooded 0ne
Use the formula once then refer to the result using the spilled range operator. So if your result is on B1....all other cells you want to get the results in should refer to =B1# – JvdV
@Hooded0ne how can I do this on multiple sheets so for example I want this formula to be updated on cells B10 and B15 on sheet 2 and B10 and B15 on sheet 3 – Girl007

2 Answers

2
votes

In B10 enter:

=B1#

same for the other B-cells

enter image description here

So if the Sheet1 data changes, then all the B cells on Sheet2 will also update.

( the same can be done on other sheets)

1
votes

Does this do what you want?

=IF(ISNA(MATCH(SEQUENCE(40),{1,10,15,20,25,40},0)),"",TRANSPOSE(UNIQUE(FILTER(Sheet1!$A$2:$A$15,Sheet1!$A$2:$A$15<>""))))

There is one potentially large drawback to this solution; you can't enter any data in the cells in between such as D3 or it will trigger a #SPILL error. If you to put data in between, using just the TRANSPOSE formula and referencing Sheet2!B1# where needed as Gary's Student suggested would work better.