Hi Is it possible to group data together so that it stays together when I sort it alphabetically? I'd like to make a list for example, Police, Fire and Ambulance stations. In cell a1 would be Police followed in b1, b2, b3, etc the names of the stations. Then in c1,c2,c3 etc the relevant telephone numbers. Then in a 4 would be Fire, followed by b4, b5, b6 etc the names of the fire stations and then their tel numbers in c4, c5, c6 etc.The same applies to Ambulance. Then I'd like to sort col A and keep col b and col c together under the proper emergency service Hope you can underst...

Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

How do i make it so that the top row, row 1, stays at the top of my sheet no matter how far i scroll down the page? Many thanks Kev. Kev, Select cell A2 and then go to the Window menu and choose Freeze Panes. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "KMc" <mcauley@ourgeneration.freeserve.co.uk> wrote in message news:bf0re3$uoe$1@newsg4.svr.pol.co.uk... > How do i make it so that the top row, row 1, stays at the top of my sheet no > matter how far i scroll down the page? > > Ma...

Using the auto-filter function I need to count the number of rows returned each time I select a new value. I can do a COUNTA (–1 to allow for the header row), on the column that counts all the rows but when I use the filter I still get the total count not a count of the reduced quantity by the filter. Can anyone help? Nick use the subtotal function with a first argument of 3 =subtotal(3,A2:A300) as an example. Make sure the column you do the counta on will have values in all rows. -- regards, Tom Ogilvy "Nick" <ner@westnet.com.au> wrote in message news:15...

I have a spreadsheet with 742 rows (of company names). I have 5 columns of criteria for the companies. Each column is a different criteria about each company, and has a numerical rank for each company. 1-~75 (some columns/criteria have no rank for a company) I want to create an overall rank of the companies based on how they perform across the 5 rows. I want the companies with the most 1's at the top and then those with 1 & 2's and then those with 2 & 2's etc. Is there a way to sort or rank this spreadsheet of companies? I would do a sum of the inverses of each of...

I came accros a spreasheet were the first 8 rows are hidden. I can select and edit the cells by entering the cell reference into th Name Box. But I cannot make them visible - the rows have a height o 15. And selecting all the cells then selecting unhide does not work. Protection has not been applied. Does anyone have any ideas on how this was done? and how can I mak them visible? Thanks -- michael.a ----------------------------------------------------------------------- michael.a7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3302 View this thread: http:...

Hello guys, im very new to excel, and im trying to learn. I cant seem to figure out how to switch my chart around. I have a list of companies on the top, and down the left i have a list of locations. How can i make the locations go on top, and the companies down the left? -- jason57gizmo ------------------------------------------------------------------------ jason57gizmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30700 View this thread: http://www.excelforum.com/showthread.php?threadid=503627 Depending on the number of rows you're using--since t...

I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

does anyone know how to do a sort for how close a list of numbers com to a designated cell with a index number in that cell?? example: a 39 b 40 c 51 d 44 e 43 sort by being the same or the closest to "44" results should be: d 44 e 43 b 40 a 39 c 5 -- Message posted from http://www.ExcelForum.com Twosix, You can add a helper column... In the column next to that one (assume A1:A5) enter =ABS(44-A1) and drag down to fill the series. Select both columns and choose Data -> Sort and sort according to the helper column. Dan E "twosix >" <<twosix...

I am using Access 2003. I have created a database to help me files by "Lot#'s". Our lot numbers for one of our products is all over the place so I created the Lot # field as a text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02. Why does access put - 1000272925 before 10-06-02-03-05? Does this look right to anyone? My thinking is that "10" comes before "1000272925". What affect does the "-" have in sorting the numbers. Would I be better off to put a space instead of the "-"? Can anyone help me ...

Rather than using the control button to select every other row, which can be very time consuming when you have more than 100 rows, I am trying to figure out how to auto-insert blank rows between pre-populated rows of information. Deb, Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code in on the right . Change the sheet name to your sheet and run the code Sub insert_rows() Dim ws As Worksheet, x as long Set ws = Sheets("Sheet3") lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = ...

