Automatic Population?

[Guest]

Hi Excel Pros,

I need some help. I am putting together a spread sheet @ work that will
allow me to pick a model number ( work related) and will auto populate for
each model number ( there is a 11 of them) the standards and opitions for
that model number? As each model number has a base price and that might
include additonal charges?

Can you please help me? What kind of formula would I use?
I would use an "If" formula?

Please let me know.

Thanks

 

[Nick Hodge]

Stacey

Set your variables, Model number, price, etc, etc into a table on another
sheet and reference that using VLOOKUP formulas

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

Hi Nick,
Thanks for the info but i am still confused. I have my model numbers in a
drop down valdiation box. In other words when I select a model number then
the right price comes up.
What my question is: How do I use a auto population to so that when I enter
the the model number the standards and opitions are auto filled?

 

[Nick Hodge]

Stacey

Has this model been built for you? I suspect the price is using a VLOOKUP
already to 'autopopulate'. My answer just expanded on this to do the same
for the other variables

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

Hi Nick

I did use a lookup to create my formula. So when I select a model then the
base price comes up. So you are saying to create the same idea using the
lookup formula so that when i select the model number then the base price
(which already appears) then i can auto fill? So I would put all of my of my
standards and opitions on a seprate sheet and then use the vlook up formula?

Thanks

 

[Guest]

And how would i create my formula with the information on the next page?
So that in my formula it would refernce the next page...

 

[Nick Hodge]

Stacey

It's difficult to know what 'dropdowns' you have, but lets back this up and
set out a quick and dirty example

Open a new workbook (presuming Sheet1, Sheet2, Sheet3) and on sheet 2 set up
a table say from A1:G10 with the model numbers in columnA, price in columnB
and the other variable in ColumnsC - G. This will give you detail of 10
products.

Now on Sheet1, presuming you are going to enter a model number in A1,
highlight B1:G1 and type the following formula

=VLOOKUP(A1,Sheet2!$A$1:$G$1,{2,3,4,5,6,7},FALSE)

Now enter this formula by holding Ctrl+Shift while pressing enter. If you
have done it correctly Excel will wrap the formula in { } and you will get a
series of #N/As. Now enter a model number in A1 on sheet1 and the data
should populate. You can copy the formula down as far as you like and enter
model numbers in columnA.

Hopefully this will get you started
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Nick Hodge]

Stacey

Sorry the range in the formula should be $A$1:$G$10

=VLOOKUP(A1,Sheet2!$A$1:$G$10,{2,3,4,5,6,7},FALSE)


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)