Excel multi row list.

Author
Discussion

br d

Original Poster:

8,388 posts

225 months

Tuesday 15th April 2014
quotequote all
The subject title should read Multi column list. Doh!

Real Excel dunce here. I have a list of items which have a code, description and price, like this:

001 Description of 001 10
002 Description of 002 100
003 Description of 003 50

I have three columns in a sheet ready for these to go into but they aren't contiguous, they are H, G and I.
I have entered the data on a second sheet in three successive columns and have made the cells in column H drop down lists so I can pick the codes from the list using data validation, so far so good. How can I get the sheet to enter the corresponding descriptions and costs automatically into columns G and I?

Hope this makes sense.

Edited by br d on Tuesday 15th April 16:55


Edited by br d on Tuesday 15th April 16:56

mike_knott

339 posts

223 months

Tuesday 15th April 2014
quotequote all
=VLOOKUP([what_to_look_for],[where_to_look],[what_column_to_return])

So if your selected code is in A1, your codes to search are in G1:G100, your descriptions are in H1:H100 and your costs are in I1:I100 then your formulas would be:

=VLOOKUP(A1,G1:I100,2) ...for the descriptions and

=VLOOKUP(A1,G1:I100,3) ...for the costs.

Note that the codes to search for have to be in the left most column.

Mike…

davepoth

29,395 posts

198 months

Tuesday 15th April 2014
quotequote all
mike_knott said:
=VLOOKUP([what_to_look_for],[where_to_look],[what_column_to_return])

So if your selected code is in A1, your codes to search are in G1:G100, your descriptions are in H1:H100 and your costs are in I1:I100 then your formulas would be:

=VLOOKUP(A1,G1:I100,2) ...for the descriptions and

=VLOOKUP(A1,G1:I100,3) ...for the costs.

Note that the codes to search for have to be in the left most column.

Mike…
that will only work if there's a single occurrence of the [what to look for], however.

br d

Original Poster:

8,388 posts

225 months

Tuesday 15th April 2014
quotequote all
mike_knott said:
=VLOOKUP([what_to_look_for],[where_to_look],[what_column_to_return])

So if your selected code is in A1, your codes to search are in G1:G100, your descriptions are in H1:H100 and your costs are in I1:I100 then your formulas would be:

=VLOOKUP(A1,G1:I100,2) ...for the descriptions and

=VLOOKUP(A1,G1:I100,3) ...for the costs.

Note that the codes to search for have to be in the left most column.

Mike…
I really appreciate your time Mike but I'm completely lost! Can I break this down into a couple of stages? I also may have got some info wrong in my original post.

On Sheet 1 where the data is input, column F Is Codes, column G is Descriptions, column I is prices.

Lets say there are 10 of these.

Where do I enter the info on Sheet 2? Are you saying I have to enter the list of codes in A1 to A10, the list of descriptions in B1 to B10 and the list of prices in C1 to C10?

TIA

Mattt

16,661 posts

217 months

Tuesday 15th April 2014
quotequote all
If you can break down your tables like this:

Heading Heading 2
Cell1 Cell2


It will help to decipher your issue

Jaldi

1,195 posts

234 months

Wednesday 16th April 2014
quotequote all
If I understand you correctly, your data on Sheet1 looks like this...



and on Sheet2 you want this....



if thats right, you can achieve this by entering the formulas shown below in the top two cells (G1 and H1) and dragging down as required ...




Hope that helps

br d

Original Poster:

8,388 posts

225 months

Wednesday 16th April 2014
quotequote all
Guys I am really grateful for your efforts here but I'm struggling, I couldn't get your example to work Jaldi, it was giving me reference errors.

I think I am failing to explain this properly so I'm going to use an actual screenshot of the sheet.

I have this:



One sheet like this for each individual address I have worked at which will contain several different items of works, could be one item, could be twenty.

I have a long list of these works items that take this form:



