Formula Problems - Lookup, IF, VLookup? HELP...

[mat08h]

Hi,

I have a workbook with multiple worksheets and I am struggling to link some of the data so I can review different bits of data on a single sheet.

I have run 6 reports and used pivot tables to combine the data into a single worksheet named project summary to evaluate project to project, this is working well...

Now I need to review the data by supplier rather than the project but if someone changes the way the data in the project summary is sorted the values don't match the supplier (if this makes sense?) meaning the data is not reliable.

Can you help with a formula that can look at the suppliers listed in column A in the supplier summary sheet and put the value from the project summary next to that supplier regardless of how it is sorted?

I have saved a modified copy of the workbook here - http://www.filedropper.com/suppliercostsummary

 

[jul stev]

Hi,

If you want to make a formula that refer to an active pivot table i think it will keep changing if you sort the pivot.
I think its better if you make the formula that refer to your database.
Try use this in B2 on Supplier summary sheet

=SUMIF('1394'!$J:$J,'Supplier Summary'!A2,'1394'!$F:$F)

 

[mat08h]

Hi jul stev,

Thanks for the quick response...

With your help I am 99% there. The only problem I have now is that when someone sorts the data in the supplier summary sheet by dollar value and not alphabetically the values aren't correct.

Any ideas??

Many thanks in advance

 

[jul stev]

Hi mat08h,

there are 2 ways for your problem :

- Copy all the formula and paste value
- change your formula in B2
=SUMIF('1394'!$J:$J,OFFSET(A1,1,0,1,1),'1394'!$F:$F)

Jul