Excel help - an expert is needed

[indigomatt]

Am in need of help or something to get me started and I may be able to do the rest... :D

I need to search a rectangle of excel that has discrete notations in it, say temperatures for example (noted as C3, C6, C4 etc…) randomly in differing rows and columns. Then I need to, on another workbook, in the same area (which I don’t mind copying over the outline etc…) write these temperatures but in 'words' from defined ranges (1 to 4 cool, 4 to 8 warm and 8 to 10 hot) ranges may change tho and in fact I've serveral things to convert like this (with different ranges and letters before and after a number code) but one the now would be better than none :D

Any pointers would be good...

M.

PS - mods to ma Mini will have to wait till the summer, as I've spent all ma monies :dead:

[Burple]

Alrighty.. the rectangle you want to search, is it say a constant range (for example A1 to G10), and these values are in random cells?
How big is the range of the 'rectangle'?
If it's not too big, you could easily do an embedded 'If' statement for the calculations on the second page, referencing the first. You'd only really have to work out the layout of the calculation once, as you could use the 'fill handle' to copy your formula to all the other cells you need to cover.

If you changed the values of your heat range, again, you'd only have to change the formula once, then use the fill handle to apply that to all the other cells..

Scared yet? Don't worry, it's reasonably easy.
I'll try and scribble a quick If statement this afternoon, if what I said above makes any sense to you?

:D

[Burple]

Right, are you sitting down? Here's one way that it should work...

The formula will be similar to this (if you decide to do it this way):

=IF(Worksheet1!A1>=8,"HOT",IF(Worksheet1!A1>=4,"WARM","COOL"))

To Explain... You're embedding 'if' statements to give a range of answers from the possible contents of one cell.
I'm assuming you have your temperatures in one worksheet, and the formulas in another in this case Worksheet1 is the name of the first, and Worksheet2 is the second. The name of the first worksheet IS important because that's what you put in front of the exclamation mark in the formula.
You should put this formula in the cells on the SECOND worksheet to mirror the first. So if your first (top left) value in sheet one is in cell A1, that's the cell reference that goes in your formula first.
The part that says "Worksheet1!" in the formula tells excel to reference the worksheet of that name, and pull the value in the cell specified AFTER the explamation mark (in the case above, 'A1') into the formula. Ok so far?
So what the formula effectively does is say, Ok, is the value in cell A1, from sheet 'Workstation1' greater than or equal to '8'? If it is, then the output will be 'HOT'.
OTHERWISE.. is the value in cell A1, from sheet 'Workstation1' greater than or equal to '4'? If it is, then the output will be 'WARM'.
OTHERWISE.. the output will be 'COOL'.
This will work so long as the temperatures you put in are within that range, if any hotter than 10 go in, then the answer will be hot, even if the temp is '200' - cos it;s still greater than 8, so that's the answer you've told Excel to give.

Now, the easiest way to copy this to all the cells you need is to use the 'Fill Handle' In the bottom right corner of the cell you've just put the formula in (the one containing the answer all going well), you should see a wee square, once you click on that cell to select it? Click on that wee sqaure (the Fill Handle) and drag it vertically down the page to mirror the number of cells containing temperatures in the first Worksheet. Now let it go. Excel *SHOULD* have copied your formula to each of those cells, and given you an 'answer' for each one. Now go back to the first cell, select it, and drag the fill handle out Horizontally as before, mirroring the number of cells. Once that's done, you can fill in the vertical columns to complete the cell range in the same way... <phew>

Hope this is of some help. :D:cool:

[Gismo]

Geek :p and i understand it :D

[indigomatt]

My head hurts :D

Will read over and see if it's of use...

The rectange is only a set width, it will get longer & shorter on differnt examples...

will be back, many thanks...

M.

[Burple]

quote:Originally posted by Bonnie Scotland

Geek :p and i understand it :D


:p:p:D:D:cool:
That's only a totty wee one. The example I teach has about 6 or 7 statements embedded. Dead easy as long as you remember all the brackets ;)



Matt, If you get the formula to work for one cell, the rest is easier than cutting and pasting! honest! :D