Any Excel Boffins Out There?

[unclebatman]

Help!
I'm in the process of creating a spreadsheet, and only have basic knowledge of formulas etc.
Difficult to explain what I need, but say I have a row which is given a category by my picklist - can I automatically select a cell from that row and automatically filter it into a formula?

Example

1 2 3
A xxx xxx yyy
B xxx xxx yyy
C xxx xxx yyy


I've selected row A as a certain category from the list, but now I've done that, I want to show the value of yyy (A3) somewhere else in the worksheet.

God I hope that makes sense!
Anyone?

 

[auction294]

bluesoup is your man for that, actually it's her and not him. Leader of some tree hugging council's IT dept somewhere in he North of England and a true whizz with anything Microsoft Office releated. I'll pm her and cajole her to help, or bully whichever works quickest.

 

[unclebatman]

Magic, thanks 294!

 

[auction294]

no prob mate,

are you trying to add the numerical value of A1 + A2 and then show the result of in cell A3 as a value somewhere in the worksheet?

so let's say, colum A row 1 has a value of 1, and Column B row 1 has a value of 2. Do you want to show the added total of both cells ; A1 & B1 in cell C1? if so it's pretty straightforward; =sum (A1:B1) you can put that value in any cell on the worksheet and the desired result will display in that cell. See the screen shot below

also, should have asked is it Office 2003 or Office 2007?

 

[unclebatman]

Bit more complicated that that.

I'm already quite comfortable with simple formulas etc - so this bit isn't the problem.
What I've done is made a pick list for each row, and I want to be able to select a value of a particular cell within that row once I've chosen the option from the picklist. Very difficult to explain, but I'm sure it would be really easy to do for anyone with even an intermediate knowledge of the product!

 

[auction294]

okay mate, I pm'd bluesoup this morning and I'm sure if it's possible she can do it.

 

[Ed68]

I don't have excell on this laptop so can't fully answer 'cos I can't test it.

If your looking to grab the first three digits in the cell you can use: =left(a1,3)
If your looking to grab the last three digits in the cell you can use: =right(a1,3)

If you want the middle digits I can't remember without testing but the formula starts = LEN(a1............

Try googling your question and you should find an answer - try Mr Excel, a helpful excel forum

 

[unclebatman]

Your help is appreciated Ed - I've just registered on Mr Excel.
If these geeks (In the nicest possible sense) can't answer me, then it can't be done!

Thanks for the tip!

 

[bluesoup]

Sounds like from what you wrote and trying to do that the vlookup function may be the best option. You use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Here's an example...

Then use the cell that gives you the answer as part of a differnet formula or as a reference to somewhere else on your spreadsheet.

If you need any more help, let me know.

 

[unclebatman]

Ah Bluesoup, the gal in the know!
I've just posted in the Mr Excel forum here, which maybe explains in better detail?

<a class="postlink" href="http://www.mrexcel.com/forum/showthread.php?t=405211" onclick="window.open(this.href);return false;">http://www.mrexcel.com/forum/showthread.php?t=405211</a>

 

[bluesoup]

Hi uncle. Your post provides me with more light on what you are trying to do. I think I know what you want. It's a bit hard without talking to you.

A] for the colour change use feature called 'conditional formatting'. This is in your formatting menu bar and relatively easy to work out.

B] for a formula to happen on a given value, inparticularly when you select it from your pick list, try the IF function. e.g. =IF(I9="settled",(E9+F9+G9),0).

Does this help?

 

[unclebatman]

I'd actually worked out that the colour coding bit was conditional formatting, but i just can't get my head round it at all. I'd kind of gathered that i need to say that whatever equals settled for example change the font colour, but dont know what cells to select. If i select multiple cells, or the whole row, nothing seems to happen!

Aaaaaaaarrrrrrrrrrrrrrggggh!

 

[bluesoup]

Hi uncle, just sent you a little something. Hope it helps.

 

[auction294]

Hi uncle, just sent you a little something. Hope it helps.

hey, pack it in your husband is a mate of mine.