conditional change of column reference in formulas

[excel help acct]

Is there a way to conditionally change all column references in formulas on a
worksheet depending on a value entered in one field.

Example:
1. In Cell A1 the user enters "peanuts"
2. You do a lookup on "peanuts" and find the column associated w/peanuts is C
3. Now you need to change all formulas which reference the previous column
reference to column C to get the correct figures for the spreadhseet

Can a macro be written to do this for a spreadhseet or even an entire
workbook? If so what would be some sample code? Any other means to go about
this? Is it even possible to do this in excel?

THANKS!

 

[Ag]

You can use a combination of match ,address and index to address the
conditional formula change if you dont have too many formulae as shown
below

peanut
3 5 4
cashew peanut walnut
=INDIRECT(ADDRESS(2,MATCH(A1,A3:E3,0),4))

HTH

Regards
Anirudh