Any excel gurus about ?
Discussion
I'm not very familiar with excel, but rapidly learning.
I have a spreadsheet that is a list of items/description/price. Each row has an individual number ie 892027 which relates to a full electrical earth bond at £xyz price.
I've sussed the drop down lists function, but surely there must be a way of entering the code and have excel fill the relevant details in other cells for me?
Any pointers as to what to do?
Thanks in advance
Phil
I have a spreadsheet that is a list of items/description/price. Each row has an individual number ie 892027 which relates to a full electrical earth bond at £xyz price.
I've sussed the drop down lists function, but surely there must be a way of entering the code and have excel fill the relevant details in other cells for me?
Any pointers as to what to do?
Thanks in advance
Phil
If you've got the data on the sheet somewhere else (i.e. the specifics of the 987239487 model number), then look at using VLOOKUP or HLOOKUP (stands for vertical or horizontal look up).
Simply put, you will search for given data in another table, and then return a value relative to that found data.
The HELP screen in Excel, especially with functions, I find very useful indeed - it's just knowing which functions you need
Simply put, you will search for given data in another table, and then return a value relative to that found data.
The HELP screen in Excel, especially with functions, I find very useful indeed - it's just knowing which functions you need
philthy said:
Does it enter the data in different cells though.
ie
quantity in cell A1, code in A3, description in A5, and price in A7 ?
You enter the HLOOKUP formula, for example, in that Cell A3. You then say that in that cell A3, place the figure (x) columns to the right of (value) in (sheet). Where (value) is your model number and (sheet) is where all the data of your models are, and (x) is how many columns to the right of that model number the quantity is.
Make sense? That's the lamens words for what an HLOOKUP does.
Good luck
atomicrex said:
HLookUps and VLookUps are the worst things to use, especially when creating large lists! Try using Index and Match, that way your pc wont turn into a giant hourglass each time it wants to recalculate.
If you want to send me the sheet then id be happy to do it for you for free!!
Bah! LOOKUPs are marvellous, especially when you start dropping IF and ISERROR into them
hornet said:
HLookUps and VLookUps are the worst things to use, especially when creating large lists! Try using Index and Match, that way your pc wont turn into a giant hourglass each time it wants to recalculate.
If you want to send me the sheet then id be happy to do it for you for free!!
Bah! LOOKUPs are marvellous, especially when you start dropping IF and ISERROR into them
What like this beut... I worked out how they work on someonelses spreadsheet and copied it and modified the look up cell references
=IF(ISNA(VLOOKUP(Q8,$X$8:$Y$455,2,FALSE)),"",VLOOKUP(Q8,$X$8:Y455,2,FALSE))
Didnt say there we no good, just said that they waste time. Trust me having reworked workbooks at over 50mb and reems of vlookups they are slow! Fine if you only have a small array that you are looking up, but its a bad habit to use.
Try the Index and match and see how easy they are to use....after all programming is all about embracing new ideas!
Try the Index and match and see how easy they are to use....after all programming is all about embracing new ideas!
atomicrex said:
Didnt say there we no good, just said that they waste time. Trust me having reworked workbooks at over 50mb and reems of vlookups they are slow! Fine if you only have a small array that you are looking up, but its a bad habit to use.
Try the Index and match and see how easy they are to use....after all programming is all about embracing new ideas!
True, but I'm the only person in our department at work with decent Excel skills, all of which are self taught, so I just like showing off
The more complicated and unnecessary the better. If it can involve a pivot table as well, so much the better! I shall try Index and Match and report back...
GasMonkey said:
What like this beut... I worked out how they work on someonelses spreadsheet and copied it and modified the look up cell references
=IF(ISNA(VLOOKUP(Q8,$X$8:$Y$455,2,FALSE)),"",VLOOKUP(Q8,$X$8:Y455,2,FALSE))
I fear this may kick off a "my formula is more complicated than yours" arms race
hornet said:
What like this beut... I worked out how they work on someonelses spreadsheet and copied it and modified the look up cell references
=IF(ISNA(VLOOKUP(Q8,$X$8:$Y$455,2,FALSE)),"",VLOOKUP(Q8,$X$8:Y455,2,FALSE))
I fear this may kick off a "my formula is more complicated than yours" arms race
Sorry.... it was a bit of a geeky thing to do, shud stick to giving second rate car related advice ! 
GasMonkey said:
What like this beut... I worked out how they work on someonelses spreadsheet and copied it and modified the look up cell references
=IF(ISNA(VLOOKUP(Q8,$X$8:$Y$455,2,FALSE)),"",VLOOKUP(Q8,$X$8:Y455,2,FALSE))
I fear this may kick off a "my formula is more complicated than yours" arms race
Believe me, I could have hours of fun with "Geeky Excel Top Trumps"
You guys! It's only a very small worksheet, it's for the rates we charge for electrical work. Up until now, we have always just used a sheet of references, and entered the code/description/price manually. When the job fell to me, I started thinking there must be a way to automate this. Thanks for the recommendation and offer Tom and greenv8s, but this is probably too small for you to worry about. I'll upload the sheets to my webspace later if someone wants to have a crack at it though.
Thanks all
Edit to add the links to the worksheets:
www.skidvd.co.uk/codes.xls
www.skidvd.co.uk/vr.xls
A virtual pint to the winner
>> Edited by philthy on Tuesday 16th May 09:18
OK
I've Had a crack at it and it works ok.
it needs to be tidied up a bit
moved list to sheet3 on Form spreadsheet for ease
In Cell L14
=MATCH(C14,Sheet3!A:A,0)
In Cell
E14
=INDEX(Sheet3!A:C,L14,2)
J14
=INDEX(Sheet3!A:C,L14,3)
copy the formulas into each required cell L,E,J
>> Edited by nismo200sx on Tuesday 16th May 12:31
>> Edited by nismo200sx on Tuesday 16th May 12:46
I've Had a crack at it and it works ok.
it needs to be tidied up a bit
moved list to sheet3 on Form spreadsheet for ease
In Cell L14
=MATCH(C14,Sheet3!A:A,0)
In Cell
E14
=INDEX(Sheet3!A:C,L14,2)
J14
=INDEX(Sheet3!A:C,L14,3)
copy the formulas into each required cell L,E,J
>> Edited by nismo200sx on Tuesday 16th May 12:31
>> Edited by nismo200sx on Tuesday 16th May 12:46
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff