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

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

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

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
----

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
----

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

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
----