# Applying a forumla to a column until its not needed

1. ## Applying a forumla to a column until its not needed

Hi,

Hoping this might be a real simple one...

I'm trying to apply formula to a column but to get it to stop when there is no value for it to apply to. So when the 23rrd cell in column E is blank to just ignore it (I don't want N/A to show)

This is my formula:

LOOKUP(D3,Customers!A\$2:A\$15,Customers!D\$2:D\$15)

I tried:

=E:E=LOOKUP(D3,Customers!A\$2:A\$15,Customers!D\$2:D\$15)

but that just sends back "FALSE"

2. I expect a dynamic named range is what you want (many examples can be found here by searching), but there's no reference to a column E cell in the formula ...?

3. Hi,
try the error function
here's one example
http://www.freewebs.com/davesexcel/V...20Example2.xls

4. Yeah, sorry, I've tried searching the forum but its not working at the moment.

I just want to apply the formula

LOOKUP(D3,Customers!A\$2:A\$15,Customers!D\$2:D\$15)

to every cell in column E UNTIL there is no value in column D.

I'm recording a macro but the date I'm applying this formula to will vary in number of rows and the value "N/A" messes up a sum formula in another column.

Hope this makes some sense. Sorry If I'm not explaining myself very well.

5. Cheers....AWESOME!

Think I've cracked it

Thanks guys..

6. The information you provided was very helpful and resolved my issue with the Look Up formula error. However when I tried to use the same idea with my IF formula...it didnt like it!

=IF(I3="In Stock",H3,(IF(I3="Out of Stock",0,"")))

i tried

=IF(ISERROR(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))),"0",(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))

but as i say..it didn't like it. is it something to do with 2 IFs?

any tips?

thanks

7. Originally Posted by Russ3578
The information you provided was very helpful and resolved my issue with the Look Up formula error. However when I tried to use the same idea with my IF formula...it didnt like it!

=IF(I3="In Stock",H3,(IF(I3="Out of Stock",0,"")))

i tried

=IF(ISERROR(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))),"0",(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))

but as i say..it didn't like it. is it something to do with 2 IFs?

any tips?

thanks
you do not really need an If(iserror() statement for this situation,
Maybe...untested though...
=if (i3="","",IF(I3="In Stock",H3,IF(I3="Out of Stock",0,"")))

8. yes u r right....

thanks. no help needed. .. well not formulaic help anyway!

haha

thanks for ur previous help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1