Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old July 15th 05, 03:52 PM
Gordon
 
Posts: n/a
Default

"sheri" wrote in message
...
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!



  #22   Report Post  
Old July 25th 05, 08:32 PM
DianeMcP
 
Posts: n/a
Default

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 <atyahoo<dotcom
----



  #23   Report Post  
Old July 26th 05, 12:34 AM
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"DianeMcP" wrote in message
...
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



  #24   Report Post  
Old July 26th 05, 12:42 AM
Max
 
Posts: n/a
Default

Missed out one step ..

In the new sheet, after copypasting 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 <atyahoo<dotcom
----


  #25   Report Post  
Old July 26th 05, 03:16 PM
DianeMcP
 
Posts: n/a
Default

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 copypasting 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 <atyahoo<dotcom
----
"DianeMcP" wrote in message
...
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






  #26   Report Post  
Old July 26th 05, 04:54 PM
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"DianeMcP" wrote in message
...
Thanks Max,
That worked, once I also deleted the code that came from A1.
You're GREAT!
--
Thanks,
Diane





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match and index functions: corrlating data from 2 worksheets [email protected] Excel Worksheet Functions 2 May 21st 05 05:38 AM
Want to plot a graph using data from 2 different worksheets in sam cteq Charts and Charting in Excel 5 February 7th 05 10:55 PM
Comparing data in two similar worksheets HiRllr21 Excel Discussion (Misc queries) 0 February 2nd 05 05:01 PM
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 05:15 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 05:19 PM


All times are GMT +1. The time now is 02:12 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017