Data from several worksheets

I've read some of the existing questions/answers ..... and I'd like to ask.....

I'm using Excel 2002/XP. I currently have one file (Task List) with 6
worksheets (Areas of Responsibility) and four columns (Name, What, Start
Date, Deadline). Each row is a different task.

I would like to be able to retrieve my file and request that it gives me a
new screen with all the tasks with a deadline of a specific date.

Can this be done? If yes, how?
I'm relatively new to Excel and do not know much about visual basic codes.

Any help would be appreciated. Thanks.

--
Diane

Diane,

Your current spreadsheet architecture makes that difficult.

Instead, insert a new sheet, and on that sheet make 5 columns: Areas of
Responsibility, Name, What, Start Date, Deadline.

Then copy the tables from the 6 worksheets into the last four columns, one
below the other. In the first column, enter the name of the worksheet from
which you copied each section.

Do that for all 6 worksheets, then delete the original worksheets.

You will then have a database, and you can apply data filters to it, and
view your data any way you want, such as by deadlines of a specific date,
simply by selecting your table, then using Data | Filter... Autofilter.

HTH,
Bernie
MS Excel MVP


"DianeMcP" <[email protected]> wrote in message
news:[email protected]...
> I've read some of the existing questions/answers ..... and I'd like to
ask.....
>
> I'm using Excel 2002/XP. I currently have one file (Task List) with 6
> worksheets (Areas of Responsibility) and four columns (Name, What, Start
> Date, Deadline). Each row is a different task.
>
> I would like to be able to retrieve my file and request that it gives me a
> new screen with all the tasks with a deadline of a specific date.
>
> Can this be done? If yes, how?
> I'm relatively new to Excel and do not know much about visual basic codes.
>
> Any help would be appreciated. Thanks.
>
> --
> Diane

Hi Bernie,

Thanks very much for your reply.

I did want you suggested and I don't really have to apply any Data |
Filters, because sorting by Dates will also give me all the items required by
the same day. Sorting doesn't give me a separate screen/worksheet that lists
any one date, but it will arrange my list so that the dates are listed
together.

I haven't been able to understand the language/coding required for applying
filters. If I did, however, want a report/sheet with only a specific date,
what would I have to put and where?

Is there a listing somewhere of codes/syntax to be used .... hopefully with
some sample data sheets and sample reports?

--
Thanks,
Diane


"Bernie Deitrick" wrote:

> Diane,
>
> Your current spreadsheet architecture makes that difficult.
>
> Instead, insert a new sheet, and on that sheet make 5 columns: Areas of
> Responsibility, Name, What, Start Date, Deadline.
>
> Then copy the tables from the 6 worksheets into the last four columns, one
> below the other. In the first column, enter the name of the worksheet from
> which you copied each section.
>
> Do that for all 6 worksheets, then delete the original worksheets.
>
> You will then have a database, and you can apply data filters to it, and
> view your data any way you want, such as by deadlines of a specific date,
> simply by selecting your table, then using Data | Filter... Autofilter.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "DianeMcP" <[email protected]> wrote in message
> news:[email protected]...
> > I've read some of the existing questions/answers ..... and I'd like to
> ask.....
> >
> > I'm using Excel 2002/XP. I currently have one file (Task List) with 6
> > worksheets (Areas of Responsibility) and four columns (Name, What, Start
> > Date, Deadline). Each row is a different task.
> >
> > I would like to be able to retrieve my file and request that it gives me a
> > new screen with all the tasks with a deadline of a specific date.
> >
> > Can this be done? If yes, how?
> > I'm relatively new to Excel and do not know much about visual basic codes.
> >
> > Any help would be appreciated. Thanks.
> >
> > --
> > Diane
>
>
>

> ... If I did, however, want a report / sheet
> with only a specific date,
> what would I have to put and where?

One play which would deliver this ..

Assume the source data is now in Sheet1, cols A to D*, data from row2 down,
with the key col = col D ("DeadLine")
*Name, What, StartDate, DeadLine

Assuming 2 empty cols to the right, cols E & F

Put in F1: =IF(Sheet2!A1="","",Sheet2!A1)

Put in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))

Copy E2 down to say, E100
to cover the max expected data range in cols A to D

(Leave E1 empty)

In Sheet2
----------
A1 will be reserved for input of the deadline of interest
Input a date into A1, say: 01-Jul-2005

