Forum Discussion

_Parjam_'s avatar
_Parjam_
Copper Contributor
Jan 22, 2021

STOCKHISTORY FUNCTION

Is there any way to create a stable query with the data spill from the STOCKHISTORY function. I spilled the data to a spreadsheet and created a dynamic named range which captures the data spill, I ad...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 26, 2021

_Parjam_ 

Yes, that's possible, even if that's not very good practice. Please see OneQuery query in attached file.

_Parjam_'s avatar
_Parjam_
Copper Contributor
Jan 28, 2021

SergeiBaklan 

I have tested the different variations, when I use: =OFFSET(Sheet1!$R$2,0,0,(COUNT(STOCKHISTORY(Sheet1!$L$3,Sheet1!$L$4,Sheet1!$L$5,Sheet1!$L$6,0,0,1,5))/3),3)

for the named range the refresh works even when there is no data within the range, but when I use:  =Sheet1!$X$2# the query refresh returns a Invalid cell value "#REF!" when the range is not populated. So I have decided to proceed with the Offset() formula. However there is another problem which occurs when I change the column name from DATE.1 to DATE, on each refresh the table duplicates the data, I have included both samples in the attachment, I would be grateful if you could have a look at the attachment and se if you can figure out why this happens.