Hello- Im seeking an Excel tutor- who can you recommend w/ bandwidth? Over the years Ive built up a bunch of specific questions in the following areas which are intriguing me:

1: Arrays, LOOKUPs, and IFs
2: Excel forms
3: Charts repopulate based on dropdown selections
4: Dashboards
5: other misc topics

I understand solutions for some of these have been published already (e.g. on Excel MVP or Microsoft discussion boards) but Id still like someone to walk me through. I cant pay US$40 or $80 or some large amount per hour, but perhaps you know of someone willing to work with me at a lower rate?

Does this sound of interest to you? If so please advise which areas youd be comfortable tutoring on and what your earliest start date would be.

With thanks, Chris

Area #1: In Excel counting, how do you…

a) In the sample data set below, what formula/array will return the count of large red circles? (Example data set, where commas represents cell delimiters, answer should be 2 because large red circle appears twice)

IdNumber,Color,Size,Shape
1,Red,Large,Circle
2,Blue,Small,Triangle
3,Red,Medium,Square
4,Red,Large,Circle
5,Red,Medium,Triange
6,Blue,Small,Circle

b) How do you count and sum on values selected from dropdowns? (i.e. are they stored in cells just as if they were typed in, not selected from a listbox?)

c) How to set up a stacked bar graph of data in (a) based on one attribute (e.g. where thered be 2 mixed bars, one for red and one for blue)?

d) How to set up a stacked/mixed bar graph based on a different attribute? (e.g. where thered be 3 bars: one for all the circles, one for all the triangles, one for all the squares)

e) How to choose between using arrays, LOOKUPs, and IFs?

Area #2: How can you make Excel act like MS Access (when you dont want to use a database program instead…)?

a) e.g. how to add text entry & dropdown tabs to a sheet (called, say, DataInputSheet) with a submit button triggering those values to be added as a new row to another sheet (called, say, DataStorageSheet).

b) preventing cell data on a row from being accidentally altered (fat fingered) if a certain value appears (e.g. if the value Closed is selected as a dropdown value from a column called Status)

c) if you dont have a MS Access report to generate, can you have an MS Word doc that displays a(n embedded) MS Excel table of values in the middle of it, which reads an external xls file and gets the latest values from that xls each time the doc is opened?

d) can you have an MS Word doc that contains an Excel grid in the middle of it, where data can be entered/updated such that it will recalculate on the fly and otherwise act like a live xls in the middle of a doc… though there is no external xls source being read from? (i.e. the Excel table & cell contents are all contained within the doc as an Excel object)

Area #3: How to set up charts where both x-axis and y-axis values can be dynamically selected from dropdowns?

(I have a working example of this, but I cant figure out exactly how it works… there is a term in advanced Excel for this type of functionality… you can see it in the example graph I have… perhaps you could look that up & give a tutorial on how it works)

Area #4. How in Excel to build a dashboard?

(a) Is this solution in the public domain? If so, can we please walk through it? I believe there already must be working xls examples of this, or at least instructions published as to how to make them.

(b) This should include speedometers etc. if possible.

I.e. using data set from #1 above, create a dashboard that will read a user-defined threshold values for when to turn a bar yellow (say, if more than 3 large red circles exist on DataStorageSheet) and when to turn it red (say, if more than 5 are found.)

I.e. progressively shade bars, so that say the first 2 blocks of the bar counting Large Red Circles are green (bec 2 is below the warning threshold), then the 3rd and 4th blocks are yellow, then 5th and above are red?

Most ideal of all, the contents of the dashboard would be customizable based on user-defined choices, i.e. on a Customization tab the user could check/uncheck which quadrants to appear in the dashboard… I know this may be very very advanced functionality typically provided in VB or with third-party controls, but if possible itd be great to have it in pure Excel/VBA…

Tags: data, doc, Excel, excel merge company names different spreadsheets, excel mfc solution, excel plotting sets data different axis, excel remove duplicates name different salary, export java excel best solution, find cramster solution problems, inventory excel sheet solution, joomla different language according domain, large, problems data cleansing excel, red, values, vba excel problem solution, xls

