How to Reference a Cell without Specifying Cell Number

[ggelineau]

I have a report where I have built a worksheet where I can simply dump my data into and refresh a pivot on a different worksheet. My problem is that in the data worksheet column AQ has some information that I need for the title in the pivot worksheet. Since I dump the information into the data page, I often have to delete row 2 as it contains extraneous information (titles) that is not needed. This means that if I linked the cell in the pivot worksheet to the AQ2, every time I deleted that row I would loose the reference to it. Is there a was to specify a specific cell without referencing the actual cell number? In this case, column AQ has a title in cell AQ1 and the data I wish to reference in each cell after that (though the amount of rows containing this information is different each time I create the report). So, I can technically reference any cell from AQ2 to the last cell in AQ that contains the information I wish to see.

Ideas?

Thanks,
G

 

[yee388]

This may not be the best way, but =INDIRECT("AQ2") won't change if you were to delete the row. Good luck!