If then

[trebor1956]

Hi,
I have a worksheet (sheet1) that has a date in col E,G,I,K,M,O,Q,S,U,W,Y,AA. In cols F,H,J,L,N,P,R,T,V,X,Z there may be the letter N. I would like another sheet (sheet2) to show the letter N and the date that is in the previous col and the data in cols B, C, D of the same line. There are 110 lines of data sheet 1.
Hope this makes sense, all help much appreciated.

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Date	b	c	d
2	3/3/2019	b4	c4	d4
Sheet2

Cell Formulas
Range	Formula
A2	A2	=SUMIF(Sheet1!F2:Z110,"n",Sheet1!E2:AA110)
B2:D2	B2	=INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!$F$2:$Z$110)/(Sheet1!$F$2:$Z$110="n"),1))

The A2 formula only works if you have just one N anywhere in your range. If there could be more, you could adapt the B2 formula, which just looks at the smallest row with an N in it. Also, make sure you format the A2 cell (wherever you want it on your sheet) as a date.

 

[trebor1956]

Hi Eric,

Thank you for this. The likelihood is there will be more than 1 "N" so how would the B2 formula need to be adapted please?
Could you disect the B2 formula explaining what each part actually does please? - I like to try and understand any formulas that I use. Please see copy of part of sheet1 to explain further. So, if "N" is entered into any of the columns after a date I want a formula on sheet2 to take the first 4 cols plus the date preceding the "N" and the "N". ie. where there is "N" in line 2 col P - I would like to show on sheet 2 cols A,B,C,D and O,P.


A B C D E F G H I J K L M N O P

Walk Order

Extinguisher Number

Location

Type

Check Date

Service Date

Check Date

Check Date

Check Date

Check Date

Check Date

Check Date

Check Date

Check Date

1

15

Side double doors

Foam 6 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

N

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

2

95

Exams Office

CO₂ 2 kg

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

3

14

Bar

Powder 2 kg

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

4

5

Dining room

Water 9 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

5

6

Nursery

Foam 6 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

6

7

Nursery

Water 9 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

7

9

Nursery kitchen

Powder 2 kg

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

8

8

Nursery kitchen

Blanket

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

9

10

Main kitchen

Blanket

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

10

11

Main kitchen

Foam 2 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

11

13

Main kitchen

Wet Chemical 6 ltr

04/Jan/19​

19/Feb/19​

15/Mar/19​

17/Apr/19​

16/May/19​

17/Jun/19​

24/Jul/19​

23/Aug/19​

19/Sep/19​

22/Oct/19​

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Date	b	c	d
2	15/Mar/19	5	Dining room	Water 9 ltr
Sheet2

Cell Formulas
Range	Formula
A2	A2	=IFERROR(INDIRECT("Sheet1!"&TEXT(AGGREGATE(15,6,(ROW(Sheet1!$F$2:$Z$110)*100+COLUMN(Sheet1!$F$2:$Z$110)-1)/(Sheet1!$F$2:$Z$110="n"),1),"R00C00"),0),"")
B2:D2	B2	=INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!$F$2:$Z$110)/(Sheet1!$F$2:$Z$110="n"),1))

If the dates are all the same in each column like your example, you can use this A2 formula instead:

=IFERROR(INDEX(Sheet1!$A$2:$Z$2,MOD(AGGREGATE(15,6,(ROW(Sheet1!$F$2:$Z$110)*100+COLUMN(Sheet1!$F$2:$Z$110)-1)/(Sheet1!$F$2:$Z$110="n"),1),100)),"")


As far as how it works, let's look at the B2 formula. The AGGREGATE function has some array processing in it. If you give it a range, it will calculate everything in the range. So if we give it a range of ROW(F2:G4), this returns this array
{2,2;
3,3;
4,4}

Then if we give it (F2:G4="n") and let's say G3 = "n", then we get this array:

{FALSE,FALSE;
FALSE,TRUE;
FALSE,FALSE}

Now we take the first array and divide by the second array, and TRUE is equivalent to 1 and FALSE is equivalent to 0, we get:

{#DIV/0!,#DIV/0!;
#DIV/0!,3;
#DIV/0!,#DIV/0!}

Now AGGREGATE takes over. The 15 means get the SMALLest value, the 6 means ignore errors, so we get 3, the row where the N is found. We give that to INDEX and apply it to column B to get your value. If there are multiple cells with N in them, then it will just pick the lowest row.

The A2 formula works under the same principle, except it uses the row times 100 + column, so instead of just 3 for G3, we'd get 306 (row 3 column 6). Then we take the 306, subtract 1, and give it to TEXT with a format of "R00C00", so it converts to "Sheet1!R03C05". Then we use INDIRECT to convert that text value to a range and we get the value.

The alternate A2 formula isn't much shorter, but it doesn't use the volatile INDIRECT function.

Hope this helps!