Hello- Im seeking an Excel tutor- who can you recommend w/ bandwidth? Over the years Ive built up a bunch of specific questions in the following areas which are intriguing me:

1: Arrays, LOOKUPs, and IFs
2: Excel forms
3: Charts repopulate based on dropdown selections
4: Dashboards
5: other misc topics

I understand solutions for some of these have been published already (e.g. on Excel MVP or Microsoft discussion boards) but Id still like someone to walk me through. I cant pay US$40 or $80 or some large amount per hour, but perhaps you know of someone willing to work with me at a lower rate?

Does this sound of interest to you? If so please advise which areas youd be comfortable tutoring on and what your earliest start date would be.

With thanks, Chris

Area #1: In Excel counting, how do you…

a) In the sample data set below, what formula/array will return the count of large red circles? (Example data set, where commas represents cell delimiters, answer should be 2 because large red circle appears twice)

IdNumber,Color,Size,Shape
1,Red,Large,Circle
2,Blue,Small,Triangle
3,Red,Medium,Square
4,Red,Large,Circle
5,Red,Medium,Triange
6,Blue,Small,Circle

b) How do you count and sum on values selected from dropdowns? (i.e. are they stored in cells just as if they were typed in, not selected from a listbox?)

c) How to set up a stacked bar graph of data in (a) based on one attribute (e.g. where thered be 2 mixed bars, one for red and one for blue)?

d) How to set up a stacked/mixed bar graph based on a different attribute? (e.g. where thered be 3 bars: one for all the circles, one for all the triangles, one for all the squares)

e) How to choose between using arrays, LOOKUPs, and IFs?

Area #2: How can you make Excel act like MS Access (when you dont want to use a database program instead…)?

a) e.g. how to add text entry & dropdown tabs to a sheet (called, say, DataInputSheet) with a submit button triggering those values to be added as a new row to another sheet (called, say, DataStorageSheet).

b) preventing cell data on a row from being accidentally altered (fat fingered) if a certain value appears (e.g. if the value Closed is selected as a dropdown value from a column called Status)

c) if you dont have a MS Access report to generate, can you have an MS Word doc that displays a(n embedded) MS Excel table of values in the middle of it, which reads an external xls file and gets the latest values from that xls each time the doc is opened?

d) can you have an MS Word doc that contains an Excel grid in the middle of it, where data can be entered/updated such that it will recalculate on the fly and otherwise act like a live xls in the middle of a doc… though there is no external xls source being read from? (i.e. the Excel table & cell contents are all contained within the doc as an Excel object)

Area #3: How to set up charts where both x-axis and y-axis values can be dynamically selected from dropdowns?

(I have a working example of this, but I cant figure out exactly how it works… there is a term in advanced Excel for this type of functionality… you can see it in the example graph I have… perhaps you could look that up & give a tutorial on how it works)

Area #4. How in Excel to build a dashboard?

(a) Is this solution in the public domain? If so, can we please walk through it? I believe there already must be working xls examples of this, or at least instructions published as to how to make them.

(b) This should include speedometers etc. if possible.

I.e. using data set from #1 above, create a dashboard that will read a user-defined threshold values for when to turn a bar yellow (say, if more than 3 large red circles exist on DataStorageSheet) and when to turn it red (say, if more than 5 are found.)

I.e. progressively shade bars, so that say the first 2 blocks of the bar counting Large Red Circles are green (bec 2 is below the warning threshold), then the 3rd and 4th blocks are yellow, then 5th and above are red?

Most ideal of all, the contents of the dashboard would be customizable based on user-defined choices, i.e. on a Customization tab the user could check/uncheck which quadrants to appear in the dashboard… I know this may be very very advanced functionality typically provided in VB or with third-party controls, but if possible itd be great to have it in pure Excel/VBA…

Tags: data, doc, Excel, excel merge company names different spreadsheets, excel mfc solution, excel plotting sets data different axis, excel remove duplicates name different salary, export java excel best solution, find cramster solution problems, inventory excel sheet solution, joomla different language according domain, large, problems data cleansing excel, red, values, vba excel problem solution, xls