Complex - Multiple Criteria

dunmore83 · Jul 7, 2015

Excel 2012

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

2

Ear Tag

Sex

NLIS

Birth Date

01-Jul-14

02-Jul-14

03-Jul-14

04-Jul-14

05-Jul-14

06-Jul-14

07-Jul-14

08-Jul-14

3

4

5

1658

Heifer

-

04.09.11

04-Sep-11

1,031

1,032

1,033

1,034

1,035

1,036

1,037

1,038

6

2112

Heifer

982123490405654

10.08.12

10-Aug-12

690

691

692

693

694

695

696

697

7

272

Heifer

982123490425668

27.08.12

27-Aug-12

673

674

675

676

677

678

679

680

8

2282

Heifer

982123490425757

28.09.12

28-Sep-12

641

642

643

644

645

646

647

648

9

3007

Heifer

982123490425176

24.01.13

24-Jan-13

523

524

525

526

527

528

529

530

10

3008

Heifer

982123490417932

24.01.13

24-Jan-13

523

524

525

526

527

528

529

530

11

3018

Heifer

982123490425858

29.01.13

29-Jan-13

518

519

520

521

522

523

524

525

12

3026

Heifer

982123498847561

31.01.13

31-Jan-13

516

517

518

519

520

521

522

523

13

3040

Heifer

982123490449063

06.02.13

06-Feb-13

510

511

512

513

514

515

516

517

14

3042

Heifer

982123490417737

06.02.13

06-Feb-13

510

511

512

513

514

515

516

517

15

3053

Heifer

982123490405560

07.02.13

07-Feb-13

509

510

511

512

513

514

515

516

16

3052

Heifer

982123490449125

07.02.13

07-Feb-13

509

510

511

512

513

514

515

516

17

3051

Heifer

982123489462454

07.02.13

07-Feb-13

509

510

511

512

513

514

515

516

18

3055

Heifer

982123490425967

08.02.13

08-Feb-13

508

509

510

511

512

513

514

515

19

3054

Heifer

982123490425848

08.02.13

08-Feb-13

508

509

510

511

512

513

514

515

20

3064

Heifer

982123490417515

11.02.13

11-Feb-13

505

506

507

508

509

510

511

512

21

3061

Heifer

982123490419708

11.02.13

11-Feb-13

505

506

507

508

509

510

511

512

22

3065

Heifer

982123498847685

12.02.13

12-Feb-13

504

505

506

507

508

509

510

511

23

3069

Heifer

982091016308716

14.02.13

14-Feb-13

502

503

504

505

506

507

508

509

24

3071

Heifer

982123490447819

14.02.13

14-Feb-13

502

503

504

505

506

507

508

509

25

3074

Heifer

982123490425039

17.02.13

17-Feb-13

499

500

501

502

503

504

505

506

26

3084

Heifer

982123490226691

26.02.13

26-Feb-13

490

491

492

493

494

495

496

497

27

3088

Heifer

982123490425140

28.02.13

28-Feb-13

488

489

490

491

492

493

494

495

28

3090

Heifer

982123489464042

03.03.13

03-Mar-13

485

486

487

488

489

490

491

492

29

3098

Heifer

982123489459356

11.03.13

11-Mar-13

477

478

479

480

481

482

483

484

30

3104

Heifer

982123489466360

01.04.13

01-Apr-13

456

457

458

459

460

461

462

463

31

3161

Heifer

982123490224041

09.08.13

09-Aug-13

326

327

328

329

330

331

332

333

32

3176

Heifer

982000191763119

15.08.13

15-Aug-13

320

321

322

323

324

325

326

327

33

3179

Heifer

982000191805821

16.08.13

16-Aug-13

319

320

321

322

323

324

325

326

34

3190

Heifer

982000191727512

18.08.13

18-Aug-13

317

318

319

320

321

322

323

324

35

3186

Heifer

982123498851839

18.08.13

18-Aug-13

317

318

319

320

321

322

323

324

36

3191

Heifer

982000191736709

19.08.13

19-Aug-13

316

317

318

319

320

321

322

323

ages-excel-forum

gaz_chops · Jul 10, 2015

Added and uploaded.

Note: There are a lots of blanks in column W.

https://www.dropbox.com/s/1eovcxr419454rr/Stock-Numbers-Gaz-Chops.xlsx?dl=0

dunmore83 · Jul 10, 2015

Thanks.

That works however I have made some more changes to the presentation of the summary sheet. It is complicated because we have 2 different owners for the animals.

Would be great if you could place formulas in the columns shaded blue. I can then copy across to other groups as required as they are all just replicated.

It is basically all the sample principles as the previous file however I need the numbers to reflect the owner which is in column N of the source data sheet which can be cross referenced with the entries in rows 10:13 on the summary sheet.

https://www.dropbox.com/s/vqc8mxl4ydu3uhb/Stock-Numbers-Gaz-Chops-11-Jul-15.xlsx?dl=0

gaz_chops · Jul 14, 2015

Just taking a look, which column do you want to lookup Bull/Heiffer/Births & Still Born from?

dunmore83 · Jul 14, 2015

From column W in the Stock Source Data worksheet then cross referenced with column N in the same worksheet for the owner

The formulas you have in the column CM, CS & CY seem correct. They just need adjusting to reflect the owner - either Sier or Dun

gaz_chops · Jul 15, 2015

This part won't work, column W either says Heiffer, Bull or Death! So if it says Death then it won't say Heiffer or Bull!

Code:

[TABLE="width: 178"]
<colgroup><col><col span="2"><col><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 10"]Death[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Sier[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 4"]Dun[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Heifer[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Bull[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Heifer[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Bull[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

dunmore83 · Jul 15, 2015

Hi

Column W will only have 3 options - Heifer, Bull or Still Born

The deaths will come from the Deaths-2014-15 worksheet. I just realized that this worksheet needs another column which will go in between Ear Tag # and Birth Date which will have either Heifer or Bull

gaz_chops · Jul 15, 2015

Uploaded the updated version, do some testing.

https://www.dropbox.com/s/iwdg3tfwp03u9yo/Stock-Numbers-Gaz-Chops-11-Jul-15-2.xlsx?dl=0

dunmore83 · Jul 15, 2015

Thanks.

I've incorporated the formulas. Seems to be working for the 1st group. However can't work out why it is not working in Y36 to AT47. It is something to do with the last part of the formula... *('Stock-Source-Data'!$W$9:$W$1634=$Y$8) If this is removed the formula returns the number however it is not liking the reference of Heifer, Bull & Still born

https://www.dropbox.com/s/ujzomwcm2g7n4b7/Gaz-15-Jul-15.xlsx?dl=0

gaz_chops · Jul 15, 2015

Looks like you haven't copied the formulas over correctly? P18 should reference "Birth", it doesn't!

dunmore83 · Jul 15, 2015

I made that adjustment intentionally as I want column P & S to show the total number of births regardless of whether they were a heifer, bull or still born

The problem seems to be when I copy down the formulas. For example Y17:Y29 is working correctly. However Y36:Y47 doesn't seem to be picking up the numbers ??

Complex - Multiple Criteria

dunmore83

Well-known Member

gaz_chops

Well-known Member

Excel Facts

dunmore83

Well-known Member

gaz_chops

Well-known Member

dunmore83

Well-known Member

gaz_chops

Well-known Member

dunmore83

Well-known Member

gaz_chops

Well-known Member

dunmore83

Well-known Member

gaz_chops

Well-known Member

dunmore83

Well-known Member

Similar threads

Forum statistics

Share this page

Complex - Multiple Criteria

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock