IF Excel Function or Macro?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I would like to automate the population of cells thru an
IF worksheet function. However, a nested IF Function
allows only a limited number of times it can be done. Here
is the formula that I tried:

=IF(Work Request = 99999, "99999", IF(Work Request =
99996, "99999", IF(Work Request = 217351, "11111", IF(Work
Request = 197010, "11111", IF(Work Request =
197018, "11111", IF(Work Request = 197025, "11111", IF
(Work Request = 197036, "11111", IF(Work Request =
197049, "11111"))))))))

The problem that I have is that there are about 40 work
request numbers in total.
Can this be done thru a macro instead? If so how or is
there another excel function I can use?

Thanks.

Bob
 
You would probably be MUCH better off to create a table
showing:

Work request Cell Value

Then using Vlookup to return the required Cell Value for
the Work Request number

BAC
 
Assuming the Work Request no's and associated values are
essentially constant, you could create a table and use
one of the LOOKUP functions.

It would certainly be easier to maintain than e.g. a
series of case statements in a macro.

Julian
 
Hi Bob,

Have you considered using the vlookup function.

In another sheet you can have the following in
column a = work request number
column b = corresponding number

Use the following formula in the cell you want auto populated
=VLOOKUP(A1,Sheet2!A:B,2,0)

"A1" is the cell containing your work request number
"Sheet2!A:B" is the telling excel to look at the sheet and columns yo
set up above
"2" is telling excel to return the value from the 2nd column
"0" is telling excel to look for an exact match.

Hope this helps.

Jef
 
Would a Vlookup work where you lookup a the work request
number and populate the result that way?
 
Thanks for the quick respones. I've never used the vlookup
function. So then I would need another sheet with both
columns of data and then use the vlookup function to
populate?
 
Back
Top