Hi,
I have a worksheet where I have a dashboard on a worksheet which references multiple worksheets. I am referencing data in cells which have the same relative locations throughout the worksheets by using an index of sheet names and then using a INDIRECT function (eg for the index being in column A, this might be =INDIRECT("'"&A3&"'!f3") for data held in f3 in each worksheet.
As I add new worksheets I copy down a new row on the dashboard worksheet and update the index name and the static fields update well.
However, there are cells I would like to reference further down in each worksheet which may move up or down a column (but remain in the same column) due to additional data being added in new rows. Eg, the data I want to reference from worksheet 2 might be in g11, but in worksheet 3 it might be in g12. Is there any way of using anchors or names which follow a cell that I could use in the indirect function that would update with a copy down from a previous row? or is this just not possible and I will have to update the cell reference manually for each dashboard column entry?
thanks
Spicerguy
I have a worksheet where I have a dashboard on a worksheet which references multiple worksheets. I am referencing data in cells which have the same relative locations throughout the worksheets by using an index of sheet names and then using a INDIRECT function (eg for the index being in column A, this might be =INDIRECT("'"&A3&"'!f3") for data held in f3 in each worksheet.
As I add new worksheets I copy down a new row on the dashboard worksheet and update the index name and the static fields update well.
However, there are cells I would like to reference further down in each worksheet which may move up or down a column (but remain in the same column) due to additional data being added in new rows. Eg, the data I want to reference from worksheet 2 might be in g11, but in worksheet 3 it might be in g12. Is there any way of using anchors or names which follow a cell that I could use in the indirect function that would update with a copy down from a previous row? or is this just not possible and I will have to update the cell reference manually for each dashboard column entry?
thanks
Spicerguy
