Index & Match Formula & MACRO

[Harry Basra]

I have created a macro that uses a pivot table to extract the a list of reference numbers from a weekly information sheet containing data for the whole country. I use the formula below to match the clients reference numbers within the weekly M.I with the numbers that are relevant to my region. The formula i used is shown below.

=INDEX(PO!A:A,MATCH(Sheet1!A:A,PO!A:A,0))


The problem I'm having is when I receive the new weekly information, I run the Macro and a new pivot table is created in a new sheet. However the formula doesn't recognise that a new sheet has been created. Basically I need the "Sheet1!A:A" part of the formula to be relevant to the new data created by the pivot table every time I run the macro.

Can anyone help please?

[dilipandey]

Hi Harry,


welcome to the forum.

Sheet1:A:A - this column A will have new data appended at the bottom everytime you run the macro and you want this new data to be considered in formula... correct? If so, can you upload a sample workbook so that I can try a quick attempt ? Thanks


Regards,
DILIPandey
<click on below * if this helps>

[Harry Basra]

Hi DILIPandey

Thank You very much for your response. Yes you're right in thinking that every time I run the macro I want this new data to be considered in formula. I am currently unable to uploaded a sample at the current time due to restrictions at work. However I will get one uploaded towards the end of the day when I am home.

Thank You

Harry

[dilipandey]

Okay... no problem.

Looking forward to the sample...



Regards,
DILIPandey
<click on below * if this helps>

[Harry Basra]

Book1.xls

Hi


I have uploaded a sample. The data has obviously been cut down a lot just to make it easier to decipher. You will find i haveincluded a comment in cell G8 of sheet 1. I hope this helps

Thank

Harry

[dilipandey]

Okay... I have updated your macro.. try again

Book1.xls

Regards,
DILIPandey
<click on below * if this helps>

[Harry Basra]

Thank you very much for this. I cant look at it until I'm home afraid due to restrictions at work. What exactly have you done, as I am going to have to apply it to the main workbook saved on my work drive?

Thank You

[dilipandey]

I have edited your existing macro a little bit.

removed code line in beginning before ActiveWorkbook.PivotCaches.....
and updated below line:-
ActiveCell.FormulaR1C1 = _
"=INDEX('Order Numbers'!C[-4],MATCH(C[-2],'Order Numbers'!C[-4],0))"

thats it

Regards,
DILIPandey
<click on below * if this helps>

[Harry Basra]

Thank you very much for you help DILIPandey

[dilipandey]

you are welcome Harry Basra

Please mark this thread as [SOLVED].. thanks.


Regards,
DILIPandey
<click on below * if this helps>