Copy named cells to evaluate diferent data.

CFL23

New Member
Joined
Apr 27, 2012
Messages
3
Hi all, im looking for a way of using named cells instead of nesting IF functions, im going to work with 2 BIG IF functions, in the case im working out I can use a VLOOKUP function but I need it for instructional purposes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
So here’s what I’ll do, I’ll take this first function and give it a name, let’s say formula_1<o:p></o:p>
=IF(C2=$A$13,$B$13,IF(C2=$A$14,$B$14,IF(C2=$A$15,$B$15,IF(C2=$A$16,$B$16,IF(C2=$A$17,$B$17,IF(C2=$A$18,$B$18,IF(C2=$A$19,$B$19,0)))))))<o:p></o:p>
This second one, formula_2<o:p></o:p>
=IF(C2=$A$20,$B$20,IF(C2=$A$21,$B$21,IF(C2>=$A$22,$B$22,0)))<o:p></o:p>
And ill do this =if(formula_1,formula_1, formula_2)<o:p></o:p>
So here’s the Q, when I place this function on the first group of values A2 to evaluate everything is ok, but how do I copy it down the cells to evaluate the other group of data, B, C,etc.<o:p></o:p>
 

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
Formula1

can be replaced with

=INDEX(A13:B19,MATCH(C2,A13:A19,0),2)

=VLOOKUP(C2,$A$13:$B$19,2,FALSE)

thats the Vlookup for it

More to follow
 
Last edited:

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
Totally missed the point

Named ranges can be created using a formula to define the referenced range

but i don't think they can be affected by relative references

so dragging your formula of named ranges, won't reference changing cell locations
 

Forum statistics

Threads
1,082,450
Messages
5,365,591
Members
400,840
Latest member
Fortune

Some videos you may like

This Week's Hot Topics

Top