Apr 8, 2013

Using Excel 2010

Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.

My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet

In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23

B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places

On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.

All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:

On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula

='1'!B23&" - "&'1'!H23

The first part is fine as B23 will always be a whole number

How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59

I have cell B59 set as a number cell with no decimal places but it still appears as 2.5

Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen

='1'!$B$23&" - "&'1'!$H$23

if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across.

View 4 Replies
View Related