Newbie Question...

I'm using Excel 2003. I can create columns, rows, data, new worksheets, etc.,
so I have a very basic knowledge of using Excel.

With that said, here is what I'm trying to accomplish:


First of all, I'm the manager of several apartment complexes. We have
seperate worksheets for each complex. Each worksheet contains unit #, move-in
date, lease expiration, etc. Just general information. There are also columns
we mark 'R' for when we have requested that trash, paint, maintenance,
cleaning and carpet cleaning be performed. We then update the 'R' to 'X' when
they are completed.

What I'd like to do is create a new worksheet titled Maintenance and have
sections for Cleaning, Carpet Cleaning, etc. Each section would reflect the
units that need to be cleaned, for example, and also display the move-in
date. So if they are organized by move-in date, then we'll know which ones
are top priority, etc. The same needs to be done for carpet cleaning, etc.

So for each section (Cleaning, Carpet cleaning, etc), I'll need it to first
lookup the move-in dates. Then display the move-in date and the Unit number.
Also, a column that looks up whether the task (cleaning, carpet cleaning) has
been requested.


I know this is a complex question (or at least it is to me), but how would I
display that data?


Also, I've done a little bit of research online to find some Excel 2003
tutorials but I can't find any intermediate ones, per se. I can find very
basic ones and pretty advanced ones but none that seem to explain what the
LOOKUP function is used for, etc. Are there any sites you can suggest?


Thanks,
Ben

Can you upload a sample file/data* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Assuming that all the data pertaining to an apartment is in one row, copy
all data from every sheet into your Maintenance sheet.

Next sort the Maintenance sheet by move-in date then AutoFilter on the R/X
columns and you will have the Data displayed as you want it.

You could record a Macro copying more rows from each sheet than you will
ever need - say 20 rows - to the Maintenance sheet and then Sorting and
Filtering the Maintenance sheet but perhaps that is a bit too advanced for
you at the moment. Post baclif you need further help.


--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Benjamin M" wrote in message
...
I'm using Excel 2003. I can create columns, rows, data, new worksheets,
etc.,
so I have a very basic knowledge of using Excel.

With that said, here is what I'm trying to accomplish:


First of all, I'm the manager of several apartment complexes. We have
seperate worksheets for each complex. Each worksheet contains unit #,
move-in
date, lease expiration, etc. Just general information. There are also
columns
we mark 'R' for when we have requested that trash, paint, maintenance,
cleaning and carpet cleaning be performed. We then update the 'R' to 'X'
when
they are completed.

What I'd like to do is create a new worksheet titled Maintenance and have
sections for Cleaning, Carpet Cleaning, etc. Each section would reflect
the
units that need to be cleaned, for example, and also display the move-in
date. So if they are organized by move-in date, then we'll know which ones
are top priority, etc. The same needs to be done for carpet cleaning, etc.

So for each section (Cleaning, Carpet cleaning, etc), I'll need it to
first
lookup the move-in dates. Then display the move-in date and the Unit
number.
Also, a column that looks up whether the task (cleaning, carpet cleaning)
has
been requested.


I know this is a complex question (or at least it is to me), but how would
I
display that data?


Also, I've done a little bit of research online to find some Excel 2003
tutorials but I can't find any intermediate ones, per se. I can find very
basic ones and pretty advanced ones but none that seem to explain what the
LOOKUP function is used for, etc. Are there any sites you can suggest?


Thanks,
Ben

Max, thanks for the prompt reply. Here is my uploaded file:

http://www.nwadesignworks.com/LeaseBook.xls



"Max" wrote:

Can you upload a sample file/data* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Here's an idea which might appeal to you,
thoughts enclosed in this sample:
http://www.freefilehosting.net/download/3hj8k
Property Maintenance.xls

Assume identically structured source sheets: Gables, Gold Leaf, Lawson Sq,
etc
with key col = col M (MID), data from row 2 down
*MID = Move-In Dates

In a new sheet: M,
Create a DV list in A1 to select the property, eg: Gables

In B2:
=IF(OFFSET(INDIRECT("'"&$A$1&"'!F1"),ROWS($1:1),)= "","",OFFSET(INDIRECT("'"&$A$1&"'!F1"),ROWS($1:1), )+ROW()/10^10)
Leave B1 blank. This is the criteria col

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(OFFSET(INDIRE CT("'"&$A$1&"'!A:A"),,COLUMNS($A:A)+4),MATCH(SMALL ($B:$B,ROWS($1:1)),$B:$B,0)))

In D2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(OFFSET(INDIRE CT("'"&$A$1&"'!A:A"),,COLUMNS($A:A)-1),MATCH(SMALL($B:$B,ROWS($1:1)),$B:$B,0)))

In E2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(OFFSET(INDIRE CT("'"&$A$1&"'!A:A"),,COLUMNS($A:A)+7),MATCH(SMALL ($B:$B,ROWS($1:1)),$B:$B,0)))
Copy E2 to J2. Select B2:J2, copy down to cover the max expected extent of
data in the source sheets. Cols C to J will return only the lines with
specified MIDs from the source sheet selected in A1, with lines neatly
bunched at the top & sorted in chronologic order by MID dates.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Typo:
with key col = col M (MID),

should read:
with key col = col F (MID),
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You are soooooooooooooooooooooooo better off designing an MS Access database!

:-)

"Benjamin M" wrote:

I'm using Excel 2003. I can create columns, rows, data, new worksheets, etc.,
so I have a very basic knowledge of using Excel.

With that said, here is what I'm trying to accomplish:


First of all, I'm the manager of several apartment complexes. We have
seperate worksheets for each complex. Each worksheet contains unit #, move-in
date, lease expiration, etc. Just general information. There are also columns
we mark 'R' for when we have requested that trash, paint, maintenance,
cleaning and carpet cleaning be performed. We then update the 'R' to 'X' when
they are completed.

What I'd like to do is create a new worksheet titled Maintenance and have
sections for Cleaning, Carpet Cleaning, etc. Each section would reflect the
units that need to be cleaned, for example, and also display the move-in
date. So if they are organized by move-in date, then we'll know which ones
are top priority, etc. The same needs to be done for carpet cleaning, etc.

So for each section (Cleaning, Carpet cleaning, etc), I'll need it to first
lookup the move-in dates. Then display the move-in date and the Unit number.
Also, a column that looks up whether the task (cleaning, carpet cleaning) has
been requested.


I know this is a complex question (or at least it is to me), but how would I
display that data?


Also, I've done a little bit of research online to find some Excel 2003
tutorials but I can't find any intermediate ones, per se. I can find very
basic ones and pretty advanced ones but none that seem to explain what the
LOOKUP function is used for, etc. Are there any sites you can suggest?


Thanks,
Ben