I have a large pivot that looks something like this
Number F1 F2 F3 F4 F5 Entry Status
2209 IM MM YES YES Yes YES
... and so on. with a large amount of data.
I am trying to use the IF Function to search over the entire range of data and IF the entry status is YES, then I want it to return values in the
Number F1 F2
.. columns and return those values at a seprate point(which would start at where I enter the formula)
any suggestions/help on this matter?
Thanks in Advance!
Ken.
Last edited by k3nparikh; 09-10-2009 at 04:10 PM.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Not a problem. Here are the before and after files. Due to the size limit its kind of messy, but it should give the general idea.
That's not a workbook!
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Here's One.
In E5, enter:
=IF(D5=$I$3,MAX($E$4:E4)+1,0) and copy down
In H6, enter:
=IF(ROWS($A$1:$A1)>MAX($E$5:$E$10),"",INDEX(A$5:A$10,MATCH(ROWS($A$1:$A1),$E$5:$E$10,0)))
and copy down as far as you want
in I6, enter:
=IF(H6="","",INDEX(C$5:C$10,MATCH(ROWS($A$1:$A1),$E$5:$E$10,0)))
and copy down same number of rows..
adjust all ranges to suit.
That was going fantastic until I noticed a small glitch, which was probably my fault in the first place. I see that you're using the incrementation and comparing it to the rows. The problem as you might be able to see in my sample workbook, is that there are spaces. Is there a way the formula can be edited so that the comparison is not between rows and the incrementation. Thanks for all your help!
Ken
It still works that way with the same formulas..
all you need to do is expand the ranges in the After section...
See Attached.
NOTE: In the attachment, "Entered" in I3 has an extra space at the end... for this to work, you must delete that space...
Okay, 99% There.. just one small bug shows up .. and I made a change in Sample Book to highlight the change I made.. and when that situation is encountered in my actual data file, there's a bug!
You have been of fantastic help, thank you so much!
Doh! Forgot to attach the sample file, nearing end of work day..
I assume the bug is you don't want items with no ID number to appear, even though they may have status "Entered"?
If so, just change the formula in E1 to:
=IF(AND(D5=$I$3,A5<>""),MAX($E$4:E4)+1,0)
and copy down.. and magically the After sheet adjusts...
Actually, believe it or not.. it's the other way around.. I want them to appear, with the co-responding ID NO. So, in the sample, Debra has been entered, under MM Category.. and her ID NO should be the same as that of Alan, 2206. So, if there is a cell, where it says Entered, but no ID NO, if it looks up the most previous/recent ID NO and returns that value, it should do the trick.
Ok, then...
Insert a column between D and E and place formula in E5:
=IF(D5=$J$3,LOOKUP(9.9999999999E+307,$A4:A$5),IF(D5="","",A5))
copied down...
Now in the new F5,
=IF(D5=$J$3,MAX($F$4:F4)+1,0)
copied down
and the new I6:
=IF(ROWS($A$1:$A1)>MAX($F$5:$F$30),"",INDEX($E$5:$E$30,MATCH(ROWS($A$1:$A1),$F$5:$F$30,0)))
and the new J6:
=IF(I6="","",INDEX(C$5:C$30,MATCH(ROWS($A$1:$A1),$F$5:$F$30,0)))
I haven't tried using it on my workbook as yet, but in the sample book you sent me, there were some red flags, I have highlighted them in Red.. as you can see.. the problem we're having is
2267 - Has not been entered, yet it shows up in the list along with category IM
3456 - Has been entered, but it shows up in the list that the Category registered is MM, wheras it should show up IM
6789 - Has been entered, but does not show up on the list.
Thanks!
I must admit I did that last one in a bit of a hurry as I wanted to give you something before I had logged off for the day...I do apologize for that carelessness...
This formula in E5 should fix the issues..
=IF(D5<>$J$3,"",IF(A5<>"",A5,LOOKUP(9.99999999E+307,$A$4:A4)))
copied down.
I am yet to test this out, I have been busy with other projects, however, I had a quick question if you don't mind answering.
Can VLookup return another table as a value, or does it always have to return a single value?
Vlookup returns a single value.
Ah, Thanks. I just had a assessment test which I got massacred in, I thought perhaps If I had tried to get VLOOKUP to return an array and nested it under another VLOOKUP, I might have been saved.. but I guess not.
Thanks! The formulae you gave me yesterday, work perfectly!
Just another quick question, the data I obtained, how do I copy it into a separate workbook. It copies the formulae instead.
not sure what you mean?
If I Highlight the results and Press CTRL+C and the Paste them in separate work book, it does not show me the ID NO and Category but instead, just the formulae in those cells. Anyway to trick excel in copying the value of the formula and not the formula itself?
Edit|Paste Special and select Values. Is that what you mean?
Yes! all done! thanks once again, I'm brain dead today, apologies.
No problem, neighbour
Please mark the thread as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks