Posts by bigbob

filter mode

if you use the SUBTOTAL function on your range of data you will be able to show the count , average and sum of a filtered range as well as eight other statistics depending on the argument you use in the function

select cell just under headigs then go to windowsv menu and select freeze panes

Hi
Has all your data got the same number of letters at the start If so you could put a formula into a cell such as =LEFT(A2,2) which should pick out the first two characters and for the numbers the formula would be =RIGHT(A2,LEN(A2)-2)
Hope this helps
Bob

Hi
Insert another sheet into the workbook and use copy and paste to put the three results sheets onto one page
Big Bob

Hi
If in cell C1 you put the following formula =IF(ISBLANK(B1),"",B1-A1) and copy it down as far as needed it should do what you want
Big Bob

Is autofilter not enough
Big Bob

Hi
Using the drawing toolbar create a circle of sufficient size in your worksheet
Then put one team at the centre of the circle and evenly space out the remaining teams around the circumference For the first week draw lines from the centre to a team and then draw five lines at right angles to that one connecting the two teams either side of the first line then the next two and so on
For the second week move around the circle by 1/11th and repeat and so on for the remaining weeks For the second half of the season swap the home and away teams
HOPE THIS HELPS

Hi
If you put =RAND() in the column next to the numbers 1 to 50 and copy it down then you can do a sort on the column containing the random numbers which willchange the order of the column containing 1 to 50
Big Bob

Hi
Why not put the subtotal formula above your list of data and either use a range very much greater than you are using or probably a better idea use the COUNTA function to get the number of lines of data
HTH Big Bob

Hi
The only way I have found is to do this is to increase the zoom factor on the standard toolbar .Hide some columns temporarily so that this is possible
HTH
Big Bob

use autoshapes for hyperlinks not cells

Why is it marked solved when there have been no replies please and as a second thought how do I bring VIEWS to my computer Sorry if i'm being slow but ??

Hello ,
I have a block of hyperlinks arranged as a 5 * 5 square as an index to a worksheet . I have sorted the syntax for the hyperlinks but when I want to return to the index using the BACK button on the web toolbar if I had picked something in the third row then the first two rows of my square are hidden .Is there a way to tell EXCEL that I want to be able to see all of my 5 * 5 square.

Hi
Not sure of the correct syntax but INDIRECT would be my way of starting

=a2-a1 and format the cell as general otherwise Excel will think you want a date then you may need to round the result

Hi,
Dates and times are stored as decimal numbers so midday =0.5 Also 6 a. m. would be stored as 0.25 To this must be added an integer representing the number of days since Jan 1st 1900 .If you use the relevant numbers in your countif formula you should get the correct answers HTH
Big Bob

=end time - start time will give right result the cells are formatted to show a time but the cells actually contain decimal parts of a day so b1-a1 would give you the time taken in parts of a day . *24*60 to give the time in minutes .
HTH

INT(A1)=A1
Is this what you want

Hi If you have titles inrow1
If you have the months in column A ,the sales in column B then the following formula in C2 and copied down will give you 30% commission while the sum of sales is less than or equal to 60000 and will give you 50% commission on all sales above 60000
=IF(SUM($B$2:B2)>60000,(SUM($B$2:B2)-60000)*0.5+18000,SUM($B$2:B2)*0.3)
The 18000 is 30% of 60000
Hope this is what you need
Big Bob

Excel only keeps the first 1000(one thousand) unique entries in an autofilter list .Used =row(a2) and copied down with headings in a1