Hello, I am trying to create a template for my staff however I am running into an issue and I don't know if I can accomplish what I am hoping for. I currently have empty cells in b2:b16 where my staff can enter medical codes. In cells c2:c16 I have a lookup formula that pulls the definition of the code from a seperate work book. What I am trying to accomplish is in cell a95 toward the bottom of the work book i want to combine all of the descriptions from cells c2:c16 seperated by a ;. The issue I'm running into is that unless all of these fields are filled out I get an error. When they are completely filled out it does exactly what I want. Unfortunatly I cannot predict whether one request will come in with 1 code or 15 codes so it varies. Is there a way to combine cells as needed? Example shown below:
This code works when I have all fields completed:
=A2&""&C2&"; "&C3&"; "&C4&"; "&C5&"; "&C6&"; "&C7&"; "&C8&"; "&C9&"; "&C10&"; "&C11&"; "&C12&"; "&C13&"; "&C14&"; "&C15&"; "&C16
A2 is the word "Request" btw
This is what it returns:
Request: 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL
Request: But if I am missing ,for example, 14 of these fields it returns this:
#N/A
And I want it to return this:
Request: 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL
Any help would be greatly appreciated, I have been playing with this for a while.
Bookmarks