Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CONCATENATE
Hello All,
I have wrestled with this formula for weeks but I cannot seem to get it to work: =IF(Totals!B40,CONCATENATE(Totals!B4,"-",Totals!B3),"") The formula above works, however I would like to have this formula work accross columns. This sheet is a summary order sheet, which references another sheet. On the sheet it is referencing along the top I have items, down the side there will be customers. What should happen is if the referenced sheet field is blank nothing is ordered or showed on the summary sheet; if the number is 1 or, the total quantity and the item will appear e.g. 5 - peaches, in Mr. Smith's row. The above formula works for just one column, I would like it to show all orders, e.g. in Mr. Smith's row 5-peaches, 3-apples, 2-pears, etc.. There are over 100 items so I cannot repeat the formula above for all columns. Any help would be appreciated. Sincerely, Dave |
#2
|
|||
|
|||
change your formula to =IF(Totals!B40,Concatenate(Totals1B4,"-",Totals!B$3),"") -- PF Wannabe ------------------------------------------------------------------------ PF Wannabe's Profile: http://www.excelforum.com/member.php...o&userid=25798 View this thread: http://www.excelforum.com/showthread...hreadid=391989 |
#3
|
|||
|
|||
typo in previous reply =IF(Totals!B40,Concatenate(Totals!B4,"-",Totals!B$3),"") -- PF Wannabe ------------------------------------------------------------------------ PF Wannabe's Profile: http://www.excelforum.com/member.php...o&userid=25798 View this thread: http://www.excelforum.com/showthread...hreadid=391989 |
#4
|
|||
|
|||
Thanks PF Wannabe,
However, this formula does the same thing as the original one I posted, ideally I wish to use this formula accross cells so that a summary of multiple items would appear. Any field with no quantity would not show up, however, fields with a number greater than 0 will show with the appropriate item attached: eg. 6-apples, 5-oranges, 1-banana, etc.. The summary field would be composed of list of concaatenated items separated by commas, back slashes, etc. what ever would work. I appreciate your help, Dave "PF Wannabe" wrote: typo in previous reply =IF(Totals!B40,Concatenate(Totals!B4,"-",Totals!B$3),"") -- PF Wannabe ------------------------------------------------------------------------ PF Wannabe's Profile: http://www.excelforum.com/member.php...o&userid=25798 View this thread: http://www.excelforum.com/showthread...hreadid=391989 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
Match and Concatenate ?? | Excel Worksheet Functions | |||
Concatenate cells in Pocket Excel | Excel Discussion (Misc queries) | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Concatenate two halves of a phone number | Excel Worksheet Functions |