Countif, index, match

[n1kk1]

hello, i have the attached spreadsheet for invoicing/receipt purposes and use the above formula to return data from my main ('Art Log') worksheet.

I am now trying to set up a price list but I only want the cells to return items that have not been returned, i have a column with the return date, for instance in worksheet 'PRICE_LIST' I need the Artist Name and Description and Sale price to be returned from the ''Art Log' worksheet but only if column U does not have a date in (ie it has been returned).

Could anybody help with this, I have attached the spreadsheet - I'm not sure if this is the correct formula I should be using for this

Thanks

[DonkeyOte]

Might help if you either

a) ensure file isn't password protected

or

b) give details of the password...

[n1kk1]

aaah yes that probably would help!

[DonkeyOte]

To keep things as simple as possible I would suggest you create a key on 'Art Log' sheet, eg:

Please Login or Register  to view this content.

You can then use this key to quickly retrieve the appropriate info. on your Price List sheet.

First in G1 identify the number of items that are to be returned:

Please Login or Register  to view this content.

With the above in place you can then look to return the info.

Please Login or Register  to view this content.

Similar logic applied to remaining columns - simply adjust column ref. in INDEX to match that containing the relevant info. (eg Col C for Invoice No. etc)

Note: your layout restricts the no. of records you can list of course.

EDIT: I suspect you will want to limit further based on specific Client Code ?

[n1kk1]

Hi, thank you yes I understand this logic as it is something I have used on other worksheets.

The difference with the price log is I only want to return prices for items we hold, so i need a formula that says returns all this only if column U in Art Log (date returned) is BLANk ie does not have a date in, hence we hold it in stock - that is why im not sure if the above formula would work?

Any advice would be great - thanks

[n1kk1]

another thought is i do not want it to return items that have been sold either so I want to return everything apart from items that appear in 'SOLD' column K and 'date returned' - column U

[DonkeyOte]

The difference with the price log is I only want to return prices for items we hold, so i need a formula that says returns all this only if column U in Art Log (date returned) is BLANk ie does not have a date in, hence we hold it in stock - that is why im not sure if the above formula would work?

Yes it would.

another thought is i do not want it to return items that have been sold either so I want to return everything apart from items that appear in 'SOLD' column K and 'date returned' - column U

In which case simply modify the INDEX key formula to include this additional test, ie:

Please Login or Register  to view this content.

[DonkeyOte]

In retrospect - if the intention is that the Price List sheet is to be relevant to the Artist ID as selected in Receipt sheet (H11) then use:

Please Login or Register  to view this content.

The remaining formula are the same however note I missed the all important exact match criteria from the MATCh function so it should have read:

Please Login or Register  to view this content.