PDA

View Full Version : Routine cycling data after automatic database sorting



CydMM
02-03-2012, 08:18 AM
Hi guys,
well, I am working on a tough project (for me, it is) and I am now stuck trying to automatize the model.


The project is here enclosed.



Let me explain the point.


The file I am enclosing is composed by just two sheets: Database and Dashboard.


In the Database one, there is a table which shows the breakdown of selling price of a series of articles, divided into several sets (those linked under column A). Each set occupies a record.


The range of products is organized by:

Segments (Column C). Segments are 2: Residential/Industrial
Price List (Column D). Price lists are 3: European/Extra-European/Deluxe
Installation (Column E). Installation types are 3: Installation A / Installation B / Installation C
Inside Dashboard sheet I created a someway prettier table structure, which should summarize the price breakdown by sets, for each kind of Installation. The data that will be get from the database table are those with yellow colour over the column, and with the number of column at the top (i.e: Col5).

What would I like to achieve?
In cells B5 and B6 of Sheet Dashboard user could for example choose which segment and then which price list to analyze.



Then, after that double choice, an hypothetic routine should accordingly sort the table inside Database sheet and, maybe through an ActiveX control on Dashboard sheet, cycling through all the sets pertaining to the double choice selected by user, and show results inside the tables of the Dashboard sheet.


As you may see, there are three tables, one for each kind of Installation. User will focus on the middle column of each table, where he can edit data shown, data that will be updated by the routine on the database table. The other two columns in grey are not editable, they are only usable for reference, showing the immediately previous set and the immediately following one.

In other words, after the choice made by user about Segment and Price List, an ActiveX control, or something similar, should let user cycle through the sets pertaining to that double choice and revise, edit data, as per columns and rows indicated.

Is that possible?

I am really stuck at thi s point and I am really looking forward to receiving your help

Many thanks guys

Mark

Bob Phillips
02-03-2012, 09:50 AM
Something like this?

CydMM
02-03-2012, 09:57 AM
Hi XLD,

yes...exactly something like that!! :yes

But your method seems to work out only till COL12 in the Dashboard tables and only when either Residential+Extra-European or Residential+European is selected.

Then, how could we manage to cycle through the different sets on the Dashboard sheet using an ActiveX control, or a similar tool?

CydMM
02-03-2012, 10:05 AM
Hi XLD,

yes...exactly something like that!! :yes

But your method seems to work out only till COL12 in the Dashboard tables and only when either Residential+Extra-European or Residential+European is selected.

Then, how could we manage to cycle through the different sets on the Dashboard sheet using an ActiveX control, or a similar tool?

XLD,

I forgot to underline that I am ideally searching for a way to "directly" edit data displayed on the middle column of the three tables of Dashboard Sheet. I mean, that, after changing some values, they will automatically be updated in the correct place in Database table...would it be possible in your opinion? :dunno

Bob Phillips
02-03-2012, 10:23 AM
I would strongly advise against that, it would need to capture the input, apply to the Database, then re-apply the formula. You should keep input and view separate, not try to make them multi-purpose.

Bob Phillips
02-03-2012, 10:31 AM
Hi XLD,

yes...exactly something like that!! :yes

But your method seems to work out only till COL12 in the Dashboard tables and only when either Residential+Extra-European or Residential+European is selected.

Try this change.


Then, how could we manage to cycle through the different sets on the Dashboard sheet using an ActiveX control, or a similar tool?

Not sure what you mean.

CydMM
02-03-2012, 11:09 AM
It doesn't work...exactly like before

Bob Phillips
02-03-2012, 05:28 PM
No it isn't, it covers all 27 columns now.

CydMM
02-04-2012, 11:17 AM
No it isn't, it covers all 27 columns now.

Yes Xld, you are right.

