Simple Index function drag down to change the row value accordingly

[ahme0740]

Hi, I have a pretty simple function. Context: I have 1 excel file and I'm trying to compile data onto one of the sheets (detailed report) from another sheet (data pull). I have some additional columns with formulas in the detailed report sheet that I don't want to overnight by copy pasting the data from the data pull sheet. The data pull sheet will be different every time (can't use VLOOKUP because there isn't something that would remain the same). I'm querying the data from a pharmacy database and depending on the time and parameters I use the query, the stores, order numbers, prescription numbers, etc would always be different. But I need to column names on detailed report sheet to stay as what I set them to but I want to pull the data for the relevant columns from the data I paste into the data pull sheet. I have data in columns A to R in the data pull sheet and A to T in the detailed report sheet (the two additional columns fall in the middle of the sheet). Anyways so I decided to use an INDEX function (hope this is correct), I'm okay with doing one function per column and dragging it down to reduce the complexity of the formula. So for Column A (named StoreId in A1), so from A2 down to however many rows the SQL query pulls for the given data pull, this is the formula I have =INDEX('Data pull'!$1:$1048576,2,1)

When I drag it down I want it to say =INDEX('Data pull'!$1:$1048576,3,1) for cell A3, =INDEX('Data pull'!$1:$1048576,4,1) for cell A4 and so on. If possible I would also like it to not display anything (blank cell) if I drag it all the way down and there are no records in that row from the data pull sheet. Is this even possible? I'm sure it is, but I'm very confused *crying face*

Right now when I drag it down it just copies =INDEX('Data pull'!$1:$1048576,2,1) down all the way and doesn't change anything. Please help!!! Thank you in advance

 

[ahme0740]

Thank you so much! I will give this a try!