using a calculated value as a row number

you cannot reference a cells value directly, you need to use the indirect and address combination

so yours would be something like this:

=indirect(address(B2,2,,,"Sheet 2::Table 1"))

Jason

One approach is to use your row number with INDEX to retrieve the value in the cell on that row:

Here the row number is in B2. You could replace the B2 in the formula with your working MATCH function.

SG

Darn it, I guess is use Address since I am always using the sheet (as well as column and row combinations) as an input and completely forgot index doesnt care if the sheet is not going to change. Got used to Address being very flexible, got used to using it and forgot to go simple and use index 🙂

Thanks SG, reminding me to rethink things, i forget that sometimes,

Jason

Gave me a nudge to have a look at ADDRESS too. Thanks.

SG

as an example, I have my teams vacation calendar made up in excel.

Each team member has a tab with their name on it where i have a vertical list of all approved vacation, sick days, etc... with dates, notes... you get the idea. It also has a count of all information for days remaining of each type of day (vacation, sick, floaters, etc)

On my summary and calendar view tabs, all i have to do is add their name to the top of a new column and fill equations over and that is used by address functions to know what sheet to go to for all those lookups in that column. for different types of data (date, reason code, notes) i also feed the column number to use on the destination sheet. So all arguments for the address function are references to other cells including what sheet to go to. One formula is used for all cells, no changes required, ever.

Makes adding or removing a team member sooo easy.

you can do something like this to convert a calculated value to a valid address:

enter the column (0 based) in B1 and the row (also 0 based) in C1

A1=INDIRECT(CHAR(CODE("A")+B1)&C1)

this is shorthand for... select cell A1, then type (or copy and paste from here) the formula:

=INDIRECT(CHAR(CODE("A")+B1)&C1)

to get the value in cell A3 enter 0 in cell B1 and and 3 in cell C1

to get the value in cell C5 enter 2 in cell B1 and and 5 in cell C1

To see how the address if formed just look at the argument to INDIRECT() by itself and change the values in B1 and C1

=CHAR(CODE("A")+B1)&C1

Nice way of doing it, it is a good demo that any way you construct the string that looks like a reference, it still needs to go through the indirect function to be converted into a valid range reference.

jason

Decided VLOOKUP function suits my purpose exactly. A few proof-of-concept tests showed me what I wanted to see.

Yes, VLOOKUP is essentially a less flexible subset of the INDEX MATCH approach.

SG

Hi Jonathan,

Is this what you originally requested? I've used a different column ( C, instead of B ) to contain the values to be returned to avoid the possibility of a self reference error when B2 contains a 2. The formula is in D2.

D2: =INDIRECT("C"&B2)

Regards,

Barry