fetch value of a cell, if its row contain a value > 0
Hello...,
I have 4 coloumns in a sheet called "Sales Report" (for each month, i keep seperate excel files)
A | B | C | D |
Name | Amount | Paid | Pending |
abc | 150 | 100 | 50 |
def | 200 | 200 | 0 |
ghi | 125 | 125 | 0 |
jkl | 100 | 75 | 25 |
need to track all the pending cash by the end of the month in a seperate sheet called "Cash Pending" with the following coloumns
A | B |
Name | Pending |
i need to scan the "Sales Report" sheet and find out which customers have Pending and get only those "customers name" to the "Cash Pending" sheet + the pending cash of that customer (here for eg; for customer abc, pending cash is 50)
any advice/suggestions ?
Last edited by creamecake; 02-07-2012 at 01:27 AM.
I would look at two things.
First, filter the first sheet with your criteria and then copy (or cut) the visible cells to the second sheet.
Second, look at advanced filter http://www.contextures.com/xladvfilter01.html
If neither of these are option for you, please provide a sample workbook with what you have and what you desire and how do you want the data to transfer to the second sheet. Is this something you do once in awhile or do you need it to happen whenever a new value is added to the first sheet?
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
hi jeffreybrown,
thanks for your comment. I need this "Pending Cash" sheet to be populated automatically. It should be updated whenever a "cash pending" occurs in the sheet "Reports".
the sample workbook is attached!
If there is no "Cash Pending" for a customer, i dont need his name on the "Pending Cash" sheet.
thx in advance
Try the attachment and let me know if it works.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
helloo bebo...
the formula is working in that attachment you had given to me as exactly i need. But when i copied this formula to my original excel, it is not working at all...
and i can see an "{" in your formula. I dont know about it! Previously also, when someone pointed me (in another case), this curly brackets "{" was there in a formula, and i was not able to copy and work with it.
attached the screenshot? how can i take this to my orig file...
With an array formula, you must confirm by Ctrl-Shift-Enter after typing, not single Enter only. By this, you can see {} at starting and ending of expression.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
thx for that info bebo!,
Now can u pls tell me the following regarding this formula;
1. in the attached screenshot (which is actually updated by you), in the formula of C6, it mentioned like this ....SMALL(POS,ROW(B1) what is this? and why it points to B1?
2. how can i take this formula to my orig file, which also has the same sheet name as Report ( i am attaching 2 screenshots of my orig file, which shows both sheets "Reports" and "Cash Pending"
pls advice me how to do this in original file...
POS is user defined name which you can see by Ctrl-F3.
=IF(Reports!$F$5:$F$31<>0,ROW(INDIRECT("1:"&ROWS(Reports!$F$5:$F$31))),"")
Adjust POS according to your actual file.
Instead of typing 1, ROW(B1) present number 1 which can be increased automatically by filling down
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Bebo................ Bunch of thanks
It is working for me.... really cooll.....
Can u pls help me to take out this error message (#NUM!) from the cash pending sheet... i am attaching the screenshot... pls look
I could not look at your formula from picture, but try:
In B5:
=IF(ROW(A1)<COUNT(POS),"YOUR ORIGIN FORMULA","")
Copy to C5 and D5
Copy down as far as you want.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
@bebo,
i just did as u advised. But it is not working for me... pls look @ this screenshot, i had shown what i had changed in B5, C5 and D5.
kindly advice...
Hic, "YOUR ORIGIN FORMULA" does not mean you put your origin formula between " "Originally Posted by creamecake
Try,
=IF(ROW(A1)<COUNT(POS),INDEX(...),"")
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Sorry for that. Actually i am a basic user to excel. Thx for correcting me...
Now there are few more problems in my scenario.
1. In the Name Manager, in the POS value, my current value is like this;
=IF(Reports!$F$4:$F$41<>0,ROW(INDIRECT("1:"&ROWS(Reports!$F$4:$F$41))),"")
But actually in the reports file, i had made 500 rows, inorder to accommodate any new customer added to our list. As currently we have only 38 customers which is aligned up to 41th row in report file, i am not able to mention up to 500 in the POS value. If i do so, it returns an error in the Cash Pending Sheet. you can see this in the attachment "from-original-file-Cash Pending - 3"
2. Ok, if i set the POS value up to 41 (F$4:$F$41), then in the output in the sheet cash pending, one entry is missing. I mean if customer 1001, 1003, 1007 and 1015 have pending cash, then the cash pending sheet will mention only 1001, 1003 and 1007 ( the last highest number will be omitted from the output). another example, if customer 1001, 1003, 1007, 1015, 1025 and 1029 have pending cash, it will report 1001,1003, 1007, 1015, and 1025.
Please advice...
send your example file...
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
ok! thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks