Excel Cell Auto Fill

[Monkey101]

I am working on an Excel spread sheet that will be used for tracking who is using what PC in the company.

I want to have it set so that there is a list of the user’s names and you choose from a drop down what model PC they have. And from there it fills in certain columns related to the PC specs (CPU, Memory, and HDD)

What i need to know is how to make certain cells in the row have values added to them based on what another cells value is.

eg. Cell A2 is where you enter the PC model. Cell A3 - A5 will contain CPU, Memory, HDD.
If i enter Dell Optiplex 780 into cell A2 it should fill in the CPU, Memory and HDD automatically in cell A3 – A5

I know it is possible using a VB script but my skills in that side of things are non-existent.
And i do not have access to Google here so it’s not something i can go read about right now.

 

[Saffa]

Sounds like a simple solution might be enough.

You can use data validation to create the drop down list of PCs in A2.

Then you can use the VLOOKUP function to look up the PC in A2 and return the specs that you specify in your master "database".

 

[Monkey101]

Sounds like a simple solution might be enough.

You can use data validation to create the drop down list of PCs in A2.

Then you can use the VLOOKUP function to look up the PC in A2 and return the specs that you specify in your master "database".

Yeah i have data validation set up for the drop downs. Will check out VLOOKUP when i can. Thanks

 

[Saffa]

You can start the formula with an if statement to check for blank cells first. Otherwise you will have a lot of N/A#'s in your sheet.

=if(A2="","",vlookup(A2,'database','nr', FALSE))

Database refers to the database of computers.
nr refers to the column number in which the result is that you want to return. Say Memory is column 2 then it will be 2.
FALSE = you want an EXACT match not approximate.

Hope this make it a bit more elegant.

 

[Monkey101]

So i have managed to get it all working using vlookup (thanks again for that )
One last question, can you get Excel to not display the #N/A for the cells that do not have a value in the required cells? If that makes any sense.

 

[SirFooK'nG]

Just do the whole thing in MS Access, so much easier....

but...

if(isna(value),"",result)

something like that

explanation:

if the cell or formula you are referencing returns the #N/A then it must just show a blank space "", if it is not #N/A then show the value.

one of my spreadsheets uses this all over so as not to show the #N/A, heres my formula:
=IF(ISNA(VLOOKUP("J"&C16,LiveDb,49)),0,VLOOKUP("J"&C16,LiveDb,49))

 

[Monkey101]

Just do the whole thing in MS Access, so much easier....

but...

if(isna(value),"")

something like that

Not everyone who needs access to the document has Microsoft Access :-/.
I did originally have one for Access.

 

[SirFooK'nG]

check my post again, some more detail... sigh... access is so underrated!

 

[Monkey101]

check my post again, some more detail... sigh... access is so underrated!

Ok cool thanks will give that a go.
And i agree with Access being awesome. It makes life so much easier. But the powers that be want it in Excel :-/.

 

[pjjdp]

check my post again, some more detail... sigh... access is so underrated!

+ 1

I actually just use MSSQL these days and script everything I need. Reporting is in Crystal and interrogation/analysis is in Qlikview.

 

[SirFooK'nG]

Hell been out of the IT side of things for so long ... I did my Crystal reports training in 2002 i think .... was revolutionary back in those days, awesome reporting tool!

Myself and a Colleague floored quite a lot of people when we showed them what could be done in Access (we used it to rapidly propagate data from one system to another - was a dos based system to a win 32 SQL Server based app). we did all our data cleanup and normalization / importing and exporting with it... Hell I even used it to audit our Payroll system.