Another Excel Query.

[Taff]

I have a list of reference numbers which is 580 rows deep. All numbers finish in either /1 or /2 etc.

I have created another column for each office eg /1 is Office 1, /2 is Office 2 etc but how can I get it so that Excel will automatically enter "Office 1" when it sees "/1" etc? Once I've got that I can sort the list in Office order.

I'm sure there's an easy way to do this, and I'm determined to find it.

 

[Pip_UK]

Hi Taff, maybe try this formula in the cell you want the office number text:

="Office "&RIGHT(cellref,1)

Where cellref is the cell (e.g. A1) which contains your first reference number.

The RIGHT function returns the last n characters (1 in this case, as you only need the last digit) from the text in cellref, and the '&' glues it on to the text in quotes.

Then click/drag the fill down widget at the bottom right of the cell, to replicate the formula for all your rows, whilst automatically changing the cell references.

HTH
-Pip

 

[hops]

not sure

if this is exactly what you want but if you select the top row in your spreadsheet which contains your column headings, by clicking once on the corresponding number in the left side margin, (the row number) then go to the 'data' tab in the top menu bar and further select 'filter' then 'autofilter' this will insert little drop down arrows in your headers. By using the appropriate columns arrow you can then get it to show items in any order you wish from that column by selecting the desired criteria.


It sounds complicated but its not really, give it a try, if its not what you're after just close the spreadsheet and don't save it. Simples!

 

[Taff]

Hi Taff, maybe try this formula in the cell you want the office number text: ="Office "&RIGHT(cellref,1) Where cellref is the cell (e.g. A1) which contains your first reference number.

Brilliant. Never seen that before but it worked at the first attempt. Saved me hours of fannying around.

... select the top row in your spreadsheet which contains your column headings, by clicking once on the corresponding number in the left side margin, (the row number) then go to the 'data' tab in the top menu bar and further select 'filter' then 'autofilter' this will insert little drop down arrows in your headers. .... It sounds complicated but its not really

It wasn't quite what I wanted, but I can use it for something else. Give yourself a Brownie point.