Help?

Kristy, use Vlookup.

In Sheet2, i'm guessing B2 (Column Desc.) put this formula.

=IF(COUNTIF('Sheet1'!$A2:$A1000,$A2),VLOOKUP($A2,'Sheet1'!$A2:$D1000,2,0),"")

In C1 (ITEM #)

=IF(B2,VLOOKUP($A2,'Sheet1'!$A2:$D1000,3,0),"")

In D1

=IF(B2,VLOOKUP($A2,'Sheet1'!$A2:$D1000,4,0),"")

And that should work.

Juan Pablo

-------------

Hey Kristy-I could easily do this in a few minutes using a VLOOKUP formula but it is difficult to explain. If you want to email me the file(s), I'll fix it and send it back.
travis.harr@cableone.net

Thanks for your help, Juan, but I can't get it to work the way you have it (of course, this could just be me).

I do think we're onto something, though.

One little nagging thought: I want to type in the description and have it bring up the information. If I type a formula in there, won't I simply be typing over it then?

I tried putting the formulas in the item # and page # columns and leaving the description blank. When I type something in there, I just get #VALUE in item/page #.

If it would help any, I would be typing the description in D4:D27. Item # is C4:C27, and Page # is E4:E27. The product list (not counting the heading row) is A2:C146 with A=Item description, B=Item # and C=Page #.

It's been a long time since I even *had* to do anything with a formula other than the normal sum, etc. I'm trying!:-)

Thanks,
Kristy

Ok Kristy, let's do this step by step.

1.Select in Sheet2 the range D4:D27 to avoid the typing, i'm putting a DropDown List, so you can choose.... if you don't want this then skip this step. Goto to Data - Validation, and select List. Make sure that Dropdown list is checked, and in the origin type: Sheet1!A2:A146

2.Still in Sheet2. Select C4:C27 and type this

=IF(D4,VLOOKUP(D4,Sheet1!$A$2:$C$146,2,0),"")

Now press Control + Enter to put the formula in all the range

This gives you item# if D4:D27 is not blank.

3.The same with Page#. Select E4:E27 and type

=IF(D4,VLOOKUP(D4,Sheet1!$A$2:$C$146,3,0),"")

and press Control + Enter

4.Now, you can choose / type a description and the results should appear, the error that appeared maybe due to a mistype, something like

Sugar (In Sheet1) and you typed Zugar ... it has to be an EXACT match, that's the use of the dropdown list...

Juan Pablo

Soo clooose....

Ok. I've typed in the code to the letter, and tried it without the dropdown list first. Same thing happens. I tried typing in Advil exactly as it is in the list, and got the wonderful #VALUE.

Then, I tried doing the dropdown list and when I type in the origin, the only thing that comes up in the list is what I typed there. If I put an equal sign in front of that, I get an error saying that I can't use references to other worksheets/workbooks for data validation criteria.

Re: Soo clooose....

Ok, ok, i forgot i thing... sorry

Can you mail me your workbook ? i wanna check what the error is

Re: Soo clooose....

Nope. I don't have email here at work.

Re: Soo clooose....

OK...I managed to get the dropdown list working (go me!) but it's not bringing up the item/page numbers. Still getting #VALUE.

Re: Soo clooose....

Wow - lots of feedback - thanks guys.

What I wanted was to protect the sheet so that it couldn't be unhidden by someone selecting Tools-sheet-unhide without entering a password.

I got round the problem by adding two buttons to my spreadsheet. On hide the sheet using sheet.visible - xlveryhidden which means that it doesn't appear on the list of hidden sheets when you use the tools-sheet-unhide menu.

The other button opens an input box asking for a password - if the password is correct the sheet gets unhidden!

I'm sure it's a bit long winded but it works
Cheers for your help though
Douglas

Set sht = ActiveSheet If sht Is HiddenSht = False Then If InputBox("Enter password to continue", "Password") <> "Mypassword" Then HiddenSht.Visible = False MsgBox "Incorrect Password", vbCritical sht.Activate Else HiddenSht.Visible = True HiddenSht.Activate End If End If End Sub

Small fix

Kristy --

Juan's proposal requires a slight modification with respect to

Sheet1!$A$2:$C$146

In order to give this as source in a sheet other than Sheet1, you need to name it.

Select A2:C146, go to the Name Box on the Formula Bar, and type SDATA followed by ENTER.

Type as source

=SDATA

instead of

=Sheet1!$A$2:$C$146

in the Data Validation window.

Aladin

========

Re: Soo clooose....

Just so you're aware, I cannot read the response you posted above. I end up in another thread on the board, not anything to do with this one.

Re: Soo clooose....

Kristy, first, you have to name th range in Sheet1 (A2:A146) as Aladin said.

The other thing, change the first part of the formulas to

=IF(D2<>"",

instead of

=IF(D2,

Juan Pablo

Got it!!!!!!

Thanks!:-)

Glad to help

Kristy, first, you have to name th range in Sheet1 (A2:A146) as Aladin said. The other thing, change the first part of the formulas to =IF(D2<>"", instead of =IF(D2,