Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet functions - Ajit11021225
I have workbook named News.xls with 3 sheets namely Pricelist, Muster and
Bill for the month of January, 2005. Muster contains presenty of each client, marked P for present & A for Absent A.....B.....C.....D..... Days Sun Mon Tue Wed Date 1 2 3 4 1 John P A A P 2 Lucy A P P A 3 Pricelist contains prices for news papers (amount in Indian Rupees) A.....B.....C.....D.......E.........F.......G..... ..H... Days Sun Mon Tue Wed Thu Fri Sat Times 5 4 4 4 4 4 3.50 Economic 9 2 2 2 2 2 9 Now I want to prepare bills in sheet "Bill". I want Excel to check first from Muster whether client is marked as "P" on 1st of Jan, 05 (Saturday), if so, he should take a price of Times newspaper for Saturday from sheet Pricelist i.e. Rs.5/-, if not, put 0.00, & so on.. Thus my sheet will show : A.....B.....C.....D.....E.......F......G.......H.. ....I..Total Days Sat Sun Mon Tue Wed Thu Fri Sat Date 1 2 3 4 1 John 4 5 4 4 4 0 0 4 25.oo Note : On Thu & Fri John was marked as "A" How can I perform this in sheet Bill. -- Knowldege is Power |
#2
|
|||
|
|||
Ajit
If you put this IF formula into your worksheet "Bill" =IF(Muster!B3="P",Pricelist!B3,IF(Muster!B3="A",0, "CHECK MUSTER")) I have assumed for this that the first P for John on Sun 1st in your example is in cell B3, and similarly that the Price for Times on Sun 1st is also in B3 - this also assumes that John always takes the Times so uses the same row all of the time? I have given you a nested IF to add to this - this last part is for if neither "P" nor "A" has been entered and to give you a warning rather than just assume it can be blank or zero... Steve "Ajit Munj" wrote: I have workbook named News.xls with 3 sheets namely Pricelist, Muster and Bill for the month of January, 2005. Muster contains presenty of each client, marked P for present & A for Absent A.....B.....C.....D..... Days Sun Mon Tue Wed Date 1 2 3 4 1 John P A A P 2 Lucy A P P A 3 Pricelist contains prices for news papers (amount in Indian Rupees) A.....B.....C.....D.......E.........F.......G..... ..H... Days Sun Mon Tue Wed Thu Fri Sat Times 5 4 4 4 4 4 3.50 Economic 9 2 2 2 2 2 9 Now I want to prepare bills in sheet "Bill". I want Excel to check first from Muster whether client is marked as "P" on 1st of Jan, 05 (Saturday), if so, he should take a price of Times newspaper for Saturday from sheet Pricelist i.e. Rs.5/-, if not, put 0.00, & so on.. Thus my sheet will show : A.....B.....C.....D.....E.......F......G.......H.. ....I..Total Days Sat Sun Mon Tue Wed Thu Fri Sat Date 1 2 3 4 1 John 4 5 4 4 4 0 0 4 25.oo Note : On Thu & Fri John was marked as "A" How can I perform this in sheet Bill. -- Knowldege is Power |
#3
|
|||
|
|||
One way ..
Assumed set-ups in the 3 sheets, PriceList, Muster and Bill In sheet: PriceList, the table below is in A1:H3 Days Sun Mon Tue Wed Thu Fri Sat Times 5 4 4 4 4 4 3.5 Economic 9 2 2 2 2 2 9 In sheet: Muster, in cols B to AG, from row1 down, you have: Days Sun Mon Tue Wed etc Date 1 2 3 4 .. 31 John P A A P .. etc Lucy A P P A ... etc etc In sheet: Bill in cols A to AG, from row1 down is: <DV Days Sat Sun Mon Tue etc ------- Date 1 2 3 4 etc (till "31" in AG2) 1 John 2 Lucy (Customer names: John, Lucy start in B3 down) In A1, make a data validation list (DV) to select the paper: Times, Economic Select A1 Click Data Validation Under Allow, select List Put in 'Source" box: Times, Economic Click OK Put in C3: =IF(INDEX(Muster!$A:$AG,MATCH($B3,Muster!$B:$B,0), MATCH(C$2,Muster!$2:$2,0)) ="P",OFFSET(PriceList!$A$1,MATCH($A$1,PriceList!$A :$A,0)-1,MATCH(C$1,PriceLi st!$1:$1,0)-1),0) Copy C3 across to AG3, fill down by as many rows as there are names in col B For the sample data in Muster, with "Times" selected in A1, you'll get: Times Days Sat Sun Mon Tue ------- Date 1 2 3 4 1 John 3.5 0 0 4 2 Lucy 0 5 4 0 And if "Economic" is selected in A1, you'll get: Economic Days Sat Sun Mon Tue ------- Date 1 2 3 4 1 John 9 0 0 2 2 Lucy 0 9 2 0 (assuming the sample "P" indications for John and Lucy in Muster also apply for the paper: Economic) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ajit Munj" wrote in message ... I have workbook named News.xls with 3 sheets namely Pricelist, Muster and Bill for the month of January, 2005. Muster contains presenty of each client, marked P for present & A for Absent A.....B.....C.....D..... Days Sun Mon Tue Wed Date 1 2 3 4 1 John P A A P 2 Lucy A P P A 3 Pricelist contains prices for news papers (amount in Indian Rupees) A.....B.....C.....D.......E.........F.......G..... ..H... Days Sun Mon Tue Wed Thu Fri Sat Times 5 4 4 4 4 4 3.50 Economic 9 2 2 2 2 2 9 Now I want to prepare bills in sheet "Bill". I want Excel to check first from Muster whether client is marked as "P" on 1st of Jan, 05 (Saturday), if so, he should take a price of Times newspaper for Saturday from sheet Pricelist i.e. Rs.5/-, if not, put 0.00, & so on.. Thus my sheet will show : A.....B.....C.....D.....E.......F......G.......H.. ....I..Total Days Sat Sun Mon Tue Wed Thu Fri Sat Date 1 2 3 4 1 John 4 5 4 4 4 0 0 4 25.oo Note : On Thu & Fri John was marked as "A" How can I perform this in sheet Bill. -- Knowldege is Power |
#4
|
|||
|
|||
Drop me a line if you'd like a sample book
with the set-up implemented Either: demechanik <atyahoo<dotcom, or xdemechanik <atyahoo<dotcom -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Thanks Max! Though I have not checked the functions,It's gr8 help. Thanks a
lot again. Pl. send me the sample book, if possible, I will be obliged for the help. Ajit "Max" wrote: Drop me a line if you'd like a sample book with the set-up implemented Either: demechanik <atyahoo<dotcom, or xdemechanik <atyahoo<dotcom -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
You're welcome, Ajit !
Send me an email* so that I can attach the sample book in reply (your email address below doesn't seem valid, going by the mail failure/rejection received) *To either: demechanik <atyahoo<dotcom, or xdemechanik <atyahoo<dotcom -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ajit Munj" wrote in message ... Thanks Max! Though I have not checked the functions,It's gr8 help. Thanks a lot again. Pl. send me the sample book, if possible, I will be obliged for the help. Ajit |
#7
|
|||
|
|||
well, if you're still reading this thread,
you could also post a "readable" email add here (unable to send the sample book w/o a valid add) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
worksheet functions | Excel Discussion (Misc queries) | |||
worksheet functions with dates | Excel Worksheet Functions | |||
How to call worksheet functions from Access ? | Excel Worksheet Functions | |||
How use group and ungroup functions while worksheet is protected | Excel Worksheet Functions |