• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

nested if with goto statement

Simayan

Member
Hi,

Problem background:

If(condition,(true,false))

for true it will pick the data from the Database; else i.e. if false then it will go to next item to check condition and carry on.

How this can be sorted?

Thanks in advance
 
Hi,

If you look into the "Final Estimate-IO-PO sheet" in column N we have type of research.
Now based on column N we need to create separate sheet since cost centers are different.
Till now what we used to do is to copy the estimate no (I,e, Col A) and rest of data were pulled by vlookup.
What I want is to create a if statement with goto statement (i.e. Jump to next row for false value)
e.g.
In CBC sheet at A3 I want a formulla like
if('Final Estimate-IO-PO sheet'!N2="CBC",'Final Estimate-IO-PO sheet'!C2,else go & check for next row
This can be done by Index match ( which has been used already in "Final Estimate-IO-PO sheet" to extracft final data from "Estimate Tracker")
Any Alternative means is welcome
 

Attachments

  • Estimate-PO-Invoice Tracker_2014-15.xlsx
    210.3 KB · Views: 0
Simayan,

Paste this formula in Cell A2 of your CBC Sheet, and then press Ctrl+Shift+Enter (while your cursor is still in the formula bar):

=INDEX('Final Estimate-IO-PO sheet'!$C$1:$C$1000,SMALL(IF('Final Estimate-IO-PO sheet'!$N$1:$N$1000="CBC",ROW('Final Estimate-IO-PO sheet'!$N$1:$N$1000)),ROW()-2))

Then, grab the handle and drag it to fill as many cells as necessary.

I think it will do what you're asking...But only while your source list is <1000 rows long...You could solve that with some named ranges...

Isaac
 

Attachments

  • Simayan1.xlsx
    212.3 KB · Views: 0
Thanks. Very much required solution
Hi,

Now if I want to take the values which are not either "CBC" or "CPT"; how can I extend the same.

Tried the following but did not help ...any clue

=INDEX('Final Estimate-IO-PO sheet'!$C$1:$C$1000,SMALL(IF(AND('Final Estimate-IO-PO sheet'!$N$1:$N$100<>"CBC",'Final Estimate-IO-PO sheet'!$N$1:$N$100<>"CPT"),ROW('Final Estimate-IO-PO sheet'!$N$1:$N$1000)),ROW()-2))
 

Attachments

  • Simayan_Extended.xlsx
    212.5 KB · Views: 0
Hi Narayan,

The solution is working. But can You Please Guide or let me know how this MMULT formula works with a n example
 
Back
Top