Excel INDIRECT or CONCATENATE ?
I am struggling with either INDIRECT or CONCATENATE in (Excel 2003 or 2007 I have access to either).
Using a multi sheet workbook, every month I have to go through each sheet and change the row reference in a cell formula. Thought INDIRECT or CONCATENATE could generate a volatile reference for me.
If in Sheet 1 (Sheet Name FOW) cell A1 I put the current month row reference (same on each sheet eg 23) and on each sheet in cell A2 I put that sheets column reference (they vary - AB, BT, CZ etc.), then a cell in each each sheet should be able to have a volatile cell lookup based on current sheet cell A2 concatenated with Sheet1 cell A1
If I put text in the cells =CONCATENATE(A2,FOW!A1) I can get the CONCATENATED reply (eg JohnDoh) but cannot workout how to get this into a formula.To return the cell value. Where FOW!A1 is 23 and A2 is CZ. The simple cell reference is =CZ23.
Each month I go through each sheet changing =CZ23 to =CZ24 then =CZ25 etc, and on the next sheet =BT23 to =BT24 then =BT25 and so on.
Any assistance appreciated.
Paul
PS Sure it cannot be as difficult as I am making it.
|