Get numbers from row

[Therese]

Hi
I work in office 2003, and don't have acces to newer formulars. We have a
worksheet with names in column A and dates in row 2.
Allan (in A1)comes in from the 14th to the 20th, so in B14:B20 the numbers
14,15,16,17....20 appears.
Barbara (in B1)comes in from the 1st to the 12th and then again from the
17th to the 22nd.
In cell A32 we would like "14" to appear, and "20" in A33.
The same goes for row B; in cell B32 we would like "1" to appear and "12" in
B33. In B34 we would like to see "17" and "22" in B34.
Is this possible?
If it's too complicated to get two sets of numbers, we would be super-duper
happy to just have the first and last dates in A32.
Thanks
Therese

 

[FSt1]

hi
you've confused me a bit.
if you just have numbers in B14:B20 then in A32 put this formula...
=B14 and in A33 put =B20
If you have regular dates in B14:B20 then in A32 but this formula...
=Day(B14) and in A32 put =Day(B20)

post back if i have misunderstood.
Regards
FSt1

 

[Therese]

Well....the numbers changes downwards.
We have a rather large list of names and the numbers varies from name to
name...The persons comes in different days in the month. Clara comes from the
14th to the 19th, Dora from th 12th to the 13th etc.
Some comes in twice, but that may be too dificult, don't you think?
Do you think you can work it out ?

Ps. They are just numbers. The worksheet is called may.

Thanks
Therese

 

[FSt1]

hi
from what you have said i think the first formula will do what you want. i
simply draw the number from on cell to another. from where you have it to
where you want it.

Regards
FSt1

 

[Max]

Perhaps something simple like this ?
(it assumes you have numbers as per your ps within row 2 to row 31)

In A32: =MIN(A2:A31)
In A33: =MAX(A2:A32)
Select A32:A33, copy across as required

 

[Ron Rosenfeld]

Hi
I work in office 2003, and don't have acces to newer formulars. We have a
worksheet with names in column A and dates in row 2.
Allan (in A1)comes in from the 14th to the 20th, so in B14:B20 the numbers
14,15,16,17....20 appears.
Barbara (in B1)comes in from the 1st to the 12th and then again from the
17th to the 22nd.
In cell A32 we would like "14" to appear, and "20" in A33.
The same goes for row B; in cell B32 we would like "1" to appear and "12" in
B33. In B34 we would like to see "17" and "22" in B34.
Is this possible?
If it's too complicated to get two sets of numbers, we would be super-duper
happy to just have the first and last dates in A32.
Thanks
Therese

It's all probably possible, but you will need to describe your setup better.

For example, you write

Names in Column A; Dates in Row2

But Barbara is in B1 (which is column B, not column A)

 

[Max]

Typo, sorry .. line

In A33: =MAX(A2:A32)

should read as

In A33: =MAX(A2:A31)

---

 

[Therese]

ok. Well thanks. I don't think I have explaned understandable. I will try
again - but thanks.

Therese

 

[Therese]

Ha ha
Thanks Ron ;-)

It's all probably possible, but you will need to describe your setup better.

For example, you write

Names in Column A; Dates in Row2

But Barbara is in B1 (which is column B, not column A)

 

[Therese]

Hi
Thank you, this works very fine for the ones who only comes in once a month.
But will I be able to maybe combinate this formular with another, so that I
can get all four numbers...like : A32=14 A33=22 A34=28 A35=30 If Anne comes
in from 14th may to 22nd may, and the she comes again from 28th may to the
30th. As it is for now, A32=14 and A33=30 in the above example.
Therese

 

[Max]

It's difficult. Keep it simple, stick to one block of 30 input cells per
person per session (eg: A2:A31). You can repeat the construct below, in the
same col to allow for a 2nd session

 

[Sandy Mann]

There may be better ways but I would use a helper column.

Insert a new Column B. In B1 enter the formula:

=IF(A1<>"",1,"")

In B2 enter:

=IF(OR(AND(A2<>"",A3=""),AND(A1="",A2<>"")),COUNTIF($B$1:B1,"<>"&"")+1,"")

and copy down to B30

In B31 enter:

=IF(A31<>"",31,"")

In A32 enter:

=IF(COUNT(B1:B31)>=(ROW()-ROW($A$31)),LARGE($B$1:$B$31,COUNT($B$1:$B$31)-(ROW()-ROW($A$32))),"")

and copy down as far as you require, (it will work for as many different
periods as you want)

Note: if someone comes in for one day, it will only return one number not 2.

You can then hide Column B

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk