Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Microsoft Excel


  • Please log in to reply
5 replies to this topic

#1 hintswen

hintswen

  • Members
  • 57 posts
  • OFFLINE
  •  
  • Local time:01:28 PM

Posted 02 August 2005 - 06:54 AM

I was wondering if there was a way to automaticly enter a value into a cell accroding to what is entered into another cell. ie

A1-Jack
B1-14.6
A2-Sarah
B2-2.9
A3-Mark
B3-6.7
A4-Gary
B4-3
A5-Jack
B5-14.6
A6-Mark
B6-6.7
A7-Jack
B7-14.6
A8-Sarah
B8-2.9
A9-Bill
B9-9

maybe that wasn't a good way to explain but hopefully someone understands :D

BC AdBot (Login to Remove)

 


#2 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:01:28 PM

Posted 02 August 2005 - 09:29 AM

So far, you have some names running along the top, and a number underneath each name. In this example, what would be automatically entered into what cell, and what cell would trigger the entry?
So far, it looks like you would have to manually enter the names and the numbers into each cell, though you could certainly do a total or an average to the right of the last b(x) entry.
Regards,
John

A much later PS:
I failed to note until I reread the post sometime later that there is a repetition of names/numbers. I suppose an easy way would be simply to copy the cells for the two rows for the four people into the next group, if that is what you want to end up with, but I suppose you have something more complicated in mind. Maybe if you went through the process, step-by-step, of what you want to do, it would be clearer to me.
Thanks,
John

Edited by jgweed, 02 August 2005 - 09:16 PM.

Whereof one cannot speak, thereof one should be silent.

#3 hintswen

hintswen
  • Topic Starter

  • Members
  • 57 posts
  • OFFLINE
  •  
  • Local time:01:28 PM

Posted 03 August 2005 - 02:49 AM

the a colum would trigger what's in the b colum and i can't sopy+paste(names appear in odd orders and that takes too long. the table would be clear and i'd have to manually look up the second colum's value's.

what i want to do is have excell automaticly enter a certain value in the B colum according to what has been typed in the first colum, this value stays the same depending on the name or i wouldn't have a John Smith - 8.3 and another John Smith - 3.4

I will have all the names and value's typed up somewhere else if i need it but mainly i just need the value's to auto-enter.

isn't there a database function iv excell that allows you at add people and details then i can get it to check what name is entered and auto fill in the other data according to the database?

Edited by hintswen, 03 August 2005 - 02:52 AM.


#4 Jay Cat

Jay Cat

  • Members
  • 7 posts
  • OFFLINE
  •  
  • Local time:01:28 PM

Posted 03 August 2005 - 06:31 PM

Try VLOOKUP or HLOOKUP. For example, type all the names in column D and all the values in column E. Select all the information in columns D and E. On the menu select Insert, Name, Define and enter a name like TheData. In column B1 enter the following formula:

=VLOOKUP(A1,TheData,2,TRUE)

Then copy the formula in B1 to the rest of the cells in column B.

#5 Rok_Hard_CDN

Rok_Hard_CDN

  • Members
  • 23 posts
  • OFFLINE
  •  
  • Local time:02:28 PM

Posted 14 June 2006 - 08:59 PM

give this a try... I'm been on this very same quest for quite some time
http://www.contextures.com/excelfiles.html

#6 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,574 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:02:28 PM

Posted 15 June 2006 - 11:57 AM

Try VLOOKUP or HLOOKUP. For example, type all the names in column D and all the values in column E. Select all the information in columns D and E. On the menu select Insert, Name, Define and enter a name like TheData. In column B1 enter the following formula:

=VLOOKUP(A1,TheData,2,TRUE)

Then copy the formula in B1 to the rest of the cells in column B.

I'd add to this two more suggestions:
1. Make sure the range called here TheData is sorted on the column to be looked up, I suppose it's name
2. For exact match use the FALSE switch at the end, so that incomplete matches won't confuse things. #N/A error, or similar will come out if there's no matching entry (because of new new names) in the lookup column. At which point you can add to TheData and redefine the range address. OR, initially include in TheData some fake value which sorts to the end (zzzzzzzzzzzzz) and insert new values above. Then Excel will automatically recalculate the address of TheData range and you'll be smiling :thumbsup:




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users