Help Excell stuff again.....I am so thick[blush]

[Katymac]

OK I have numbers 1-75; each one has a number 1-16 next to it

the numbers 1-16 respond to 16 statements

I want the right statement in the box next to my origional 75 numbers......any idea how?

tia
KMc

I think you need Find and Replace in the Edit menu.

Find - your number

Replace with - the associated statement

Do that 16 times for numbers 1-16.

That's great

But it replaces the 1 in 13 with the phrase so I need a way to make 01 show - I think?

OK - there's a wee button in the dialogue box saying "options". click that, then one of the options you're given is "match entire cell contents"

If you tick that it should only work for the full number IYSWIM.

OK I am doing it that way

It is a long slog

I thought there might be an easier way

you should only have to do it 16 times

Yeah 16 times - there is a "replace all" option so you do all your 1s at the same time etc

Yep 16 times for each spreadsheet

Maybe 20 or so spreadsheets

Well, at least you don't have to type all the statements I guess... tis deathly dull though.

There is an easier way if you can be bothered......

Somewhere else on the sheet, or on another sheet, make a two-column table going
1 Statement
2 Statement
3 Statement

etc in order for all 16 statements.

Select that grid and do Insert, name, define. Choose a name - say "data"

If your other list looks like

1 3
2 4
3 15
....
75 4

in columns A and B, then in cell C1 (for example) type =vlookup(B1,data,2,false)

That says to excel "lookup the value in B1 in the table called 'data', and take whatever is in the second column and put it here".

But that's not easier you cry. It's a right palarver. It becomes easier if you have to make any subsequent changes to your questions, so if you spot a typo or something - you only have to fix it in the "data" list rather than searching for it repeatedly...

oh and then copy and paste the formula down the rest of the column of course....

omg Tech, lookups!!

I had totally forgotten about them!!!!

How terribly exciting

If you don't use Tech's option (which is the best btw ) and you want to search and replace, the other option (instead of selecting entire cell contents option) is to start by replacing 16 with the phrase and work backwards to 1. (then by the time you get to 1 all the other 1s (i.e. 10-16) have already been replaced.

good point. I love excel

That is brilliant

That is just what I need (I think)

As I have lots of tables to do this for (IYSWIM)

So 1 master table data

and 1 formula to copy & paste (if I understood right?)

Yep, you only need the one data list.

OK - but it isn't pasting the colour/font

How do I do that?

No solution

I was sure Tech would come up with somehing?

Errm, you just need to apply the same formatting as you had on the originals. So click in the square that has the colour/font you want and do Edit, Copy. Then select the cells you want to format and do Edit, Paste Special. From the dialog box that opens up choose format. This will copy over the cell formatting without changing the cell contents.

The lookup thing only operates on the "values" in the cells, not what they look like. If you want to apply formatting you need to do that separately.

But that still gives me 20 spreadsheet with 75 or so entries to copy & paste doesn't it?

Each of the 16 is a different colour

Only if each and every cell has a different format. You can paste into all 75 cells in one go if you want them all the same format (colour font size etc). Without knowing what you are trying to do, I'm not sure exactly what you mean really, sorry. Does each question have a different set of colours / formats? If so then yes, maybe you might have to paste separately.

You could check the excel help on "conditional formatting" - that lets you apply different formats depending on the value in the cell.

Just out of interest, why is each question a different colour? What's the spreadsheet for? [nosy icon]

My 16 values/phrases on the Data file have 16 colours

So on my list of 75 which need phrases in the right colour

That doesn't help much does it?

It is for recording the implimentation of 'Birth to Three' so that OFSTED will love me

But if I get it right it will also help with spreadsheet timetabling

I'm a massive Excel nerd, want a hand?