From workbook 1 I link data into workbook 2. I need to manipulate the data in workbook 2 from workbook 1. I have no control over workbook 1 this is why I link only the data I need to workbook 2. I have three questions when I link the data from workbook one into workbook 2.

1. How to merge four cells into one cell linking from worksheet one into workbook 2?
=CONCATENATE(L7," ",M7,L8," ",M8)
I tried this, having trouble with spaces and not sure how to add the link from workbook 1.

2. How to split one cell from workbook one into workbook 2 by 2 cells?
=LEFT(B9,FIND(" ",B9))
=RIGHT(B9,FIND(" ",B9))
This is OK but the RIGHT code display as a number format. I need both to show as a number format. Not sure how to add the link from workbook 1.

3. How to show “0” in workbook two where the cell is “NO” in workbook one?
=IF(NO(VLOOKUP(B9)),0,VLOOKUP(B9))
Not even close, need help with this one from scratch. No sure how to add the link from workbook 1.

Hi comf36,

For your first one, take a look at the sample workbook I made for you. I think it does what you were asking, and used the Cells that you had just to make it easy. More to come.......... possibly.

have a nice one,
BUD

Hello again,

Here you go with you last two. Hope these help you out as just one possible way of achieving what you are after. Not a pro but use it enough to get what I need done.

have a nice one,
BUD

Great!
From your examples it worked like a charm. But I still have another problem, when I split the cells and or change the value from "NO" to "0", the formats will not change to numbers from the link table in Access. I get the famous #Num! value in Access from the excel link table.

From the split cells the right code shows #NUM!, the left code the format is number. Both cell should be a number value from the link excel worksheet.

Hi again comf36,
The format doesn't need to change. In Workbook 1 you would have a cell to hold NO and possibly YES??? You don't place NO in the Cell on Workbook 1, you enter Yes or NO and Workbook 2 displays the result either 0 or "whatever you wish". You were wanting the cells in Workbook 2 to change to "0" when the said cell in Workbook 1 was "NO". That it does, and when you change it to "YES" it can either show "whatever text you wish" or "nothing at all" in Workbook 2. I had no idea from your post there was anything to do with Access. I just changed the Cell Values in Workbook 2 to "Number" and it still worked just fine with no error. So tell me, are you trying to import all this into Access? Tell me exactly what your entire process is so that I can evaluate it and duplicate it to possibly get at a definitive answer for you.

BUD

The excel examples does exactly what I needed, thanks. The Access problem was solve with the transferspreadsheet code;

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:=strTable, _
FileName:=strWorkbook, _
hasfieldnames:=True, _
Range:="tbltype"

Thanks,
Comf36

Just glad to be able to assist you. Hope you have a nice one,
BUD

Bud;
Ran into a bump!
I need to link from a totally different excel workbook and concantanate into a new workbook cell.

='C:\AVG\Products Management\Sales\[CYCLES 2005.xls] MARKETS 2005'!D41&" "&D42&" "&D43&" "&D44&""

This will equal b10, but it's blank; help.

thanks comf36