The formulas you arranged do work out amazingly :clap: but only selecting Residential+European and Residential+ExtraEuropean. :(

When Choosing Residential+Deluxe, and Industrial+European and Industrial+ExtraEuropean, the formulas return #N/D. But this is not because formulas are incorrect, the reason lies on the "architecture" of the price list range and the way the several sets are arranged in segments.

In other words, the pruduct range of the Industrial segment is divided in sets that are different from those of the Residential one. And inside the Residential segment, the Deluxe price list is based upon another different range of sets.

In fact, sorting the sets inside Database sheet, you will see 3 different sets: one for residential (bot european and extraeuropean), one for deluxe and the last one for Industrial (both european and extraeuropean).

Now, the problem is to find the way to let the cells in row 7 of Dashboard change accordingly to the selection made in cells B5 and B6. What I am searching for is a method to avoid user from manually type sets on row 7 but, instead, to work through either a slide bar or any other "automatized" method. Naturally keeping formulas on the rest of the table, but the "core" of the system, of the project I am aiming to is just a way to change values of Sets selected in row 7 through an authomatic method, either by VBA or not.

Do you think is possible?

CydMM
02-06-2012, 09:29 AM
Yes Xld, you are right.

The formulas you arranged do work out amazingly :clap: but only selecting Residential+European and Residential+ExtraEuropean. :(

When Choosing Residential+Deluxe, and Industrial+European and Industrial+ExtraEuropean, the formulas return #N/D. But this is not because formulas are incorrect, the reason lies on the "architecture" of the price list range and the way the several sets are arranged in segments.

In other words, the pruduct range of the Industrial segment is divided in sets that are different from those of the Residential one. And inside the Residential segment, the Deluxe price list is based upon another different range of sets.

In fact, sorting the sets inside Database sheet, you will see 3 different sets: one for residential (bot european and extraeuropean), one for deluxe and the last one for Industrial (both european and extraeuropean).

Now, the problem is to find the way to let the cells in row 7 of Dashboard change accordingly to the selection made in cells B5 and B6. What I am searching for is a method to avoid user from manually type sets on row 7 but, instead, to work through either a slide bar or any other "automatized" method. Naturally keeping formulas on the rest of the table, but the "core" of the system, of the project I am aiming to is just a way to change values of Sets selected in row 7 through an authomatic method, either by VBA or not.

Do you think is possible?

UP

Bob Phillips
02-06-2012, 10:47 AM
Determining it should be relatively simple in itself, but as always, the devil is in the detail.

You have four sets associated with Residential-European, but only 3 in the dashboard. What determines which 3? And what about when there are none, e.g. Industrial-Deluxe.

CydMM
02-06-2012, 11:45 AM
Determining it should be relatively simple in itself, but as always, the devil is in the detail.

You have four sets associated with Residential-European, but only 3 in the dashboard. What determines which 3? And what about when there are none, e.g. Industrial-Deluxe.

The Dashboard tables just show the breakdown of each selected Set. For each one of the three installation types, a table shows the TARGET SET (with yellow colour) in the middle column, and at its left the breakdown of the previous Set, while on its right a column shows the breakdown of the following set.
After the choice made in Cells B5 and B6, some sort of Button or sliding bar should let those columns cycle the sets pertaining to the combination Segment/Price List chosen by the user.

Bob Phillips
02-06-2012, 12:43 PM
So if we had a list in of the the values that are applicable to the Segment and Price List, without the first and last (so that we can have a previous and next) with the lower defaulted. Would that do it?

CydMM
02-06-2012, 12:46 PM
So if we had a list in of the the values that are applicable to the Segment and Price List, without the first and last (so that we can have a previous and next) with the lower defaulted. Would that do it?

Sorry Xld but I am afraid I have not understood what you mean. Would you please explain? :think:

Bob Phillips
02-06-2012, 12:54 PM
What I mean is that if you select Residential European, there are sets of 1.20, 2.80, 3.60, and 5.40, we would create a dropdown of 2.80 and 3.60 in E7 (we would drop 1.20 and 5.40 as we can't do previous and next with those). If you select Residential Deluxe we would use 2.99, 4.80 (dropping 1.80 and 5.90), and so on. If we don't have values, we would use #N/A.

CydMM
02-06-2012, 12:58 PM
Some way or another also the first set and the last one of each combination Segment/Price list must be considered by the Model in Dashboard sheet.

Since I will not be the only one to use that file, I was thinking of a more user friendly contro to cycle through the sets of each combination.
In case of the first set of each combination Segment/Price list, the first column will be balnk....and for the last set...the third column will be blank.

What do you think about that?

Bob Phillips
02-06-2012, 12:59 PM
Now it's my turn to not understand :)

CydMM
02-06-2012, 01:10 PM
Now it's my turn to not understand :)

Just guess the user selects Residential in B5 and Europena in B6, the model should "read" following sets from the database Sheet: 1,20 - 2,80 - 3,60 - 5,40. The "routine" will find 12 rows in the database, beacus each Set of Segment/price list has 3 installation types.

Now, in the Dashboard Sheet, according to the 3 types of installations, the model should have a "slide bar", clicking on which, a routine puts the first set (1,20) in E7 of Dashboard, getting all the other data through the matricial formulas. In D7 there will be blank space, because 1,20 is the first one. So all the other cells in the columns will have #N/D I guess. In F7 there will be 2,80.

The same values will be in I7:K7 (with values related to Installation B) and again in N7:P7.

Now...the user click on the sliding bar and values in D7:F7, I7:K7, N7:P7 do "jump" to the following step....with the result to show
1,20 in D7, I7 and N7
2,80 in E7, J7 and O7
3,60 in F7, K7 and P7

and so on...till teh end of the set for that Segment/Price list.

Bob Phillips
02-07-2012, 08:22 AM
This is what I was suggesting

CydMM
02-07-2012, 08:31 AM
This is what I was suggesting

Hi Xld,

I tried to act on the dropdown menu from the E7 cell, but first of all, that menu is always the same, while it should change accordingly to the selections made as per Segment/Price List.
Secondly, nothing happens after choosing any of the numbers indicated in the dropdown menu. The result on the Column is #N/D in every cell.

Third, there is not any effect on the columns related to the previous set and to the following set.

Bob Phillips
02-07-2012, 08:57 AM
NO, you are off your trolley mate. I just tried it again, the dropdown changes. I am currently staring at Industrial Extra-European with values of 24.5, 29.6 and 54.8, and 54.8 also in the dropdown.

Sounds like you have disable events.

CydMM
02-07-2012, 09:11 AM
NO, you are off your trolley mate. I just tried it again, the dropdown changes. I am currently staring at Industrial Extra-European with values of 24.5, 29.6 and 54.8, and 54.8 also in the dropdown.

Sounds like you have disable events.

No way, my friend.
My Excel 2010 is working at its full funcionality.
I tried again to work on the dropdown menu of cell E7 but the performance is totally out of control.


First of all , in spite of the changes I make to the selection in B5:B6, the different values that the dropdown menu shows don't absolutely fit to the range of sets pertaining to that selection Segment/Price List.
Once selected any of the values of the dropdown menu, the value shown in E7 changes and becomes something very weird...for example...Residential European...once selected in B5:B6 by default your formulas now show some 19,12 in E7...:bug: Where does it come from? Well, from here...if you select the value 2 from the dropdown menu (the sets are with one decimal...here this decimal disappeared.....) in E7 appears a 0, and all #N/D in the rest of the column....only in J7 and O7 there is the right value (2)...but "downstairs" all over the column again the formulas show #N/D.
The same choosing Industrial/ExtraEuropean, for example.I don't think it's my problem Xld...I guess the settings you chase and your codes are not working properly.

Bob Phillips
02-07-2012, 09:18 AM
Well it works here, not much more I can do.

CydMM
02-07-2012, 09:26 AM
Wow...is that all you are able to say? :eek:

Till now, all your changes to the file never created problems here. I have always been able to read the file and act with all Excel functionalities at my disposal.

I don't understand why now it shouldn't be like that anymore.
All modules are running, no other files that may create conflicts are open.
Where should be the problem for such an huge performance problem between the way I read the file here and the way you see the file performing out there? :think:

How can we solve that?

Bob Phillips
02-07-2012, 10:05 AM
What do you expect me to say? I am here, it works fine here. I am not there, so I have no idea why it doesn't work there. I think you have had enough of my time don't you?

CydMM
02-07-2012, 10:13 AM
What do you expect me to say? I am here, it works fine here. I am not there, so I have no idea why it doesn't work there. I think you have had enough of my time don't you?

If it works there, why don't you kindly explain to me what you implemented? This way I could try to reproduce by myself here....or is your worthy time expired?

You know, my friend, if you don't explain the architecture of your "solutions" people attending this very nice Forum will never succeeed to improve their skills about Excel Programming, don't you agree dear friend?

But if are sick of the problem I raised and you are not willing to spend anymore time trying to help me, I can understand that. I have already had enough of your time, right? :thumb

Thanks for your efforts and for your help.