trying to retrieve a cell from table in sheet 2 to sheet 1

[jeeremie]

Let me explain briefly what I want to achieve.

Clients come to the shop and can ask to receive body massage, foot massage or herbs massage for 1 hour, 2 hours or more. Prices vary depending on the number of hours requested by client and option (body massage, foot massage or herbs massage). For example, one hour is more expensive than if they get two hours at once.

At the moment, our secretary has to keep records of who's massaging, what option and number of hours. From that she has to calculate how much each employee must get at the end of the day.

Hence, I want to create a document to automate all this by letting her select from drop down menus the name of employee, time massage started, number of hours and the option (body massage, foot massage or herbs massage). It contains two sheets:

SHEET 1: management: see atached image: management.jpg
SHEET 2: rates: see atached image: rates.jpg (prices are not real).

In SHEET 1: Management
TOTAL in column H is what we charge customer.
TOTAL EMPLOYEE in column I is what must be paid to the employee after each massage.
TOTAL in column A and B is total price at the end of the day.

In SHEET 2: rates
There's 3 options: body massage, Foot massage, Herbs massage. For each options, there are two columns: first column is the price for client and second column is price to pay for employee.


Depending on number of hours and option selected in SHEET 1 it should go to take the corresponding price in SHEET 2 and fill it in column H and I in SHEET 1.

I thought that would be easy to select price in my table in SHEET 2 but I am stuck and some help would be greatly appreciated. I am not an expert at excel and would prefer to avoid using VBA if possible. I have attached my document (management&billing.xls) with the images if you want to have a look.

Let me know if you need to know something else or if my explanation was not clear enough. Thanks.

Btw, I use Excel 2003.

Thanks in advance.

[ConneXionLost]

Hi Jeeremie,

It appears your choices A1, B1, C1 in the drop down boxes are not necessary if choices A, B, or C are already made.

This is a first attempt, let me know.

Please Login or Register  to view this content.

HTH

[jeeremie]

Hello ConnexionLost,

Thank you for your fast reply. I tried your code and though it seems to be correct (to me at least), it didn't work. I don't understand what's wrong.

It appears your choices A1, B1, C1 in the drop down boxes are not necessary if choices A, B, or C are already made.

Yes, sorry for not mentionning. I was only testing. A1, B1 and C1 will not appear in drop down in G4 (management).

[ConneXionLost]

Okay, I don't know why it won't work either, but I'll post a copy of your sample with my modifications. Hope that will fix things.

Cheers,

[jeeremie]

Oh! That works!

I think I know what was the problem. My excel is a french version and thus the formulas had to be in that language and not in English. Did you write the new code in french yourself or did it translate automatically?

the new code is:

F4 =SI(OU(F4="",G4=""),"",INDEX(rates!$B$3:$H$9,EQUIV(F4,rates!$B$3:$B$9,0),EQUIV(G4,rates!$B$4:$H$4,0)))

I guess in English it was:
F4 =IF(OR(F4="",G4=""),"",INDEX(rates!$B$3:$H$9,MATCH(F4,rates!$B$3:$B$9,0),MATCH(G4,rates!$B$4:$H$4,0)))


Anyway, thanks a lot. I see you even entered the formulas for the first columns.

[ConneXionLost]

The translation must have occurred automatically since I wrote that in English. Glad it's working for you, if that's answered your question, please follow the instructions in my signature block for marking this thread [SOLVED].

Cheers,