Data from several worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
sheri said:
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.
Click to expand...

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?
 
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..
 
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.
 
Thanks Max,
That worked, once I also deleted the code that came from A1.
You're GREAT!
--
Thanks,
Diane

Max said:
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
Click to expand...
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..
Click to expand...
 
Back
Top