An excel query

  [email protected]® 19:41 19 Aug 2004

I have an excel document with various names in different cells. What I want to do is put a number next to a name i.e Daz in A1 and 7 in A2 and if that name appears again somewhere else in the spreadsheet i.e Daz in F7 I want 7 to be entered automatically in G7 without me having to type it! I hope this can be understood!!

  stalion 20:12 19 Aug 2004

Oh VoG™ wherefore art thow, your help is needed here and now

  Simsy 20:22 19 Aug 2004

define the range that contains the original names and numbers, it would be possible using VBA. It would be a lot easier, I think, if the numbers were next to the names, rather than underneath. You have "Daz" in A1 and "7" in A2, rather than B1... is that what you meant?

Anyway, supposing you had names entered in the range A1:A50, with corresponding numbers in B1:B50, and you gave the range A1:B50 a name, I think it could be done.

It would take me a while to think about it, and I'm sure others could do it quicker, but there may be repercussions on other cell entries; suppose you entered the name "Gaz" in cell F10... what you have asked for would mean that "7" would be automatically entered in cell G10, regardless of another entry being already there... is that what you want?

Anyway... I'll have a think and a play... but I bet someone else gets there first!



  [email protected]® 20:25 19 Aug 2004

Yes that is what I meant. A1-name, B1-number and so on.

  Chris the Ancient 20:29 19 Aug 2004

I'd rather avoid long-winded explanations here, but have a look in the 'help' pages at HLookups.

They do take a bit of perseverance, but I think they may help.


  Simsy 20:30 19 Aug 2004

How many possible names? (And there much be no duplicate names!)

I won't be able to get into this for a few days, but if I can, I will.



  Simsy 20:32 19 Aug 2004

It's going to involve using a lookup, but VBA as well because of the offsetting one cell to the right.



  [email protected]® 20:36 19 Aug 2004

Whats lookup and VBA?

  Simsy 20:49 19 Aug 2004

is a type of function that Excel uses to do calculations with. (See "lookup" in the help files).

VBA is "Visual Basic for Applications", a programming language that enables Excl, (and other programmes) to have specific action programmed in.

In this case the programming action would be, basically,

Look at what has just been entered and see if it matches someting in the named range.

If it does, see what is in the cell to the right of that entry. (This is the lookup part).

Store that value in memory

Move one cell to the right of the cell you have just made an entry in

Enter into that cell the value just stored in memory.

That whole sequence is something that can't be done with a formula, hence it has to be programmed using VBA.

if you record a Macro, wat you are doing is writing a small bit of VBA. If you record a macro and then , via Tools>Macro>Macros>edit, look t what is written, you can see the sort of language used.

It's certainly not something you'd want to speak, but it is logical!

Anyway, I'll see what I can come up with!



  pc moron 20:49 19 Aug 2004

So you want the name in A1, A2 etc, and the corresponding number in B1, B2 etc.

  [email protected]® 20:55 19 Aug 2004

Yes thats right. But as I said the same name may be somewhere else like M14 so I want the number I put in B1 to appear in N14!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)