Excel Table Formula References

[jjosa]

How can I refer to any table column in a dynamic fashion:


For example, I have several fields in my table (tblSales) - Revenue and Cost. I want to be able to dynamically type in a field value in A1 that would automatically change the contents of the formula in A2.


Similar to the following example:

A1 = "Revenue"

A2 = AVERAGE(tblSales[A1]) such that the A2 formula really shows AVERAGE(tblSales[Revenue])


A1 = "Cost"

A2 = AVERAGE(tblSales[A1]) such that the A2 formula really shows AVERAGE(tblSales[Cost])


It doesn't allow me to do this. Any suggestions?

 

[kaushik03]

Hi jjosa,


Suppose your table is from C1 to D8 at sheet1 (C1 and D1 are headers, Revenue and Cost, respectively).


Now A1, incorporate the data validation (Place the cursor at A1, Go to "Data">"Data Validation">select "List" from "Allow Box">The range for source box should be =$C$1:$D$1


Now you have two options, cost and revenue at A1


Now create two dynamic named range as follows:


Press CTRL+F3 from keyboard>Hit "New"> At name box, write DynRevenue(name of your dunamic named range. You can give any name of your choice)>At "Refer To" box, write =OFFSET(Sheet2!$C$2,0,0,COUNTA(Sheet2!$C:$C,0)-2,1) and hit ok


Similarly,again press CTRL+F3 from keyboard to bring name manager. At name box, write DynCost and at "Refer To" box, write =OFFSET(DynRevenue,0,1)


Now at B10, write the following formula: =IF(A1=Table1[[#Headers],[Revenue]],AVERAGE(DynRevenue),AVERAGE(DynCost))


Changing the option from drop down at A1, should update the formula at B1O dynamically.


Please let us know if this what you are looking for.


Regards,

Kaushik

 

[jjosa]

Thanks Kaushik! It worked. I'll have to spend a bit more time digesting it though.

 

[kaushik03]

Hi jjosa,


Glad to help you.


Let me know in case of any clarification is required.


Regards,

Kaushik