Hello, I am currently using data from many different worksheets in one workbook. I would like to be able to use the same exact formula on a "Master" data sheet that compiles data from all of the worksheets to be analyzed. I would like to just spill the worksheet names and leave the columns and numbers the same since all of the worksheets look the exact same, just with different data. for example, I want to have the equation ='sheet1'$A:$1 spill down to be ='sheet2'$A:$1, but excel will not let me spill a sheet name. some of the things I have looked up tell me about different arrays or queries and I'm honestly just very confused because even when I try those it gives a #REF error. So, my question is is it even possible to spill a worksheet name or do I just have to manually go and change it for every single cell? I can't pull them across due to gaps in my worksheets or cells with titles rather than numbers, I can only pull the equation down.
Thanks :)
