# Referencing part of a formula

#### xcellnoob

##### Board Regular
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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()))

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()))

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.

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

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. ?????

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.

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))))

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.

Replies
2
Views
132
Replies
5
Views
160
Replies
4
Views
142
Replies
8
Views
175
Replies
3
Views
336

1,219,808
Messages
6,150,352
Members
450,953
Latest member
Soleil2438

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back