i want automatically filled data in customers sheet when i enter his name only (in other sheet)



i am using this formula but this gives me empty cell in between when name comes after a few cells

=IF('all people'!A3:A6='all people'!$K$1,'all people'!B3:B6,"")

some one gave me example workbook but that formula is working in the same sheet


and i need it to work in another sheet and i could not do that


please download sample work book to understand this formula and my problem as well

Attached Files

	help me.xlsx (11.8 KB, 10 views)
	example.xlsx (10.9 KB, 15 views)

Cross post

http://www.excelforum.com/excel-form...e-help-me.html

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

here is cross post link

http://www.excelforum.com/excel-form...e-help-me.html


and i have changed the title of one the thread

i am sorry again i did not want to be a part of spam

Hi nicholes,

There is no objection to cross-posting, per se. All that is asked is that you provides links for all affected boards.

For a comprehensive solution, see:
http://windowssecrets.com/forums/sho...l=1#post734296
or:
http://www.techsupportforum.com/foru...ml#post2567119

Note: There are numerous spelling errors in your 'help me.xlsx' workbook (nearly every column on all sheets!).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

By now, nicholes, someone has told you about the VLOOKUP function...right? Because it sounds to me that's what you need here.

Bob: It's a whole lot more complicated than merely using VLOOKUP.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I thought at first it was that simple. Then I read again and decided, with you, that it's more complicated. But on a third reading I changed my mind again. He wants to fill in a name in sheet X (the customer list) and have that name appear in sheet Y, right? Why won't a VLOOKUP in sheet Y do the job?, combined with an IF(ERROR(...)) to keep from displaying #N/A. Obviously because I haven't understood his requirements yet. How would you describe them, Paul?

Well, for starters, a simple VLOOKUP on 'A' will only ever return the first occurrence. As you can see from the 'help me' workbook, though, there is more than one 'A', with 'B' records interspersed. The same applies to the 'B' records. Sure you can use formulae to suppress anything to do with non-matching records, but you're immediately moving beyond a simple VLOOKUP and that still doesn't address how to get the next record on those lines instead. Remember, too, that you'll also need to synchronize the adjacent columns. If you look at the workbooks in the links I posted, you'll see how involved the formulae are.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]