I want to call cell A???. The value of B1 is "100". I want to call A100 by using that value in B1. So how do I generate a reference to CELL Avalue of B1? I need to do it like this because I want this to be dynamic, so I can simply change the contents of B1 and have this formula now call to cell A200, for example.
Any help would be much appreciated.
-Chris
Multiple ways of doing this:
=INDIRECT("A"&B1)
=OFFSET(A1,B1-1,0,1,1)
=INDEX(A:A,B1)
Update in Response to Comment by Scott
How to choose between the options above:
Indirect: Indirect leads to recalculation each time a cell is updated in the sheet and is a "heavy" function. However, you can construct the address dynamically (at least the row number). This was closest to your question and easiest to understand which could be important in case you have only one or two such formulae in the sheet.
Index: Index offers great performance as long as the range you need to choose from is defined. It works in both directions (Horizontal and Vertical) in at least recent versions of Excel. It is limited in a way that it can only refer to one cell and not a range unlike the other methods. It is a safe bet even if you insert or delete rows and columns from the selection range. The formula will adjust itself.
Offset: Offset offers greater flexibility at a cost. Excel recalculates this formula every time any cell in the sheet is updated leading to the file becoming "Heavy". The benefit it offers is flexibility in the size of the range that you need for further calculation. For example you could write a formula in A1 =SUM(OFFSET(B2,A2-2,B1-2,A3,C1)) to get the sum of a dynamic range starting at Row specified in A2, Column in B1, Width in A3 and Height in C1.
Update in Response of Needing Reference to Another Sheet
My personal preference in this case is Offset since it allows the original cell to be in another sheet, or even another Workbook!
=OFFSET(Sheet2!A1,B1-1,0,1,1)
Update in response to the more complex problem stated by OP in comments
The following formula will solve the complex problem stated by OP =COUNTIF(OFFSET(INDIRECT(A2&"!Z"&C3),0,0,C4-C3+1,1),C5)+COUNTIF(OFFSET(INDIRECT(A2&"!F"&C3),0,0,C4-C3+1,1),C5)
Explanation
Indirect formula identifies the starting cell on another sheet.
Offset uses this and identifies the entire range to compare.
Countif uses the output of Offset and counts the cells that match C5.
This is done twice for Columns Z and F on sheet specified by A2 in current sheet. There is always hope!