Auto-populate?

[RickyDee]

I have two worksheets. One with all the information I
require, the second is blank. Is it possible for me to
(fairly easily) type in a word in the first column of the
2nd sheet and have the information from the first sheet
automatically fill in?

Ok, here goes:
Sheet #1. TOOLS (and in the following columns, price,
part number).
Sheet #2. Blank. I type in TOOLS in the first column.
Now, without creating a link between the two worksheets,
(with the '=' sign), have the information related to TOOLS
just copy over to Sheet #2.

The reason for this is:
Let's say I have all tools in the first column of the 2nd
sheet. I have a Philips Screwdriver and a Crescent
Wrench. I want ONLY the information for one tool in the
second sheet. I type in FLAT HEAD SCREWDRIVER. I want
the information for FLAT HEAD SCREWDRIVER to just copy
over to the second sheet.

Thank you, and have a nice day,
RickyDee
(Remove the 'NOTATHOME' from the address if writing to me
at my home email, please).
RickyDee

 

[JulieD]

Hi

try using the VLOOKUP function
where in sheet2!A2 you type in "Flat head screwdriver" (you can use data
validation to provide a drop down list for this too)
then in sheet2!b2 put the following formula
=VLOOKUP(A2,Sheet1!$A$1:$D$100,2,False)
in C2 put
=VLOOKUP(A2,Sheet1!$A$1:$D$100,3,False)


where Sheet1!$A$1:$C$100
is the range of your items, price & part number on the first sheet

let us know how you go

cheers
JulieD

 

[Guest]

Hello again, JulieD.
I tried this. May be something here at work that I can't install or run, whatever. (Military has things so tight it's pathetic). Anyway, someone mentioned just linking the two worksheets together and a drop-down list will be available on SHEET 2, with all the information from Column 1 in SHEET 1. Know anything about that?
Thank you, Sorry I haven't been back in awhile. (Busy saving lives).

 

[JulieD]

Hi RickyDee

to get a drop down list on sheet 2 with info from sheet 1 you need to use
data validation ... here's an example for the whole process - if you try it
on a new workbook you can then just use the bits you need to do it on your
existing one:

A. create your lookup lists
1. type on sheet 1 cells A1:A5 the name of ten people (make them all
different)
2. type on sheet 1 cells B1:B5 a number between 1 & 100 for each person
3. select cells A1:A5 click in the name box (little box above column A to
the left of the formula bar) and type PNAMES and press Enter (no spaces)
4. select cells A1:B5 click in the name box and type PINFO and press
enter

B. create the drop down list
1. click on sheet 2 cell a1 and choose Data / Validation from the menu
2. on the settings tab under the "allow" drop down choose LIST
3. click in the white box under this and press your F3 key - click on
PNAMES in the list and click on the OK button & then on the OK button
again - you will now have a drop down box where you can choose the person's
name

C. now to get the related info
1. click in sheet 2 cell b1 and type
=VLOOKUP(A1,
press your F3 key and choose PINFO and click Ok
then type
,2,FALSE)
press Enter
your formula should look like this
=VLOOKUP(A1,PINFO,2,FALSE)

now if it shows #NA - don't panic
choose a name in cell A1 and the associated value should be displayed in B1
to suppress the #NA when there's no name selected in A1, surround the
VLOOKUP in an IF statement - there's a couple of ways of going but this one
should work for you
=IF(A1="","",VLOOKUP(A1,PINFO,2,FALSE))

Let me know how you go.

Regards
JulieD

Hello again, JulieD.
I tried this. May be something here at work that I can't install or run,

whatever. (Military has things so tight it's pathetic). Anyway, someone
mentioned just linking the two worksheets together and a drop-down list will
be available on SHEET 2, with all the information from Column 1 in SHEET 1.
Know anything about that?

 

[Guest]

JulieD,
Well, I had time to check this out and figure out about 3/4 of it. This worked wonders for not only what I was originally trying to do, but helps with appointments, scheduling, finanances and the work that I do as a Safety Specialist. I've still got some trial and errors to conduct, but so far the only things I haven't figured out yet and need are:
If I have a list of 125 items, but need more, I can't figure out how to add more rows to correspond with my original list. The information doesn't seem to follow. (I'm thinking it's the 'validation' part of the 'formula'.
I figured out a lot more with this 'formula' just by messing with it.
Thank you very much,
RickyDee

 

[JulieD]

Hi RickyDee

to add more items to your list and have them show up automatically in the
drop down & work correctly in the VLOOKUP you need to change your ranges
(PNAME & PINFO) from static to dynamic ... Debra Dalgleish has some great
notes on this on her website at
www.contextures.com/tiptech.html

let us know how you go.

Cheers
JulieD

JulieD,
Well, I had time to check this out and figure out about 3/4 of it. This

worked wonders for not only what I was originally trying to do, but helps
with appointments, scheduling, finanances and the work that I do as a Safety
Specialist. I've still got some trial and errors to conduct, but so far the
only things I haven't figured out yet and need are:

If I have a list of 125 items, but need more, I can't figure out how to

add more rows to correspond with my original list. The information doesn't
seem to follow. (I'm thinking it's the 'validation' part of the 'formula'.