I have a list of contracts in a column. A separate table lists the months along the top row, and below each month in a column, I intend to enter the contracts that end in that month. I want this to enter the termination month next to the contract numbers in the vertical column.
How do I do this?
Thanks
Peter:spin:
Hi Peter, welcome to the Forum!
How weeded are you to this design? I think it will take a VBA function to do what you are asking. Before writing such a function, have you considered entering the the termination month in your vertical list along with the contract number and then using Excel to produce the table by months, for example by a data extract?
Unfortunatley, I am working with an exisiting spreadsheet, so the structure is already laid out for me. I could do it manually, but this would be slow, and most importantly, offer no audit trail.
VBA sounds frightening
Peter
QuoteDisplay MoreOriginally posted by peter14
I have a list of contracts in a column. A separate table lists the months along the top row, and below each month in a column, I intend to enter the contracts that end in that month. I want this to enter the termination month next to the contract numbers in the vertical column.
How do I do this?
Thanks
Peter:spin:
As Derk suggests, it would be far easier to enter the ending month of each contract in the vertical column next to the contract number, and let the automated bit be the 'filling' in of the contracts in the 'calendar'....
The way you have decided/been advised to do it will be very difficult...
Here's a very simplified example using a pivot table to show when contracts will end...
peter14
Are you able to post a sample of data layout and the output format you're trying to achieve?
I attach a sample from my spreadshhet. the idea is to list the ending contract numbers below the appropriate month number in row 35, and for that to change the ending month in column BC for the right contract. I have shown what I want for contract 2971. Hope this helps
Peter
The simplest way to deal with this would be to maintain a list elsewhere (another sheet or area ..other than below the month number in row 35) and use a look up table.
The challenge is finding the values where you want them located.
I would question whether you're making it hard for yourself with the spreadsheet structure. The best rule in spreadsheet design is to keep the data entry as simle as possible then let the spreadsheet do the work (analysis) for you. The moment you have to think about where you enter the data (and move to get to the entry point) you're going against this principle.
Anyway bear with me ...working to a solution...
Hmm. At the moment the entries in BH and beyond depend upon the end month being entered in column BC. So the logic will get circular if we try to calculate the end month from those columns. Something has to give. Somehow the end month has to be known and entered.
The idea is to get someone unskilled to enter the terminated contracts in a list (which can be easily checked) and the spreadsheet will do all the work of calculating the payments in the columns to the left of BH.
Hope that helps
Peter
Peter
Try this...
The Contract Completion date is entered in its own worksheet....
Peter
I've made some changes to the previously posted spreadsheet....note that this illustrates two different methods...the formulas are not yet complete...see grey cells in Column BC as an indication of the sort of lookup table formula thart could be used.
Note the use of dynamically named ranges.
Thanks for the help everyone:tumble:
Don’t have an account yet? Register yourself now and be a part of our community!