I have a datafeed that comes to me everyday via email , every day the feed had slighty different rows as it has different updated products i.e. somedays 999 , 1001 etc. What i need to do is run a macro so when the excel file arrives to count the number of rows and then add the letter "y" in a 3 new columns . At the moment i have to do it manually is this easy to do Paul iLastRow = Range("A1").End(xlDown) Range("B1").Resize(iLastRow,3).Value = "y" -- HTH Bob Phillips (remove nothere from email address if mailing direct) <plm11111@h...

Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

Hi, I'm looking to insert 'x' no. of rows into an excel document where 'x' is the result of a 'countA' function. Is this possible? Hi# only possible with VBA. Would this be feasible for you? >-----Original Message----- >Hi, >I'm looking to insert 'x' no. of rows into an excel document where 'x' is >the result of a 'countA' function. > >Is this possible? >. > Yes VBA is feasible "Frank Kabel" wrote: > Hi# > only possible with VBA. Would this be feasible for you? > > >-----Original ...

Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

I am having a difficult time sorting the following list: MY List How Excel Sorts I Need 2148-00 1472-00 171B-00 1480C-00 1480B-00 171C-00 1480B-00 1480C-00 1472-00 1472-00 171B-00 2148-00 171C-00 171C-00 1480B-00 171B-00 2148-00 1480C-00 I have tried formatting numbers as text and other options. I am at my wits end now. Is this sort even possible? Please help. Tania TVOUK Where is the logic to your sort please? If you can identify that then a sort may be possible! ...

I have a user who will have a phantom Excel spreadsheet, entitled Object, open at random times while she is trying to open another spreadsheet. This Ojbect spreadsheet will stay open, and she can open other spreadsheets, but the only thing that will open is the Menu bar, toolbars, and the status bar. The file itself doesn't open, the space is empty and the user can see the desktop. When this happens, the user has to shut down her machine, not a restart, before she can continue on in Excel. Any ideas? ...

can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. "Tami" <Tami@discussions.microsoft.com> wrote in message news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com... > can anyone write the formula to ...

I have a column of numbers that were imported from a web site and they won't sort correctly. I have tried reformatting the cells as general numbers but that doesn't change anything. I discovered that numbers below 100 have a space or invisible data in front of the number. If I remove it, the number then shifts to right alignment in the cell and will sort as a number. Is there any way I can remove these spaces from all numbers at once instead of going down the column row by row? Hi Fern One way Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of your data...

I get a "Dexterity Runtime has encountered a problem and must close" error when I try to run one particular modified report. The error occurs when I add a sort function in Report Writer. When I remove the sort, the report runs - without error. I am on GP 9.0. ...

I need to generate random whole numbers between the range of 21 and 90. I started with this: =rand()*90 with the cells formatted to zero decimal places. how do I limit the lower number? Any help here will be appreciated. Thanks in advance dave =RAND()*(b-a)+a taken from the Excel help file. So in your case RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21 In Excel 2007, = RANDBETWEEN(21,90) Tyro "Dave" <dave@accessdatapros> wrote in message news:56FD1420-58DC-4385-A784-C9964269B8F3@microsoft.com... >I need to generate random whole numbers...

What is the simplest way to enter a sequence of values such as =SUM (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? What is the simplest way of converting simultaneously such values to ($a$1:$a$100) etc.? I could only find a slow and clumsy method by putting the formula in a row, right dragging, then using F4 on each individual cell before using paste special with transpose. Roger PB Hi Roger, > What is the simplest way to enter a sequence of values such as =SUM > (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? > =SUM(OFFSET($A$1,0,ROW()-1,1,100...

I have a list of events and the probability that each will happen. Example: Event A 12% Event B 20% Event C 2% ...and so on. My list is about 20 entries long. What I want to do is to have Excel randomly determine what event will happen in what order, based on the percentage chance that it will happen. So I want 20 cells in another column with the results looking something like this: Event K Event B ...and so on. And be able to recalculate upon refresh. Thanks in advance!! Assuming the list is in A1:A20 - In cell C1 type: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$...

I have a strange problem with one of my spreadshteets. I am not able to access or see any rows 1 to 31. It is as if the "hide" function is in effect. I have tried the "unhide" function, but no use. It is as if I am locked out of these rows! I don't have any "freeze" function active with respect to the unaccesible rows. Anyone here knows what is going on? thanks When you try and unhide select the whole sheet by clicking the border above and to the left of cell A1 and then try unhide rows and then also try row heightr standard. -- Hope this helps M...