Table with formula

[Jman]

I need a formula. I need to place c "Time" in appropriate time table.
Corresponding with the appropriate date they came in.

On Sheet 1.
In Column B1:B2000 I have dates.
Columb C1:C2000 i have time" 60" meaning 60 minutes.

On Sheet 2

In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90
minutes and 120 minutes.

On the right side of the table In Column P2 all the way down i have fixed
dates 1/1/08 dragged down all the way to end of year. ( i do not change
these dates.)

What i want is when i enter date in Sheet1 and the time i want to
automatically be entered coresponding in the correct column and date.
But insead of transfering the exact number ex "75" to the severyfive minutes
column and date. I want it to only count as 1.

 

[Stefi]

My suggestion is to reformat your Sheet 2 a little bit:


B C D E ... P

2 60 75 90 120 Dates
3 FORM. 1/1/08

Then place this formula in B3 and copy it to the right until E3 and down to
the end of the year. It's not clear what you have in columns F:H.

Regards,
Stefi

=SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2))


„Jman†ezt írta:

 

[Jman]

Your formula worked perfectly...
Thanks Much

I have a different question not related to the above.

If in A4 i have an error message "#value!" how can i get a1 to diplay
blank..

I am am using this formula =mode(a1:a16)
But in A4 i have "#value!" resulting from another formula that i can not
delete.
How can i have
this formula ignore the "value!" sign. and count the commn denominator
between a few numbers.

 

[Jman]

TYPO;; I MEAN TO SAY

If in A4 i have an error message "#value!" how can i get A4 to diplay
blank..

 

[Stefi]

E.g. =VALUE("sss") returns "#value!"

Complete your formula in A4:
=IF(ISERROR(VALUE("sss")),"",VALUE("sss"))
Regards,
Stefi


„Jman†ezt írta:

 

[Jman]

It works well if there is no formula in the cell,
Is it possible to make it work if i already have another formula in the cell.
ex. =weekend(A4,2)
Can i combine both formulas, and if yes, how. You can use the above formula
if you want to give an example.

 

[Stefi]

1st: You can't have a formula in A4 referring to A4.
2nd: There is no weekend() function built in Excel. Is it a UDF or perhaps
do you mean WEAKNUM()? In the latter case I couldn't make WEAKNUM() return
#VALUE.

Please review your formula in A4!

Stefi



„Jman†ezt írta: