S
Summerstone
Hi all, I hope someone can help with this one - I think I'm overly
complicating my problem here - hoping there's a simple answer - it's a
tricky one to describe though....
Here's the overview.
3 rooms can be booked for courses where the customer will use minutes
in subsequent visits.
When making a booking, a "customer" tab is used to identify the price
of the couse and gives the customer a unique number e.g. 5023Tony
Summers (concatenated for a lookup)
In the diary planner tab, I have a number of columns for each room -
the front end for the user.
My problem us that I need to perform a lookup from the diary planner
tab into the customer tab to populate the FIRST INSTANCE of the price.
i.e. When they book further appointments, the value in the diary needs
to be zero.
I've tried sumif, sumifs, counts, vlookups but nothing's working. The
closest I've got is =SUM(IF(D6="",0,COUNTIF($D$6:X6,D6)))+(IF(X6="",
0,COUNTIF($D$6:X6,X6)))+(IF(N6="",0,COUNTIF($D$6:X6,N6))) which is
causing circular references for the first 2 rooms. Basically I counted
the number of times "5023Tony Summers" appeared above the current row
(wanted to pick just the unique cells but countif seems to not be able
to pick multiple cells so I had to use the whole area) and if the
result of that formula was 1 then it was the actual booking, not
course time and therefore the vlookup works. So my room 3 is working
peachy!
So to give a further example:
5023Tony Summers books a 100 minute course and will use 10 minutes in
room 3 to start with, price £25.
That's the easy bit. Vlookup 5023Tony Summers into the customer tab
and return the 4th column, £25.
He then books a further 10 minutes for the following day in room 1.
My formula now needs to identify that a previous booking has been made
in order to bring the price £0 through (i.e. already paid and is now
using up his minutes). Incidentally, I run a countdown of the
available mintues on the diary tab which should help the shop
assistant to know when the customer is running out of course time -
that's working fine!
Of course this is complicated a little by the fact that the customer
may not want a course and there is data validation in the diary for
"walk in" trade which has a different pricing structure and this has
complicated my approach and has meant a frightening amount of nested
formulas... I don't think this affects my problem, just a little more
background info.
By the way, for anyone actually interested, it's a Sunbed shop....
Any suggestions would be great, I just hope this is making some sense!
Cheers,
Tony
complicating my problem here - hoping there's a simple answer - it's a
tricky one to describe though....
Here's the overview.
3 rooms can be booked for courses where the customer will use minutes
in subsequent visits.
When making a booking, a "customer" tab is used to identify the price
of the couse and gives the customer a unique number e.g. 5023Tony
Summers (concatenated for a lookup)
In the diary planner tab, I have a number of columns for each room -
the front end for the user.
My problem us that I need to perform a lookup from the diary planner
tab into the customer tab to populate the FIRST INSTANCE of the price.
i.e. When they book further appointments, the value in the diary needs
to be zero.
I've tried sumif, sumifs, counts, vlookups but nothing's working. The
closest I've got is =SUM(IF(D6="",0,COUNTIF($D$6:X6,D6)))+(IF(X6="",
0,COUNTIF($D$6:X6,X6)))+(IF(N6="",0,COUNTIF($D$6:X6,N6))) which is
causing circular references for the first 2 rooms. Basically I counted
the number of times "5023Tony Summers" appeared above the current row
(wanted to pick just the unique cells but countif seems to not be able
to pick multiple cells so I had to use the whole area) and if the
result of that formula was 1 then it was the actual booking, not
course time and therefore the vlookup works. So my room 3 is working
peachy!
So to give a further example:
5023Tony Summers books a 100 minute course and will use 10 minutes in
room 3 to start with, price £25.
That's the easy bit. Vlookup 5023Tony Summers into the customer tab
and return the 4th column, £25.
He then books a further 10 minutes for the following day in room 1.
My formula now needs to identify that a previous booking has been made
in order to bring the price £0 through (i.e. already paid and is now
using up his minutes). Incidentally, I run a countdown of the
available mintues on the diary tab which should help the shop
assistant to know when the customer is running out of course time -
that's working fine!
Of course this is complicated a little by the fact that the customer
may not want a course and there is data validation in the diary for
"walk in" trade which has a different pricing structure and this has
complicated my approach and has meant a frightening amount of nested
formulas... I don't think this affects my problem, just a little more
background info.
By the way, for anyone actually interested, it's a Sunbed shop....
Any suggestions would be great, I just hope this is making some sense!
Cheers,
Tony