Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Solved

Posted on 2012-03-29

Hello,

When building formulas in Excel, is there a shortcut or automatic way to substitute formulas for their respective cell references? That is a step that seems to be repeated frequently — at least in the method I use to keep things straight. An example is described below the following note:

*Note: In the screenshots shown below:*

•Column B contains the names

•Column C contains the formulas

•Column D contains an Excel 4 function* which shows the formulas in Column C. (Not wanting anyone to waste their time due to misinterpreting this — as has happened before — please see the asterick (*) footnote at the bottom of this question.)

For the example, suppose you want to capture the first name from a list of full names, some of which have middle initials and some of which do not. Fig. 1 shows the steps I used to obtain the desired information. To be of much value, the final formula must obviously reference only cell B2. However, in progressing towards the solution, I get confused in a hurry if I try to use only the cell B2 reference right off the bat in every step. Instead, I build each step using references to previous steps and then substitute to remove the non-B2 references.

*(Click to enlarge image.)*For example, to determine the number of spaces (cells C7 & C8) in the name (cell B2), I have to calculate the difference in the number of characters with the spaces present (cell C3) and the number of characters without the spaces (cell C6). Therefore, I first enter the formula, "=C3-C6" in cell C7 and then in cell C8, substitute for C3 and C6 to obtain a formula for that step which references only cell B2. A similar substitution takes place in cells C6, C8, C10, C12, C14, and C15.

Thus, my question again is to find out if there is a quicker/easier/automated way to make these substitutions. I find that currently, most of my time in creating formulas, involves copying formulas from previous cells, selecting the interim cell reference, and replacing it with the contents just placed on my clipboard. I would much prefer to spend all of that time:

•figuring out how to use functions I know to accomplish my objectives

•and learning new functions.

To be complete, Fig. 2 shows the final formula (C15) inserted into its appropriate location (C2):And Fig.3 shows it copied down to additional rows:Thanks

*Following is a brief explanation of the Excel 4 function mentioned above:

In Fig. A:

•"John Z Doe" is a manual entry in B2,

•C3 contains the formula =LEN(B2) and thus displays the value 10,

•and cell D3 displays the formula in C3 (i.e. there is no formula in D3).Fig. B maybe shows this more clearly. Since cell C3 is selected, its formula is shown in the Formula Bar and can be seen to be identical to that shown in cell D2.Finally, Fig. C shows the Formula Bar with cell D3 selected.As can be seen, it contains a reference to "Formula" which is defined in the Name Manager as:

=GET.CELL(6,C3)

When building formulas in Excel, is there a shortcut or automatic way to substitute formulas for their respective cell references? That is a step that seems to be repeated frequently — at least in the method I use to keep things straight. An example is described below the following note:

•Column B contains the names

•Column C contains the formulas

•Column D contains an Excel 4 function* which shows the formulas in Column C. (Not wanting anyone to waste their time due to misinterpreting this — as has happened before — please see the asterick (*) footnote at the bottom of this question.)

For the example, suppose you want to capture the first name from a list of full names, some of which have middle initials and some of which do not. Fig. 1 shows the steps I used to obtain the desired information. To be of much value, the final formula must obviously reference only cell B2. However, in progressing towards the solution, I get confused in a hurry if I try to use only the cell B2 reference right off the bat in every step. Instead, I build each step using references to previous steps and then substitute to remove the non-B2 references.

Thus, my question again is to find out if there is a quicker/easier/automated way to make these substitutions. I find that currently, most of my time in creating formulas, involves copying formulas from previous cells, selecting the interim cell reference, and replacing it with the contents just placed on my clipboard. I would much prefer to spend all of that time:

•figuring out how to use functions I know to accomplish my objectives

•and learning new functions.

To be complete, Fig. 2 shows the final formula (C15) inserted into its appropriate location (C2):And Fig.3 shows it copied down to additional rows:Thanks

*Following is a brief explanation of the Excel 4 function mentioned above:

In Fig. A:

•"John Z Doe" is a manual entry in B2,

•C3 contains the formula =LEN(B2) and thus displays the value 10,

•and cell D3 displays the formula in C3 (i.e. there is no formula in D3).Fig. B maybe shows this more clearly. Since cell C3 is selected, its formula is shown in the Formula Bar and can be seen to be identical to that shown in cell D2.Finally, Fig. C shows the Formula Bar with cell D3 selected.As can be seen, it contains a reference to "Formula" which is defined in the Name Manager as:

=GET.CELL(6,C3)

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

1 Comment

However, I could see VBA being used to edit a formula in a closed cell and replacing all upstream references with the upstream formulas.

Kevin

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Course of the Month9 days, 15 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.