Substituting for sequential parameters

[akaushal]

First time posting so I hope I'm getting it right. I have a complicated spreadsheet for which I'm going to describe a simplified version here, in order to illustrate a challenge I'm having.

Imagine we have rows 1 to 5 and columns A to F

Column D is always some ratio of columns A, B, and C in the same row.

So D1 can be A1/B1 or A1/C1 or B1/A1 or B1/C1 or......etc.

Likewise D2 can be A2/B2 or A2/C2 or.....etc.

Column E displays the formula from column D. I used "FORMULATEXT" or alternatively a VBA I found on this forum called ShowF
<code class="vb keyword">Function</code> <code class="vb plain">ShowF(Rng </code><code class="vb keyword">As</code> <code class="vb plain">Range)</code>
<code class="vb plain">ShowF = Rng.Formula</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>


So the cells in column E are text values like A3/B3 or B15/C15

I'd like to create column F so that I am only getting the ratio without the row numbers included. So I'd like F1 to display A/B or B/C or B/A, etc....

The problem with SUBSTITUTE or REPLACE is that the parameter, like "A3", is in quotes, so it doesn't become "A4" when I drag it down to the next row. It remains "A3" when I try to drag it down to the next row.

Is there some kind of nested function or a VBA script that would be able to achieve this?

Thanks in advance!

 

[akaushal]

This forum is awesome.

I am deeply grateful for all the members who jumped in to help solve this issue. Your generosity speaks volumes for the membership of the forum. Let's roll the credits:

Many thanks to Eric for being the first, and last, to respond. Double dipping is a real sign of commitment.
Props to Tetra201 for the simplest working solution. Quick & dirty can be so lovely.
Hats off to Gary's Student and Rick Rothstein for brilliant, complex solutions and for displaying deep knowledge of VBA - something about which I know very little. Digging deep for fancy solutions - showing off for newbies!

All jokes aside, I sincerely thank you all again for your time and kind consideration. I hope to be able to pay it forward someday. Until then I'm grateful for your help as I continue learning.

Enjoy the rest of the week!

Cheers,

AK

 

[Tetra201]

You are most welcome.

 

[Rick Rothstein]

Rick, did you mean

=NoNumRefs(D1)

not E1?

No, I do not think so (not that it matters as it does not look like the OP will use it). In Message #2, you posted this...

	A	B	C	D	E	F
1	1	1	-2	1	=A1/B1	=A/B
2	2	3	-1	1.5	=B2/A2	=B/A
3	3	5			=C3/B3	=C/B
4	4	7	1	1.5	=B2/A2	=B/A
5	5	9	2	2.5	=A5/C5	=A/C

<tbody>
</tbody>

I assumed what I highlighted in red as the Row Numbers provided by Excel just as the letters in the shaded area are the Column Letters provided by Excel. You show the formula the OP wants to parse as being in Column E and (although you show an equal sign in front which I do not think the OP wanted) Column F is displaying the formula from Column E without the row numbers.

 

[akaushal]

Rick,

I did try your script and it does work. I didn't mean to say I couldn't use it, only that it not something I could have come up with in a million years. I could probably pull together Tetra201's solution after a couple hours of research and self-education online, so that saved my afternoon. Your solution requires a much deeper understanding of Excel programming, which I'd probably need a semester or two to understand.

Both solutions work. I just would not have been able to come up with yours......

......and now I know whom I might PM if I'm in a pinch and need some scripting guidance

Thanks again!!

AK