Active discussions

Meet the Other Phone. Flexible and made to last.

Meet the Other Phone.
Flexible and made to last.

Buy now

Please or to access all these features

Talk

Back to thread
Original poster

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

6 replies

Katymac · 28/09/2007 15:52

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

Go to post
MNHQ

Tech · 28/09/2007 16:24

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...

MNHQ

Tech · 28/09/2007 16:26

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

MNHQ

Tech · 28/09/2007 21:11

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.

MNHQ

Tech · 28/09/2007 21:17

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.

MNHQ

Tech · 28/09/2007 21:20

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]

MNHQ

Tech · 28/09/2007 21:28

To be honest, I don't think you can do what you want automatically without writing a macro, which means a bit of basic programming. I think conditional formatting only lets you choose three conditions, so in your case you could have up to three colours but not sixteen different colours. You can of course set the colours manually, but that's not much help..... sorry.

Watch this thread for updates

Tap "Watch" to get all the latest updates

End of posts

There are no more MNHQ posts on this thread

Back to thread