“Link” new data to a spilled list that changes constantly

[Mciccone]

Hi all.
I have a spilled FILTER list of a database that gets updated every day with some reference numbers and other columns. Every week or so I must analyse these data and fill in another column with the results of the analysis. Both the database and the filter are sorted by date. Now, since the database changes every day, it happens that new rows are inserted sometimes in between the old ones, and therefore the list changes constantly. I’d like a method to “link” this analysis results to the reference number that remains the same even if the data source changes.

 

[awoohaw]

Reference the first cell of the array with a "#" after the cell reference address in your formulas and it should work.
i.e.: VLOOKUP("Blue",H10#,2,1)

 

[Fluff]

If you are manually entering data then you cannot "link" it to the spilled range.

 

[awoohaw]

If you are manually entering data then you cannot "link" it to the spilled range.

Fluff, are you replying to my comment about typing cell ref & "#"?
Maybe I misunderstood the the OP. My take was that he is inserting data in a data source that expands the spilled range as a result of new data.

 

[Fluff]

Fluff, are you replying to my comment about typing cell ref & "#"?

No, I was replying to the OP.

 

[awoohaw]

Hi, I am not clear with # symbol description, could you give more details or give an example, please?

Please review @Fluff's comment. My solution may not work if you're typing data into spilled range.

But this is what I mean:

Assume a spilled range is in cells A1:A5, the first cell contains the spilled formula.
If you want to have a spilled range in a formula then if you highlight that spilled range while building the formula. When you hightlight cells A1:A5 and complete the formula, if you look at the formula after it has been entered the range changes from A1:A5 to A1#.

If you just want to type in the range typing "A1#" will do the same thing. But, I'm unsure if 2019 processes spilled ranges the same way.

Mr Excel Questions.xlsx
	I	J
1	Manual Type J2#	Spilled Range
2	1	1
3	2	2
4	3	3
Sheet4

Cell Formulas
Range	Formula
I2:I4	I2	=J2#
J2:J4	J2	=SEQUENCE(3,1,1,1)
Dynamic array formulas.