Hi,

Is there a way to get a value to stay when you lookup a value on another sheet and then filter that sheet? Currently if I filter the source obviously the records stay are only the ones which are in that filter Im after all records regardless of if I filter the source or not.

Thanks

You might need to provide some more information here.

Such as a sample workbook or at least what are you using as a formula.

I cant get it to happen anymore but I cant get my vlookup working either? Ive attached a sample which should be clear but it comes back NA in the vlookup result?

Attached Files

SampleData.xlsx (10.6 KB, 8 views)

It's because you can't use the Vlookup to look backwards.

From the Excel help:

Try this instead...

=IFERROR(INDEX('Data Sheet'!B:B,MATCH(B3,'Data Sheet'!C:C,0)),"")

Thank you - can you tell me, how do I use multiple criteria to lookup info. For example the attached a company can have a variety of products delivered all to the same site and all under the same delivery number but they'll have different total values (per product). How do I go across the spread sheet to return the total value in sheet2 Product 2 #? Thanks

Attached Files

SampleData.xlsx (10.9 KB, 7 views)

How about something like this...

I would also consider investing some time in learning how to use a Pivot Table. PT's make consolidating data a breeze.

Attached Files

SampleData (1).xlsx (16.1 KB, 7 views)

That's perfect - just going to read up on sumif so I can understand whats going on.

How would a pivot table benefit this kind of setup?

I updated the attachment in post # 6 to give you an idea of what a PT can do for you.

Again, PT's are a powerful tool in Excel and can be used in many different ways. Worth learning.

Thanks again - I think the data source is from a pivot table. Could you tell me how to overcome the following please? Ive attached an updated example whereby the delivery numbers all be it the same as its preceeding delivery number (in some cases) isn't in the cell and therefor throws the formula to lookup the value.

It wont always be the case there are multiple product types for a delivery number as per "TestCompany2" in the attached which only has one delivery number for one product.

Attached Files

SampleData (1).xlsx (17.7 KB, 9 views)

On the Data Sheet

• Highlight >> B3:B9
• Select F5 >> Special
• Toggle blanks >> OK
• Select the equal sign >> up arrow >> Ctrl + Enter
• Highlight column B >> copy >> pastespecial as values

I think because its a pivot table I cant use the above suggestion, Im just looking how the pivot table has been setup to remove the toggle +/- buttons on the delivery note (my sample doesn't have the PT as Im not sure how to set them up yet).

Any thoughts?
Thanks

Ribbon: PivotTable tools, Design tab, Layout section, Report layout dropdown, choose Repeat all item labels.

Thank you sooo much that's worked a treat.

Is there a way to query a cell or cell(s) for data and return the result. Once the result has been returned that result stays regardless if you filter the source or not kinda like a copy and paste of data?

I would say possibly, but when you attach a sample workbook with a before and after it much easier to suggest a solution.