How to fill out cells depending of the choice clicked in a list

**Triben** · 03-02-2011, 11:11 PM

Hello everyone,

First post here

I have used Excel I think like a lot of people for a long time and I am know stuck with a stupid problem.

Indeed in my spreadsheet I want to give to users the possibility to choose a "product name" in a list (let's say the choice can be made in every row of column A), this list is generated with a "product chart" I have in the same tab. The user does not have access to this chart, he just choose in the list of column A what product he is selling.

Depending of the choice made in column A, row 1 for example I want to have other cells of the row 1 filled out.
These other cells have to be filled according to the choice made in column A (info is in the product chart, on the same row that the product name clicked in column A).

Before I was not annoyed because I used very small charts (few products) bu now I have about 200 products in my chart, so I cannot continue with: if (A1=xxxxx,B1 =yyyyy,"")

If anyone has an answer it will be deeply appreciated.

Precision: I have MAC 2008, so I don't have VBA

Bye !

**Cutter** · 03-03-2011, 09:12 AM

Without knowing the details of your setup I can only guess that you can use the usual solution to this type of request.

Have a read through this:

http://www.excelfunctions.net/ExcelVlookup.html

**Triben** · 03-03-2011, 04:23 PM

Thank you so much Cutter, it is exactly what I needed.

Now I can go further, so I have another question.

Let's say the price of the product depends on the season.
So depending on what date the user put in row 1, the price would be automatically be displayed in another cell of row 1.

A recap:
In the product chart I use there is the price split in several columns (1 column per season).

The user of my file use another chart on the same tab.
How can I do to have the user choose the ref of the product in the list (already done), then it gives automatically the commercial name (thanks to you Cutter, this is now done) and then the user put the day the customer will rent the product and automatically the price is displayed (and this last step I don't know how to do it)

Thank you so much

**Cutter** · 03-03-2011, 06:59 PM

Could you upload a small sample file showing your setup?

For directions click the FAQ button at top of page and then follow the links.

**Triben** · 03-03-2011, 08:17 PM

Sure thing I can upload a file.

My need is to fill the orange area automatically depending on column B, D and E.

The dates entered in column D and E needs to be used to know where to find the price (in what column).

You will see that Product 9 (on row27) is tricky, its rent is spread on two season, so 2 daily rates must be applied: one from March 1st to April 1st and the second from April 2nd to April 15st

Actually, Excel needs to:
- check at what season belongs the date of column D and - depending of the product in the row - put this price in column J
- check if the whole rent is in the same season (i.e. one daily rate) if not, apply another daily rate for the second period (if a long term rent we can have the 4 season of the rent...)

**Cutter** · 03-04-2011, 11:49 AM

OK, I haven't fully tested it but have a look. I restructured your "Product Chart" to get rid of merged cells and make it easier to match dates.
I noticed an inconsistency in that chart. You have 2 occurrences of Oct 1st - the end date for Period 3 and the start date of Period 4. You didn't do that for the other periods. So that would need your attention.

**Triben** · 03-04-2011, 12:38 PM

Honestly I don't know what to say but thank you !

You are completely right for the dates of period 3 and period 4 it is a mistake I did in my example. Beginning of period should begin at october 2nd

Can you just explain me why you put a +1 +2 or +3 in the cells of period 2, 3 and 4 (in bold below):

=IF($B25="";"";IF(MATCH($E25;$V$4:$Y$4;1)-MATCH($D25;$V$4:$Y$4;1)=0;"";INDEX($V$6:$Y$15;MATCH($B25;$S$6:$S$15;0);MATCH(D25;$V$4:$Y$4;1)+1)))

While I would have written:

=IF($B25="";"";IF(MATCH($E25;$V$4:$Y$4;1)-MATCH($D25;$V$4:$Y$4;1)=0;"";INDEX($V$6:$Y$15;MATCH($B25;$S$6:$S$15;0);MATCH(E25;$V$4:$Y$4;1))))

I don't understand how your formula works.

Does the +1 mean "look in the next column" ?

Thank you again

**Cutter** · 03-04-2011, 12:54 PM

Yes, that's exactly what it means. +1 means look one column over, +2 looks 2 columns over.

The INDEX() function as used here has 3 arguments: the first is the total range, the second is the row number within the range (in this case the row matching the Product #) and the third is the column number within the range.

Since your first Period could be any season I just used the match of the earlier date and, if the later date is not within that first period, then add 1 for the next period's column.

I don't know what you plan on doing when dates run into next year, though. Expand the Product Chart?

**Triben** · 03-04-2011, 03:06 PM

Ok thank you for this confirmation.

If the renting dates run into a new year I would like excel to go back in season 1, I actually don't need to expand the product chart.
So if there is a way to stay in the same 4 columns of the 4 seasons it would be easier.

Again thank you for all these advice.