Paste the same col headers into A2:D2, viz.:
Name, What, StartDate, DeadLine

Put in A3:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A3 across to D3, fill down to D101
(cover the same range as in col E in Sheet1)

Format cols C and D as dates

Sheet 2 will auto-extract only the rows from Sheet1 where the deadlines are
equal to the date input in A1. These will be bunched neatly at the top, with
blank rows below.

Adapt to suit ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hi Max,

I've put all the coding in my columns, but how do I tell it to do the
extraction.
I've checked all the coding (and see that some of it changes based on the
column that it's in - nice to see that happen automatically). Is there a
button to press or pull-down command?
--
Thanks,
Diane

Update: I went back and noticed that the equal sign was missing from your
first command for F1. When not in those columns, there are numbers showing
up in them. In F1, I have "38520" showing. In F2, the number 2; in F6, the
number 6, and in F7, the number 7. Is this normal? What are they related to?
--
Thanks,
Diane


"DianeMcP" wrote:

> Hi Max,
>
> I've put all the coding in my columns, but how do I tell it to do the
> extraction.
> I've checked all the coding (and see that some of it changes based on the
> column that it's in - nice to see that happen automatically). Is there a
> button to press or pull-down command?
> --
> Thanks,
> Diane
>
>

> In F1, I have "38520" showing.

This is a date. If you format F1 as date, you'll see:17 Jun 2005

> In F2, the number 2; in F6, the number 6, and in F7,
> the number 7. Is this normal? What are they related to?

Think you've put the formulas in the wrong cells. All of the above F2, F3,
etc should be E2, E3, etc. The formulas are supposed to be In col E (from E2
down), not col F, re the earlier steps given:

> Put in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))
> Copy E2 down to say, E100 ..

Here's a sample file with implemented set-up:
http://flypicture.com/p.cfm?id=67949

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: DianeMcP_newusers.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> > In F2, the number 2; in F6, the number 6, and in F7,
> > the number 7. Is this normal? What are they related to?

Having taken care of the core bits earlier <g>, here's some additional
clarification. Col E in Sheet1 is a helper criteria column which returns
arbitrary row numbers for those rows in Sheet1 which satisfy the "filter"
criteria (i.e. the deadlines which match with the deadline of interest input
in Sheet2's A1) and returns blanks ("") for other rows which don't satisfy.
These numbers: 2, 6, 7, ... etc are then read by the formulas in Sheet2
(from row3 down) to extract rows which satisfy the criteria nicely to the
top over there.

> > Is there a button to press or pull-down command?

For Sheet2's A1, instead of keying-in the deadline of interest, we could
create a data validation (DV) dropdown to select the date.

Try this:

Insert a new Sheet3
Enter in A1, say: 01-Jun-05
Copy A1 down to A214 to generate sequential dates
all the way to 31-Dec 2005

With A1:A214 selected, click inside the Namebox*, input a name: DateList,
press ENTER (this creates a named range for use in the DV
*the box with the drop arrow just to the left of the formula bar

In Sheet2:
Click Window > Unfreeze Panes
(In Excel 97 - my version, DVs above/to the left of frozen panes will not
work. That's the reason for this precaution.)

Select cell A1
Click Data > Validation
Under Allow: select "List"
Put in "Source:" box: =DateList
Click OK

Now when you click on A1, there should be a droplist appearing for you to
select the deadline of interest

Here's a revised sample file with implemented DV droplist:
http://flypicture.com/p.cfm?id=67997

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: DianeMcP_newusers_2.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Just to clarify your thoughts below......I've put in "--->" to show my
replies amongst the answers below....
--
Thanks,
Diane

"Max" wrote:

> > In F1, I have "38520" showing.
>
> This is a date. If you format F1 as date, you'll see:17 Jun 2005

--->This is the column on Sheet1 where you told me to put in the coding.
>
> > In F2, the number 2; in F6, the number 6, and in F7,
> > the number 7. Is this normal? What are they related to?
>
> Think you've put the formulas in the wrong cells. All of the above F2, F3,
> etc should be E2, E3, etc. The formulas are supposed to be In col E (from E2
> down), not col F, re the earlier steps given:
>
> > Put in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))
> > Copy E2 down to say, E100 ..
>
> Here's a sample file with implemented set-up:
> http://flypicture.com/p.cfm?id=67949
>
> (Right-click on the link: "Download File"
> at the top in the page, just above the ads)
>
> File: DianeMcP_newusers.xls

--->My original workbook included different sheets. I took those sheets and
inserted them in column A as subjects, so I had to adjust your instructions
one column over. So I actually have Subject, Name, What, StartDate, Deadline
as my colunn headings in row 1.

I'm not sure if having that huge pulldown list for dates will be of any help.
It will sometimes take less time to enter a date than to find it on the
pulldown list.

My next question, however, is ......on Sheet two when, in A1, when I do
select a date, is there a special command to get a listing of deadlines for a
range of dates (say 17-Jun-05 to 4-Jul-05)?
--
Thanks,
Diane

> --->This is the column on Sheet1 where you told me to put in the coding.
> ... so I had to adjust your instructions one column over.
> So I actually have Subject, Name, What, StartDate, Deadline
> as my colunn headings in row 1.

Thanks for clarifying. If you had adjusted correctly earlier, then what's
described for col E will apply to col F. And the formula meant to be in F1
should now reside in G1 instead. F1 should now be left empty. And the
formulas in Sheet2 should now point to col F instead of col E, etc. Guess
I'm not really sure from your earlier comments whether the F1 to G1, and the
F1 left empty adjustment parts, etc were correctly done. In any case, think
the sample file provided ought to have clarified the suggested construct
sufficiently for any further adaptations to be done.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"DianeMcP" wrote
> I'm not sure if having that huge pulldown list for dates will be of any
help.
> It will sometimes take less time to enter a date than to find it on the
> pulldown list.

Well, you did ask for a pull down option, didn't you ? <g>
It was simply an illustration of what's possible with a DV droplist.

> My next question, however, is ......on Sheet two when, in A1,
> when I do select a date, is there a special command
> to get a listing of deadlines for a range of dates
> (say 17-Jun-05 to 4-Jul-05)?

Think we would need to revise the formula in the criteria col E in Sheet1 to
now pick out the defined range of dates instead of just a match for a single
deadline input in Sheet2's A1

For example:

Instead of putting in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))

