Which formula can I use? Is this possible?

[Samsung88]

Hi All,

I do not know which formula to use for what I am trying to acheive, so any help would be fab...

I am tyring to put a formula in sheet 'sheet 4', cell A2, that says:

if in sheet 1, "further Investigation" is in column D, and a figure greater than 0 is in column O,- then show me this whole row (on sheet 4)

I hope I have been clear enough! I am unsure that this is even possible (due to the "whole row" point)

 

[Scrappy666]

Hi,

This is possible, but it might not be as easy as you were hoping.

In sheet 1, enter a column at the beginning of your data. (So you have a new, blank column A).
Enter the below in cell A2:

=if(and(d2="Further Investigation",o2>0),a1+1,a1)

Drag this formula down to the bottom of your data.
(columns D and O may need to change after adding a new column A)


Next, on sheet 4 in cell A2, enter the below:

=iferror(vlookup(row()-1,Sheet1!$A:$ZZ,column()+1,false),"")

Drag this formula down as far required (to include all instances where Further investigation and >0)
And drag this across to cover all columns required also.


If it doesn't work just let me know, i haven't used example data for this, just manually typed.
Sorry if it has a mistake!

Thanks,
Dan

 

[Samsung88]

Hi scrappy,

Thank you for the above!!

I think there is an issue (that I didnt previously mention) - my column for "further investigation" and my column "O" both have formula in them- and the "further investigation" is the result of a formula- along with column 0.

The formula in column D is:
=IF(K17<8,"Further Investigation","Available")

The formula in column O is:
=(N21-M21)/7

Therefore, when I type in the above formula- the column is still bringing up "0"

Thank you

 

[Samsung88]

Please ignore the above post, as I now have the first half of this formula working, however the second formula is not working for me. Am I supposed to be entering values into the empty brackets?

Thank you!