Cell references does not change when sorting

Question

Hi,

i have seen several queries related to sorting, but not entirely sure if the problem is the same as mine so hopefully someone can help shed some light on whats going wrong for me (i'll be as detailed as i can):

I have an excel file with a number of sheets, one is called "Invoice List" and the Other "Exchange Rates".

The "Exchange Rate" sheet populates the exchange rates from a different file using Indirect formula.

The "Invoice List" has a number of invoices where each invoice has the month of when they were issued and the currency they were issued in along with the supplier name.

I have a formula in the "Invoice List" sheet which brings back the relavent exchange rate depending on currency and month. this formula populates the correct value in each instance.

The "Invoice List" needs to be sorted in a certain order as the data from it populates other sheets. I have a simple macro which sorts the invoice list alphabetically ascending (the supplier name) and by month (again ascending).

When a new invoice comes in, it is entered at the bottom of the invoice list. when the invoice is entered to the bottom of the invoice list, the formula to bring back the correct exchange rate looks something like this (i have simplified it a little, in the instance below, the new invoice is entered in row 438, i can show you the actual formula i use if it is required):

=vlookup(Month(M438)&Currency(Q438)),'Exchange rates'!$C:$O,MATCH(MONTH('Invoice List'!M438),4,false) - this formula works fine

When i sort the list the invoice above moves to a different row, lets say it moves to row 200, but the formula for the exchange rate does not change accordingly and looks something like this:

=vlookup(Month(M200)&Currency(Q200)),'Exchange rates'!$C:$O,MATCH(MONTH('Invoice List'!M438),4,false)

i have checked for absolute cell references, so that the formula is not locked in to cell M438. i dont have anything like that. I have noticed that invoices which were already in the list does not have a problem with bringing back the correct exchange rate when sorted, so if i dragged or copy the formula for the exchnage rate from one of the older invoices, the correct value is displayed (the formula is exactly the same).

anyone have any ideas as to whats is going on? Like i said, the formula works, it simply goes wrong when the list is sorted.

Thanks

Answer 1

... this formula works fine

···

=vlookup(Month(M200)&Currency(Q200)),'Exchange rates'!$C:$O,MATCH(MONTH('Invoice List'!M438),4,false) 

I do not see how that formula actually works, but if the formula belongs on the Invoice List worksheet, remove the worksheet reference from the M438 in the MATCH() function.

=vlookup(Month(M200)&Currency(Q200)),'Exchange rates'!$C:$O,MATCH(MONTH(M200),4,false)

The worksheet reference is causing Excel to think that you are referring to M438 on a difference worksheet and it should not be changed relative to its new sorted position.

Answer 2

This answered my question completely! Thanks from me as well!

Answer 3

Jeeped you beauty....cheers for that, seems to be working now. did not think of that at all as the reference to the sheet name came up when i was typing the formula so just assumed excel would know what to do with it.....that will teach me, assumption being the mother of all.....(i'll let you finish that)...thanks again.