Put in E2 something like:
=IF(D2="","",IF(AND(D2>=$F$1,D2<=$F$1+14),ROW(),""))
and then just copy E2 down as before

The above will now pick out all the deadlines in Sheet1's col D which fall
between the date entered in Sheet2's A1 up till and inclusive of a date 14
days ahead. The flexibility is hence there for you to specify by revising
the criteria formula in E2 to suit.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

The file that you linked to did indeed look like the results I was getting
(including the 38504 date code and the numbers in the rows that were the
resulting rows that were specified for Sheet2 A1).

On Sheet2, in A1, is there a way to specific a date range (say from
17-Jun-05 to 04-Jul-05)?

--
Thanks,
Diane


"Max" wrote:

> > --->This is the column on Sheet1 where you told me to put in the coding.
> > ... so I had to adjust your instructions one column over.
> > So I actually have Subject, Name, What, StartDate, Deadline
> > as my colunn headings in row 1.
>
> Thanks for clarifying. If you had adjusted correctly earlier, then what's
> described for col E will apply to col F. And the formula meant to be in F1
> should now reside in G1 instead. F1 should now be left empty. And the
> formulas in Sheet2 should now point to col F instead of col E, etc. Guess
> I'm not really sure from your earlier comments whether the F1 to G1, and the
> F1 left empty adjustment parts, etc were correctly done. In any case, think
> the sample file provided ought to have clarified the suggested construct
> sufficiently for any further adaptations to be done.
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

> On Sheet2, in A1, is there a way to specific a date range (say from
> 17-Jun-05 to 04-Jul-05)?

Posted the response below to your question earlier in the other branch:

Think we would need to revise the formula in the criteria col E in Sheet1 to
now pick out the defined range of dates instead of just a match for a single
deadline input in Sheet2's A1

For example:

Instead of putting in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))

Put in E2 something like:
=IF(D2="","",IF(AND(D2>=$F$1,D2<=$F$1+14),ROW(),""))
and then just copy E2 down as before

