Problem extracting data to another worksheet

[Spellbound]

Hi everyone

Looking for some help again, having spent the last few hours on this problem without success.

To simplify matters, I have attached an example of what I am trying to do using the two worksheets from a much larger workbook.

The information contained in the MAIN DATA worksheet is shown as values only but is actually the result of many extensive formulas plus a pivot table.

The OUTPUT worksheet shows how I would like the data to appear using the data taken from the MAIN DATA worksheet.

In essence I am looking to transfer the information for a particular loan and customer to the OUTPUT worksheet in a vertical format.

I have managed to make it work artifically but not in the way shown in my example, so that the particular loan starts from the correct date of entry and then continues to list itself until it reaches the current weeks entry.

Not sure how clear I have explained this but hopefully someone will know how best to make this work.

Thanks in advance ...Spellbound

[rylo]

Hi

1) the data seems to be coming from the row where the week number is >0. Please confirm
2) the data in the output doesn't seem to match the relevant week - it seems to be from the previous week. Again, can you please confirm that this is the right output data for the given input.

rylo

[Spellbound]

Hi Rylo

Not quite sure that I follow your queries.

Will try to explain the example as follows:

Firstly, I run each workbook on a yearly basis starting from week 1 running thru to week 52 or 53 in some cases.

In the MAIN DATA worksheet, the information to the left of column J is static and as a customer takes on a new loan then this is added accordingly. In the example, loan 672 was inputted week ending 15/03/08 which reflects an opening balance for that week to allow for subsequent payments etc to be deducted from week 12 onwards i.e. 22/03/08.

To confuse matters the company that I work with class the 1st week of payment as week zero not week 1, which may be causing the visual confusion. In other words in terms of payment history; week 12 is week 0 and week 13 is week 1 in this example.

Hope this clarifies the situation better.

Spellbound

PS: Just discovered that there was a missing payment in the original example; so I have corrected that in the attached example ...apologies for that.

[rylo]

Hi

Have a look at the numbers in columns I:K of the attached. Does this return the correct numbers? They are slightly different from your example results.

rylo

[Spellbound]

Hi Rylo

Have been playing around with your solution but without success.

Firstly I need to avoid the use of helper cells etc, as the results of this worksheet will need to be printed off and I do need the results to be as shown.

I have attached one of my revised test workbooks, which includes additional data on the MAIN DATA worksheet; which may assist better. The MATCH on just the loan number would be prone to error, so you will see that I have used an INDEX / MATCH array to ensure the correct data is elected.

Whilst I use the formulas in the example to find the first week of the loan; the problems occur as to when the autofill of the cells should stop.

The two main instances are when the current week number is exceeded and when the loan balance is zero. At the moment, my formulas produce error messages in some situations or just carry on beyond the current week.

Currently my workbook is about 30mb and it seems to take ages just to save the data, so I also felt that my method was probably not the cleanest solution and just hoped that there might be a more efficient solution to this problem.

Hope this additional information is useful ....spellbound

[rylo]

Hi

1) The helper column can be removed in the final solution - it was just there to bring back numbers to check as I didn't get the same results
2) Why is the load number prone to error? From the sample given, it seems to be unique. Why is this number a problem?
3) Cancelling the autofill output and stopping when the output is 0 shouldn't be a problem (famous last words...) so I'll worry about them when bringing back the correct data is resolved.


rylo

[Spellbound]

Hi rylo

Will try to clarify your queries as best possible.

1) Understand now about the 'helper' cell but your original solution did not produce the correct results. In the attached file, I have modified the output sheet again so that the 'starter' details are now located in G1 to G5, which means that Row 1 of the loan listing always follows on from this data. It also helped to resolve a problem with existing loans that started prior to 01/01/2008 as my workbooks are started afresh each year.

In order to reduce the size of the example workbook, I have manually inputted the figures for Columns E & F; however the Collect data (E) will be obtained from a Pivot Table and the ADJ data (F) from a formula.

To clarify the situation in the attached example, all the figures shown are correct from Row 10 to Row 40.

2) The reason I mentioned the problem with the loan number is that I have to follow the pattern used by the Company that I work with; in that they only use the last 3 digits of the loan number. Hence it may be possible to have the same 3 digits against more than one customer.

3) As you will see my way of ending the autofill does'nt work.

Thanks for your patience ...spellbound

[Spellbound]

Hi Rylo

Just thought you might like to see the results that I have obtained using various formulas to transfer the data that I required to the OUTPUT DATA worksheet.

Please note that to simplify matters, I have used a constant figure in the COLLECT column; although this will be generated by a Pivot Table in my working spreadsheet.

However, although I have tested it with most of the examples and it seems to be working properly; I would still like to find a more refined solution for transferring the data bewteen the two worksheets.

spellbound


PS: Have just come across another problem in trying to create a list box for the data input but I will start another thread for that issue.