Get formula to operate within table name reference

[b.hill2387]

I would like the formula below to be able to reference the cells below relative to a table named "Table1" instead of the worksheet. For example, currently B2 references the cell in the second column and second row of the active worksheet. I would like for it to reference the second column and second row within "Table1". So if cells C10:G50 are named "Table1" then the B2 piece of the formula below would reference cell D11 (the second column and second row within "Table1").

=IF(AND($B2>$A$1,$B2<=$A$2),$C$1,)

 

[circledchicken]

Hi,

Can you provide a small sample of your table with expected outcomes for the formula?

Will A1, A2 and C1 also be part of the table? Is this formula going to go in a particular column of the table or reside outside it?

 

[b.hill2387]

Hi,

Can you provide a small sample of your table with expected outcomes for the formula?

Will A1, A2 and C1 also be part of the table? Is this formula going to go in a particular column of the table or reside outside it?

I am not allowed to post attachments. Is there a way I can PM you the Excel file?

 

[circledchicken]

You can try one of the following options:

• Excel Jeanie Html
• https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189
• or provide a link to the file on Skydrive or DropBox

 

[b.hill2387]

Thanks but I actually figured it out! I completely forgot about using the Offset function. So I just reference the table and use the Offset function to operate within the table.

 

[circledchicken]

Thanks but I actually figured it out! I completely forgot about using the Offset function. So I just reference the table and use the Offset function to operate within the table.

Ok, cool. In case you find it useful for future reference here are some resources on working with structured references in Excel tables:

• Working with Tables in Excel 2010 and 2007
• Using structured references with Excel tables - Excel - Office.com