The above will now pick out all the deadlines in Sheet1's col D which fall
between the date entered in Sheet2's A1 up till and inclusive of a date 14
days ahead. The flexibility is hence there for you to specify by revising
the criteria formula in E2 to suit.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Sorry for the double questions being asked.....I don't see my question posted
for quite a while after a response.....so I end up posting twice. That will
stop.....I promise....
--
Thanks,
Diane

see "-->" inserts below.....
--
Thanks,
Diane


"Max" wrote:

> "DianeMcP" wrote
> > I'm not sure if having that huge pulldown list for dates will be of any
> help.
> > It will sometimes take less time to enter a date than to find it on the
> > pulldown list.
>
> Well, you did ask for a pull down option, didn't you ? <g>
> It was simply an illustration of what's possible with a DV droplist.
>
--->My pull-down was in relationship to how to get the command to start -
but that was also before I realized that one of the commands was missing the
"=" sign.
I do appreciate the extra option, though. It also explains to me how to use
something like this should I need it in the future. THANKS!

> > My next question, however, is ......on Sheet two when, in A1,
> > when I do select a date, is there a special command
> > to get a listing of deadlines for a range of dates
> > (say 17-Jun-05 to 4-Jul-05)?
>
> Think we would need to revise the formula in the criteria col E in Sheet1 to
> now pick out the defined range of dates instead of just a match for a single
> deadline input in Sheet2's A1
>
> For example:
>
> Instead of putting in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))
>
> Put in E2 something like:
> =IF(D2="","",IF(AND(D2>=$F$1,D2<=$F$1+14),ROW(),""))
> and then just copy E2 down as before
>
> The above will now pick out all the deadlines in Sheet1's col D which fall
> between the date entered in Sheet2's A1 up till and inclusive of a date 14
> days ahead. The flexibility is hence there for you to specify by revising
> the criteria formula in E2 to suit.

--->Great - I'll try that!
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com


--->Thanks VERY much for all your assistance. It's GREATLY appreciated.
Diane

You're welcome, Diane !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DianeMcP" <[email protected]> wrote:
.....
> --->Thanks VERY much for all your assistance. It's GREATLY appreciated.
> Diane

No prob, there may always be times when the "system" goes down <g>.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DianeMcP" <[email protected]> wrote in message
news:[email protected]...
> Sorry for the double questions being asked.....I don't see my question
posted
> for quite a while after a response.....so I end up posting twice. That
will
> stop.....I promise....
> --
> Thanks,
> Diane

Here's a new link to the revised sample file
with the implemented DV droplist:
http://www.savefile.com/files/6718156
File: DianeMcP_newusers_2.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Hope you can help. I'm trying to enter a formula to calculate year to date
variance from 2004-2005. Data for '04' is on another worksheet. How do I
enter the formula
I'm using Excel 2003.

Thanks
"DianeMcP" wrote:

> I've read some of the existing questions/answers ..... and I'd like to ask.....
>
> I'm using Excel 2002/XP. I currently have one file (Task List) with 6
> worksheets (Areas of Responsibility) and four columns (Name, What, Start
> Date, Deadline). Each row is a different task.
>
> I would like to be able to retrieve my file and request that it gives me a
> new screen with all the tasks with a deadline of a specific date.
>
> Can this be done? If yes, how?
> I'm relatively new to Excel and do not know much about visual basic codes.
>
> Any help would be appreciated. Thanks.
>
> --
> Diane

"sheri" <[email protected]> wrote in message
news:[email protected]...
> Hope you can help. I'm trying to enter a formula to calculate year to date
> variance from 2004-2005. Data for '04' is on another worksheet. How do I
> enter the formula
> I'm using Excel 2003.
>

In the cell you want the variance to appear, type "+" (without the quotes),
then click on the Year to Date total on the 05 sheet, then type "-" (again
without the quotes), then click on the 04 sheet tab, and click on the 04 YTD
total cell. Hit "enter". Job done!

Max,

I've got another question for you.......

If we then wanted to sort Sheet2 results based on A1's heading of Subject,
how can we do this? The headings from Sheet1 (A through E) have been copied
to row 2 in Sheet2. When we try to sort the A column (Subject), it sorts the
column but doesn't sort the rest of the columns. When we highlight the whole
sheet, it changes A2's heading from the column names to one of the subject
pulldowns.

Is there an easy way to get the results for the date we want (Sheet2:A1) and
then be able to sort by Subject (A column)? or choose other columns to sort
by column?

