MichaelFerguson
New Member
- Joined
- Jul 30, 2019
- Messages
- 3
Hello,
I have a spreadsheet that is concatenating multiple ranges of data to formula a note that can be copied and pasted elsewhere. Unfortunately I can't figure out how to exclude blank cells and instead I'm getting 0's in my final note, like this:
<tbody>
</tbody>
All of the segments with blank account numbers and $0.00 amounts are being pulled in from blank cells (sometimes these cells will contain data that I want included). How can I adjust my formula to make this work?
Here is my formula:
=IF(COUNTA(E2)=1,CONCATENATE(Z6,TEXT(A2,"#,##0.00"),AA6,B2,AB6,D2," ",AC6," ",AD6 & TEXT(E2,"#,##0.00"),AE6," ",AF6," ",D3," ",AC6," ",AD6 & TEXT(E3,"#,##0.00"),"",AE6,AF6," ",D4," ",AC6," ",AD6 & TEXT(E4,"#,##0.00"),AE6,AF6," ",D5," ",AC6," ",AD6 & TEXT(E5,"#,##0.00"),AE6," ", AF6," ",D6," ", AC6," ",AD6 & TEXT(E6,"#,##0.00"),AE6," ",AF6," ",D7," ", AC6," ",AD6 & TEXT(E7,"#,##0.00"),AE6," ",AF6," ",D8," ", AC6," ",AD6 & TEXT(E8,"#,##0.00"),AE6," ", AF6," ",D9," ", AC6," ",AD6 & TEXT(E9,"#,##0.00"),AE6," ", AF6," ",D10," ", AC6," ",AD6 & TEXT(E10,"#,##0.00"),AE6," ", AF6," ",D11," ", AC6," ",AD6 & TEXT(E11,"#,##0.00"),AE6," ", AF6," ",D12," ", AC6," ",AD6 & TEXT(E12,"#,##0.00"),AE6," ", AF6," ",D13," ", AC6," ",AD6 & TEXT(E13,"#,##0.00"),AE6," ", AF6," ",D14," ", AC6," ",AD6 & TEXT(E14,"#,##0.00"),AE6," ",AF6," ",D15," ", AC6," ",AD6 & TEXT(E15,"#,##0.00"),AE6," ",AF6," ",D16," ", AC6," ",AD6 & TEXT(E16,"#,##0.00"),AE6," ",AF6," ",D17," ", AC6," ",AD6 & TEXT(E17,"#,##0.00"),AE6," ",AF6," ",D18," ", AC6," ",AD6 & TEXT(E18,"#,##0.00"),AE6," ",AF6," ",D19," ", AC6," ",AD6 & TEXT(E19,"#,##0.00"),AE6," ",AF6," ",D20," ", AC6," ",AD6 & TEXT(E20,"#,##0.00"),AE6," ",AF6," ",D21," ", AC6," ",AD6 & TEXT(E21,"#,##0.00")," "))
Account numbers and amount are entered in D2:21 and E2:21, respectively. Other data is pulled from elsewhere to build the final note, but I have that part figured out. Would appreciate any help. I'm a big novice.
<tbody>
</tbody>
I have a spreadsheet that is concatenating multiple ranges of data to formula a note that can be copied and pasted elsewhere. Unfortunately I can't figure out how to exclude blank cells and instead I'm getting 0's in my final note, like this:
<tbody> </tbody> |
<tbody>
</tbody>
All of the segments with blank account numbers and $0.00 amounts are being pulled in from blank cells (sometimes these cells will contain data that I want included). How can I adjust my formula to make this work?
Here is my formula:
=IF(COUNTA(E2)=1,CONCATENATE(Z6,TEXT(A2,"#,##0.00"),AA6,B2,AB6,D2," ",AC6," ",AD6 & TEXT(E2,"#,##0.00"),AE6," ",AF6," ",D3," ",AC6," ",AD6 & TEXT(E3,"#,##0.00"),"",AE6,AF6," ",D4," ",AC6," ",AD6 & TEXT(E4,"#,##0.00"),AE6,AF6," ",D5," ",AC6," ",AD6 & TEXT(E5,"#,##0.00"),AE6," ", AF6," ",D6," ", AC6," ",AD6 & TEXT(E6,"#,##0.00"),AE6," ",AF6," ",D7," ", AC6," ",AD6 & TEXT(E7,"#,##0.00"),AE6," ",AF6," ",D8," ", AC6," ",AD6 & TEXT(E8,"#,##0.00"),AE6," ", AF6," ",D9," ", AC6," ",AD6 & TEXT(E9,"#,##0.00"),AE6," ", AF6," ",D10," ", AC6," ",AD6 & TEXT(E10,"#,##0.00"),AE6," ", AF6," ",D11," ", AC6," ",AD6 & TEXT(E11,"#,##0.00"),AE6," ", AF6," ",D12," ", AC6," ",AD6 & TEXT(E12,"#,##0.00"),AE6," ", AF6," ",D13," ", AC6," ",AD6 & TEXT(E13,"#,##0.00"),AE6," ", AF6," ",D14," ", AC6," ",AD6 & TEXT(E14,"#,##0.00"),AE6," ",AF6," ",D15," ", AC6," ",AD6 & TEXT(E15,"#,##0.00"),AE6," ",AF6," ",D16," ", AC6," ",AD6 & TEXT(E16,"#,##0.00"),AE6," ",AF6," ",D17," ", AC6," ",AD6 & TEXT(E17,"#,##0.00"),AE6," ",AF6," ",D18," ", AC6," ",AD6 & TEXT(E18,"#,##0.00"),AE6," ",AF6," ",D19," ", AC6," ",AD6 & TEXT(E19,"#,##0.00"),AE6," ",AF6," ",D20," ", AC6," ",AD6 & TEXT(E20,"#,##0.00"),AE6," ",AF6," ",D21," ", AC6," ",AD6 & TEXT(E21,"#,##0.00")," "))
Account numbers and amount are entered in D2:21 and E2:21, respectively. Other data is pulled from elsewhere to build the final note, but I have that part figured out. Would appreciate any help. I'm a big novice.
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Account#[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Amount[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]256.00[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]190.00[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]351.00[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]249.52[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.20[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]118.32[/COLOR] |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Total[/COLOR] | [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00[/COLOR] |
<tbody>
</tbody>