Obtain last value in row from another worksheet!

[Spellbound]

Hi all

Stumped again, been trying various combinations of Index, Match & Lookup to produce the desired result but with no success.

I hope the attached workbook will explain the situation. In the 'Test' worksheet, there is an example of the cells to match, with the result obtained from the 'Collections' worksheet.

The 'ID' and 'NUM' are primary matches, which should work on their own but there is an optional third match using the 'DATE' cell.

The result has to be obtained from the last filled 'Balance' in the matched row as this worksheet expands by 6 columns each week. I would prefer not to use a helper column unless absolutely necessary.

TIA ...David

[teylyn]

Hello,

try this:

Please Login or Register  to view this content.

Adjust ranges to suit, but keep them as lean as possible, otherwise the calculation may become slow.

cheers,

[Spellbound]

Hi teylyn

Thanks for the quick response. I did'nt realise you could link the 3 matches in that way.

I will try out in my main workbook to see how it goes. I have pruned the 'Collections' worksheet down to about 1500 rows at the moment but it will probably be up to around 3000 rows by the end of the year.

Regards ...David

[Spellbound]

Hi

Managed to do some testing with that formula and in essence it works but that is based upon a fixed last column.

The result has to be obtained from the last filled 'Balance' in the matched row as this worksheet expands by 6 columns each week. I would prefer not to use a helper column unless absolutely necessary.

However, as per my original post, the range will expand each week by 6 columns, so the last column is variable. I have tried incorporating various 'find last cell' methods with this formula but no success.

There are times when when the formula may produce a "N/A" error due to their being no value for the matching combination. Is there any short way of resolving this without resorting to an IF(ISERROR) formula, which makes it quite lengthy.

Thanks ...David

[teylyn]

Hello,

when you say "the range will expand each week by 6 columns" I assume you mean rows, not columns?

If so, use dynamic ranges to define the data range, keeping it to the minimum of required cells.

[teylyn]

Ignore the above. I was in a different movie.

Try

Please Login or Register  to view this content.

The last column in the data will be determined by the offset formula. There are other ways to achieve this, but this struck me as the shortest formula, although volatile (and may result in slow worksheet calculation). But this only occurs if you use the formula in bulk. For just a few instances, it should be fine.

cheers,

[Spellbound]

Hi teylyn

Thanks for that last formula, it does seem to work correctly in my tests.

I have incorporated it into a mini version of my main workbook, which is attached here.

The cells utilising this formula in the 'Results' worksheet are shown in red with the yellow background. As you will see by changing the ID, the number of cells using this formula fluctuates. Just for reference, there will also be a formula in K7, linked to a Pivot Table but left out in this example.

This is already resulting in slow calculations, partially due to the Worksheet code used to add and delete rows but still very noticeable in the new formula cells.

Just wondering if there is any way of streamlining either the code and/or formulas to make it run more efficient in reflecting the changes. As you will appreciate, when I add these to my main workbook, it is going to be even slower.

Thanks for your continued efforts ...David

PS: Just to keep things straight; the code in that worksheet was put together by myself and other members in a recent post on VBA Express Forum.
[URL="http://www.vbaexpress.com/forum/showthread.php?p=234286#post234286"/URL]

[teylyn]

You could try this:

Please Login or Register  to view this content.

It does not use Offset() and will calculate much faster.

[Spellbound]

Hi teylyn

I modified the Worksheet code slightly by changing Application.CalculateFull to Me.Calculate, as this was re-calculating the whole workbook each time and I also moved the Me.Calculate to after the last End If, which also seemed to help.

Not sure if this is correct!

I then created a duplicate workbook and used the latest formula in Column H of the Results worksheet. Running tests on both workbooks gave me the impression that the latest formula is marginally but not noticeably quicker.

I was trying to adapt the new formula to work in I7 & J7 but much to my surprise, I could not get the formulas to work in those columns; no matter where I tried to insert the column adjustment. Can you point me in the right direction?

In hindsight, maybe I should have left this in the programming section, since the worksheet code may need refining as I mentioned in my previous post.

Regards ...David