--
Thanks,
Diane


"Max" wrote:

> "DianeMcP" wrote
> > I'm not sure if having that huge pulldown list for dates will be of any
> help.
> > It will sometimes take less time to enter a date than to find it on the
> > pulldown list.
>
> Well, you did ask for a pull down option, didn't you ? <g>
> It was simply an illustration of what's possible with a DV droplist.
>
> > My next question, however, is ......on Sheet two when, in A1,
> > when I do select a date, is there a special command
> > to get a listing of deadlines for a range of dates
> > (say 17-Jun-05 to 4-Jul-05)?
>
> Think we would need to revise the formula in the criteria col E in Sheet1 to
> now pick out the defined range of dates instead of just a match for a single
> deadline input in Sheet2's A1
>
> For example:
>
> Instead of putting in E2: =IF(D2="","",IF(D2=$F$1,ROW(),""))
>
> Put in E2 something like:
> =IF(D2="","",IF(AND(D2>=$F$1,D2<=$F$1+14),ROW(),""))
> and then just copy E2 down as before
>
> The above will now pick out all the deadlines in Sheet1's col D which fall
> between the date entered in Sheet2's A1 up till and inclusive of a date 14
> days ahead. The flexibility is hence there for you to specify by revising
> the criteria formula in E2 to suit.
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Try making a frozen copy of Sheet2 (no formulas)
A couple of clicks will get us there ..

In Sheet2
Press CTRL+A (this selects the entire sheet)
Right-click > Copy

In a new sheet,
Right-click on A1 > Paste special > Values > OK
Right-click on A1 > Paste special > Formats > OK

Now we can sort as desired in the new sheet..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DianeMcP" <[email protected]> wrote in message
news:[email protected]...
> Max,
>
> I've got another question for you.......
>
> If we then wanted to sort Sheet2 results based on A1's heading of Subject,
> how can we do this? The headings from Sheet1 (A through E) have been
copied
> to row 2 in Sheet2. When we try to sort the A column (Subject), it sorts
the
> column but doesn't sort the rest of the columns. When we highlight the
whole
> sheet, it changes A2's heading from the column names to one of the subject
> pulldowns.
>
> Is there an easy way to get the results for the date we want (Sheet2:A1)
and
> then be able to sort by Subject (A column)? or choose other columns to
sort
> by column?
>
> --
> Thanks,
> Diane

Missed out one step ..

In the new sheet, after copy>pasting special as values/formats, we've got to
delete all the previously "blank" rows below the last row of data before
sorting. Just select the row headers for these, then right-click > Delete.
Then do the sort as per normal.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thanks Max,
That worked, once I also deleted the code that came from A1.
You're GREAT!
--
Thanks,
Diane

"Max" wrote:

> Try making a frozen copy of Sheet2 (no formulas)
> A couple of clicks will get us there ..
>
> In Sheet2
> Press CTRL+A (this selects the entire sheet)
> Right-click > Copy
>
> In a new sheet,
> Right-click on A1 > Paste special > Values > OK
> Right-click on A1 > Paste special > Formats > OK
>
Missed out one step ..

In the new sheet, after copy>pasting special as values/formats, we've got to
delete all the previously "blank" rows below the last row of data before
sorting. Just select the row headers for these, then right-click > Delete.
Then do the sort as per normal.

> Now we can sort as desired in the new sheet..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "DianeMcP" <[email protected]> wrote in message
> news:[email protected]...
> > Max,
> >
> > I've got another question for you.......
> >
> > If we then wanted to sort Sheet2 results based on A1's heading of Subject,
> > how can we do this? The headings from Sheet1 (A through E) have been
> copied
> > to row 2 in Sheet2. When we try to sort the A column (Subject), it sorts
> the
> > column but doesn't sort the rest of the columns. When we highlight the
> whole
> > sheet, it changes A2's heading from the column names to one of the subject
> > pulldowns.
> >
> > Is there an easy way to get the results for the date we want (Sheet2:A1)
> and
> > then be able to sort by Subject (A column)? or choose other columns to
> sort
> > by column?
> >
> > --
> > Thanks,
> > Diane
>
>
>

Glad to hear it worked for you, Diane !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DianeMcP" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max,
> That worked, once I also deleted the code that came from A1.
> You're GREAT!
> --
> Thanks,
> Diane