(Obviously I'm just showing some examples here, I realise these will have to be entered onto the sheet)

So, what I'm trying to achieve is to be able to have a drop down list under "SOR Code" in column F which when selected will fill in the corresponding "Works Description" in column G and the "Unit Rate" in column I.

Sometimes a job may have several of the same item repeated, all different items or a mixture of the two, so I need to be able to select any SOR code on each row.

Is that any clearer? Thanks again for the help so far.


The Boosh

116 posts

173 months

Wednesday 16th April 2014
quotequote all
Sorry, you're not being very clear at all!

From what i've read, you've got one sheet. You want to create a list, with all possible SOR codes, which is in every cell in column F. When the code is selected, you want it to fill in the rest of the ROW with a description etc? Is that right?

What is an SOR code? Does it realte to a service (i.e cleaning) or is it a job, which can contain numerous services? (i.e 1 SOR code will fill in a lot of items in the list).

Edited by The Boosh on Wednesday 16th April 11:39


Edited by The Boosh on Wednesday 16th April 11:40

Landlord

12,689 posts

256 months

Wednesday 16th April 2014
quotequote all
Is the "form" in your first picture a data table or just a list of rows? I'm suspecting the latter if you're newish to Excel.

If you want to email via PH, I'll reply and then you can send the workbook over, I'll take a look for you.

Engineer1

10,486 posts

208 months

Wednesday 16th April 2014
quotequote all
A clunky way would be using nested if statements but they can get messy.

Jaldi

1,195 posts

234 months

Wednesday 16th April 2014
quotequote all
Brad, Is this any good?

(Download it and open in Excel, rather than just viewing it in the webpage)










(Fixed link)

Edited by Jaldi on Wednesday 16th April 14:39

br d

Original Poster:

8,388 posts

225 months

Wednesday 16th April 2014
quotequote all
Good grief. Jaldi, Jaldi, Jaldi.
You are a genius sir! This stuff is like trying to decipher Linear B to me but you have cracked it!

If I ever meet you on a run or event mate I'll buy you as much as you can possibly eat and drink.

I tried to copy the formulas you did over to my original sheet but that didn't work so I copied over the other odds and sods to your sheet instead and that did work. So far it seems to be spanky!

I have one tiny last request, it's really not a big deal if you haven't got time but there is one little unexpected thing. If you look between the columns for "Works Description" and "Unit Rate" there is column H which is "Qty" (quantity). Before your fix I could put a number in there which would multiply the Unit Rate. So, if a skip was a 100 quid, putting a 2 in the Qty column would make the Unit Rate come to 200 quid. Don't know if this can still work after your new formulas.

But, aside from that little thing, I'm over the moon.
Thank you.

Jaldi

1,195 posts

234 months

Wednesday 16th April 2014
quotequote all
br d said:
Good grief. Jaldi, Jaldi, Jaldi.
You are a genius sir! This stuff is like trying to decipher Linear B to me but you have cracked it!

If I ever meet you on a run or event mate I'll buy you as much as you can possibly eat and drink.

I tried to copy the formulas you did over to my original sheet but that didn't work so I copied over the other odds and sods to your sheet instead and that did work. So far it seems to be spanky!

I have one tiny last request, it's really not a big deal if you haven't got time but there is one little unexpected thing. If you look between the columns for "Works Description" and "Unit Rate" there is column H which is "Qty" (quantity). Before your fix I could put a number in there which would multiply the Unit Rate. So, if a skip was a 100 quid, putting a 2 in the Qty column would make the Unit Rate come to 200 quid. Don't know if this can still work after your new formulas.

But, aside from that little thing, I'm over the moon.
Thank you.
biggrin you're welcome, glad you liked it.


I could make the change you want, but does it make sense? I mean, the "Unit Rate" isn't really the rate for one unit x the Qty, is it? Shouldn't that figure really be in Col J or L?

If so .....
https://dl.dropboxusercontent.com/u/85121545/Brd-0...

Cheers

br d

Original Poster:

8,388 posts

225 months

Wednesday 16th April 2014
quotequote all
Jaldi said:
biggrin you're welcome, glad you liked it.


I could make the change you want, but does it make sense? I mean, the "Unit Rate" isn't really the rate for one unit x the Qty, is it? Shouldn't that figure really be in Col J or L?

If so .....
https://dl.dropboxusercontent.com/u/85121545/Brd-0...

Cheers
I see your point and I think your way makes more sense, unfortunately these sheets are sent in for monthly payments and this client is a real pain with stuff like this, if the form isn't exactly to their design the payment gets refused. The changes you made for me before save me a big chunk of time with the inputting but the sheet keeps it's original form, that's the vital bit.

Is there a calculator or something in Excel that you can just pull up and do a quick in-cell multiplication?

Jaldi

1,195 posts

234 months

Wednesday 16th April 2014
quotequote all
OK then mate. I think this is what you need...
https://dl.dropboxusercontent.com/u/85121545/Brd-0...


Alright?


br d

Original Poster:

8,388 posts

225 months

Thursday 17th April 2014
quotequote all
Jaldi said:
OK then mate. I think this is what you need...
https://dl.dropboxusercontent.com/u/85121545/Brd-0...


Alright?
Jaldi, I have PM'd you!

br d

Original Poster:

8,388 posts

225 months

Thursday 17th April 2014
quotequote all
Just wanted to say a forum thanks to everyone who helped, especially Jaldi who took time out from what I'm sure is a busy life to bail me out of my ignorance!

Everything is working perfectly, cheers.