A couple of dilemmas

[adventurer_1]

Got a couple of question for all you experts out there - your wisdom would be much appreciated!

The dilemmas are as follows:

(1) I have a series of budgets for project partners. Each document has a summary sheet profiling the individual partner budget and a summary of their expenses to date. A second sheet lists their actual costs, to be input by the partner, and feeding the summary columns on the page one. The feed is done through selecting of the right cost carrier from a drop down list for each expense, where the cost carrier is a unique identifier for each budget line against which the expense can be claimed.

It is critical that the user enters the correct cost carrier, or else the entire budget can go to the dogs. Unfortunately the budget line titles in the summary page are long, while the unique identifiers in themselves do not hold sufficient information to remind the user of their detail. I would like to eliminate the risk of human error, and prevent the user from having to flick between the various sheets in inputting of the costs.

I have been thinking about a couple of solutions, but hit some implementation snags.

Idea A: Have a drop down list with full budget line titles, which when selected remain only as the relevant cost-carrier. What is unclear is how to return only part of a drop down list item into the cell, or else whether it is possible to use an IF or some other function for the task. Also, how does one ensure that the empty lines are not listed in the drop down list?

Idea B: have a pop up window or note once the cell is clicked, containing the reference data, assisting the user with the information for input. However, is it possible to have this non-static? For example new cost-carriers may be added at a later date, and ideally the window would update itself automatically with the new information. (and for the impossible, can it be done without VBA? i have zero experience with it, and need to turn this around fast )

Idea C: anything you think will be an alternative good solution, solving the puzzle ray:

(2) Finally, probably an easier dilemma. For each project employee there is time-sheet for the duration of the project (date, hrs worked, tasks completed). Is it possible for the partner to print a part of the timesheet relevant only to a particular reporting period? (e.g. the partner wants to print 1/3/10 to 31/7/10) What would be the best way to achieve this?

Thanks a lot in advance for all your input!!

 

[sunnyland]

Idea A:

Using VBA, you could have a drop down list that follow the user selection and that a variable linked cell. This drop down list can hold more than 1 column meaning in the drop down, you could have ie the Account Name in one Column with in another column the Account Number. So the user will select the meaningful account name but the account number will be placed in the cell.
Right clicking on the drop down list you can click on properties....

boundcolumn will take value 2 [meaning the 2nd column will be used [account number]

columncount will take value 2 [you want the drop down to display 2 columns]

listfillrange property will have to be Summary!a:b
ie:
To have this happening: you need to put the input range of the combo to ie:
Summary!a:b if for example you had on the summary sheet the accountnames in column a and accountnumber in column b.
----------------------------------------------

linkedcell will be attributed by vba see following code




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'supposing the column 2 in the spreadsheet is the column where
'will go the result not the summary sheet
If Target.Column = 2 Then
With ComboBox1
'visible and move with the selection by setting left and top to left and top of active range
.Visible = True
.Left = Target.Left
.Top = Target.Top
'linkedcell is change to reflect the current cell address
.LinkedCell = Target.Address
End With
Else
'if the selection is in another column
'hide the drop down
ComboBox1.Visible = False
End If
End Sub

If you never programmed before this is may be a bit complicated to follow.

But this is an idea.