>7 if statements

please help,
sheet 1 = project names, 12 in all:
cell a1=project 1, cell a2 = project 2, ....
sheet 2 = expenditures data:
cell a1= 1000 (project 1 admin), ..., cell a12 = 12000 (project 12 admin).
sheet 3 = budgeted amounts data:
cell a1= 1205 (project 1 admin), ..., cell a12 = 10000 (project 12 admin)
sheet 4 = generic form; cells references values from sheets 1 - 3 using if statements. column c = expenditures, column d = budget, column e = difference(variance)

in sheet 4,
cell a1 i entered 12.
cell a2 gave me "Project 12". (the formula i used = if(a1=1,Sheet1!a1,"")&...&if(a1=12,Sheet1!a12,"")

The problem:
in sheet 4,
cell c10, i would like to use a formula that would automatically insert the appropriate value from sheet 2, range a1:a12. in this instance, $12000 since entered 12 in cell a1

i used the "&" with the if value inserted is text. when i use the if without the & it will not allow more than 7)

the formula will be used in most of the cells in sheet 4, generic form.

i've tried naming formulas, cells, ranges, etc but still can't figure how to write the right formula i need.

thanks in advance.

Use:

=OFFSET(Sheet2!A1,MATCH("Project "&A1,Sheet1!A1:A12,0)-1,0)

In cell C10 of Sheet 4.

- Clay Ver Valen
Excel Help

Originally Posted by clayv

Use:

=OFFSET(Sheet2!A1,MATCH("Project "&A1,Sheet1!A1:A12,0)-1,0)

In cell C10 of Sheet 4.

- Clay Ver Valen
Excel Help

Clay, thanks. The formula is amazing. I am using it on some of the cells and some of my other spreadsheets. Works perfectly.

Is there a work around since my project names are not as I described: Project 1, project 2 ...., rather the names are unique: State College, Mt Vernon, Fortieth Street, etc. I was trying to incorporate the numerical project names to my data to take advantage of formula's power. Unfortunately, my limited excel know-how somehow gets me more confused.

Clay, thanks again for your amazing formula.

I think the attached sample will work for you. Don't worry about adding more data into sheets. The validation list will automatically grow as you add more data.

- Clay Ver Valen
Excel Help

Clay, thanks. Your formulas and sample file makes Excel even better.
Thank You.