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


On Jan 12, 4:53*am, excel help acct
wrote:
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!