Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie Question...
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 --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie Question...
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 --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie Question...
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 --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie Question...
Typo:
with key col = col M (MID), should read: with key col = col F (MID), -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Newbie Question...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
newbie question | New Users to Excel | |||
Newbie Question | Excel Discussion (Misc queries) |