An excel query

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 04

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

  Simsy 20:22 19 Aug 04

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!

Regards,

Simsy

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

  Chris the Ancient 20:29 19 Aug 04

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.

Chris

  Simsy 20:30 19 Aug 04

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.

Regards,

Simsy

  Simsy 20:32 19 Aug 04

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

Regards,

Simsy

Whats lookup and VBA?

  Simsy 20:49 19 Aug 04

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!

Regards,

Simsy

  pc moron 20:49 19 Aug 04

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

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…