nebie help please

  • Thread starter Thread starter hoole
  • Start date Start date
H

hoole

hi everyone .I am new to excel so please be gentle with me.
2 things.
I have a spreadsheet I that I " inherited " it is quite large and has
a number of sheets which produce results which are entered into the
first page. Is there a way of seperating the first page ( results page
) from the rest when all data is entered so I can email it easier, the
result page still has to have macros enabled and have all its
finctionallity .
I have one column of results ( R ) which might be
-21,-6,-2,13,15,18,19,34,46,68,89,104, etc and a variable number of
rows from 3 to 40 ,I need to make the total of this column =100% and
then each number = an equivalant proportion of the 100% how can I do
that
thanks in advance
hoole
 
1st part:
If the macros refer to the other sheets at all, you won't be able to do it.
But...
In case there's any formulas on your Results sheets, select all, select
copy, and then right-click, Paste Special, select values.
Now you could simply select all the other sheets (You can use Shift to
select an array of sheets) and delete them. Then email the single sheet.

2nd part: What rules guide what you can change? Can you just delete numbers?
Can I change them all?? Need more info

3rd part: To set all numbers equal parts of 100%, and if numbers are in
A2:A100
=100/COUNTA(A$2:A$100)
And place this formula into the B column next to your numbers.
 
Alternative for step 1:

Right Click on the totals tab and select Move or Copy...

Select (new book) from the drop-down. If you want to still have the total
page on your initial workbook as well, also click Create a Copy.

E-mail only the new workbook from then on out.
(For it to work happily, always update values when opening)

You MAY need to update your macro manually to look in the other workbook, so
it depends on what your macro is doing.

Not sure where a step 2 is requested...

For step 3, the below answer will make all have the same %.

in another column, =R2/SUM(R:R) and copy down. This will give % of total in
your new column as requested.

Of course, negative numbers make this interesting, since the total could
theoretically be 0.

To mitigate this, type =IF(sum(R:R)=0,1,R2/sum(R:R)) so that all values will
be 100% of 0.
 
Back
Top