Hi All,
I have a VLOOKUP that looks like the following in Cells B2:B51 and C2:C51 in a sheet called Paid DD and the cells are formatted as General and Date:-
DD Members is another sheet in the same workbook and the cells are formatted as General and Date in the sheet.
When a match is found, it bring the cell value as expected. However if the cell is empty in sheet DD Members, the result of the first VLOOKUP is 0 (in column B) and 00/01/00 in Column C.
Is it possible if the value to be returned by the VLOOKUP is empty, in Column B (sheet DD Members), then the VLOOKUP puts a blank in the Column B for the first VLOOKUP and the same for Column C.
The reason for the above is I have a COUNTA formula in sheet Paid DD, cell B52 (=COUNTA(B2:B51)) and C52 (=COUNTA(C2:C51)) which is returning the wrong count as the cells are not blank as they have a formula in them.
I have search the forum and even tried to
formula as suggested by Fluff in another post and that doesn’t work.
I have had to delete data in sheet DD Members from Columns F onwards as it is P+C.
Any assistance offered will be appreciated.
I have a VLOOKUP that looks like the following in Cells B2:B51 and C2:C51 in a sheet called Paid DD and the cells are formatted as General and Date:-
VBA Code:
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)DD Members is another sheet in the same workbook and the cells are formatted as General and Date in the sheet.
When a match is found, it bring the cell value as expected. However if the cell is empty in sheet DD Members, the result of the first VLOOKUP is 0 (in column B) and 00/01/00 in Column C.
Is it possible if the value to be returned by the VLOOKUP is empty, in Column B (sheet DD Members), then the VLOOKUP puts a blank in the Column B for the first VLOOKUP and the same for Column C.
The reason for the above is I have a COUNTA formula in sheet Paid DD, cell B52 (=COUNTA(B2:B51)) and C52 (=COUNTA(C2:C51)) which is returning the wrong count as the cells are not blank as they have a formula in them.
I have search the forum and even tried to
VBA Code:
=T(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))| 2020 Edited Membership.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Pay Type | Membership Number | DO NOT DELETE, REQUIRED in SHEET PAID DD | Payment Statement number | Payment Received Date | Surname | First Name | Address | Town | Post Code | Member Type | Junior Parent | ||
| 2 | 99 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||
| 3 | SM | 00001 | SM00001 | 305 | 01/04/20 | |||||||||
| 4 | SM | 00002 | SM00002 | 305 | 01/04/20 | |||||||||
| 5 | SM | 00003 | SM00003 | 249 | 01/04/20 | |||||||||
| 6 | SM | 00004 | SM00004 | 303 | 01/04/20 | |||||||||
| 7 | SM | 00005 | SM00005 | 304 | 01/04/20 | |||||||||
| 8 | SM | 00006 | SM00006 | 303 | 01/04/20 | |||||||||
| 9 | SM | 00007 | SM00007 | 246 | 01/04/20 | |||||||||
| 10 | SM | 00008 | SM00008 | 303 | 01/04/20 | |||||||||
| 11 | SM | 00009 | SM00009 | 307 | 01/04/20 | |||||||||
| 12 | SM | 00010 | SM00010 | 246 | 01/04/20 | |||||||||
| 13 | SM | 00011 | SM00011 | 303 | 01/04/20 | |||||||||
| 14 | SM | 00012 | SM00012 | 307 | 01/04/20 | |||||||||
| 15 | SM | 00013 | SM00013 | 307 | 01/04/20 | |||||||||
| 16 | SM | 00014 | SM00014 | 306 | 01/04/20 | |||||||||
| 17 | SM | 00015 | SM00015 | 304 | 01/04/20 | |||||||||
| 18 | SM | 00016 | SM00016 | 307 | 01/04/20 | |||||||||
| 19 | SM | 00017 | SM00017 | |||||||||||
| 20 | SM | 00018 | SM00018 | |||||||||||
| 21 | SM | 00019 | SM00019 | 323 | 01/04/20 | |||||||||
| 22 | SM | 00020 | SM00020 | 304 | 01/04/20 | |||||||||
| 23 | SM | 00021 | SM00021 | 304 | 01/04/20 | |||||||||
| 24 | SM | 00022 | SM00022 | 306 | 01/04/20 | |||||||||
| 25 | SM | 00023 | SM00023 | 305 | 01/04/20 | |||||||||
| 26 | SM | 00024 | SM00024 | |||||||||||
| 27 | SM | 00025 | SM00025 | 250 | 01/04/20 | |||||||||
| 28 | SM | 00026 | SM00026 | 250 | 01/04/20 | |||||||||
| 29 | SM | 00027 | SM00027 | 305 | 01/04/20 | |||||||||
| 30 | SM | 00028 | SM00028 | 248 | 01/04/20 | |||||||||
| 31 | SM | 00029 | SM00029 | 306 | 01/04/20 | |||||||||
| 32 | SM | 00030 | SM00030 | 306 | 01/04/20 | |||||||||
| 33 | SM | 00031 | SM00031 | 304 | 01/04/20 | |||||||||
| 34 | SM | 00032 | SM00032 | 304 | 01/04/20 | |||||||||
| 35 | SM | 00033 | SM00033 | 250 | 01/04/20 | |||||||||
| 36 | SM | 00034 | SM00034 | 303 | 01/04/20 | |||||||||
| 37 | SM | 00035 | SM00035 | 304 | 01/04/20 | |||||||||
| 38 | SM | 00036 | SM00036 | 306 | 01/04/20 | |||||||||
| 39 | SM | 00037 | SM00037 | 306 | 01/04/20 | |||||||||
| 40 | SM | 00038 | SM00038 | 304 | 01/04/20 | |||||||||
| 41 | SM | 00039 | SM00039 | 304 | 01/04/20 | |||||||||
| 42 | SM | 00040 | SM00040 | 304 | 01/04/20 | |||||||||
DD Members | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2,F2:L2 | D2 | =COUNTA(D3:D152) |
| C3:C42 | C3 | =CONCATENATE(A3,B3) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A:A | Cell Value | ="RM" | text | YES |
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K2:K51,H2:H51,E2:E51,B2:B51 | B2 | =VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE) |
| L2:L51,I2:I51,F2:F51,C2:C51 | C2 | =VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE) |
| J53,H53,E53,B53:C53 | B53 | =COUNTA(B2:B51) |
| B55 | B55 | =B53+E53+H53 |
I have had to delete data in sheet DD Members from Columns F onwards as it is P+C.
Any assistance offered will be appreciated.
