Referencing part of a formula

[xcellnoob]

This should be simple for someone I think, but not me....yet.
Let's say that cell A1's formula is
=Inputs!A1
This is copied down, but sometimes the order isn't sequential. So, you could have =Inputs!A15 and the the next cell down is =Inputs!A23. What I want to do without having to go through and manually change everything is to concatenate two cells based on what the first one is already. So, formula in A1 is
=Inputs!A1&" "&Inputs!B1
the second half of that formula, I want something that will look at Inputs!A1 and say...hmmmm...A1; The next part should be B1. Make sense? I was originallly thinking LEN, but I have no idea now.
Also, on an aside, is there a way to bold half of the text?
Thanks for any and all help on this.

 

[BruceyBonu$]

Morefunc , a great download available at
http://xcell05.free.fr/forums/viewforum.php?id=4

once installed, there is a function called XLM.GET.CELL that I believe would be usefull here... as an example, the following , also the SETV & GETV functions

=SETV(inputa!a1)&" "&INDIRECT("Inputs!b"&XLM.GET.CELL(2,GETV()))

 

[BruceyBonu$]

Morefunc , a great download available at
http://xcell05.free.fr/forums/viewforum.php?id=4

once installed, there is a function called XLM.GET.CELL that I believe would be usefull here... as an example, the following , also the SETV & GETV functions

=SETV(inputs!a1)&" "&INDIRECT("Inputs!b"&XLM.GET.CELL(2,GETV()))

 

[xcellnoob]

Thanks for that. I already have the addin, but many others will be using this sheet and I don't want to have them download and install an addin. Is there any other way within excel formulas/functions without going into VBA either?
Thanks.

 

[BruceyBonu$]

dont forget you can embed the addin in the spreadheet so it can be used by others...tools>>morefunc>>embed...

 

[xcellnoob]

Ahh. Thanks for that as well. One more thing: Why doesn't the addin show up? I have it checked in the addins dialog box, but it doesn't show in the tools. ?????

 

[BruceyBonu$]

You need to reinstall it...there is an option flag on the install that says something like 'add a shortcut in tool menu' type of thing.. then it will allow you to embed.

 

[BruceyBonu$]

Hi again, just in case you were still playing with this... I thought about your prob some more, and I dont think the original explanation will sort it. I know you dont want to get into VBA but there is a very small bit of code that would fix if you are interested

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

Then you can enter then formula as follows to solve your problem...
=A1&" "&INDIRECT("Sheet2!b"&XLM.GET.CELL(2,INDIRECT(MID(getformula(A1),2,999))))

 

[xcellnoob]

Thanks for your continued help. It took me about 5 times of uninstalling/reinstalling to get the Morefunc submenu to show up. That was a pain and I don't know why it was like that, but whatever. That's neither here nor there. As for the VBA example, that looks pretty cool, but I will probably go with the morefunc addin solution. I think it's pretty slick.