Hi All
I have started naming my cells which is great and all, however on numerous occasions I do this at the end of my calculations. As an example (see attached) I did my calculation and then named my cells, however, highlighted cell D3 still reflects "$B$3*(1+$B$4)" in the formula box instead of the newly named reference cells i.e "NOI*(1+Esc._Rate)". Does anyone know how to update all the cells in a worksheet to correct for this?
Thanks in advance for any help!
Regards
SilverBullet
You would have to create a table to get it to work the way you are talking about. I have done it in the attached.
SilverBullet17-naming-cells-help-naming-cells-help.xlsx
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
in the Formula menu, you have the Define Name option. If you click on that menu, you'll see that you have the option to APPLY names. You can then select a named range and apply it to your sheet or workbook. You have to do that for every range name you have created.
Next time, it is best to create the range named before doing the formulas.
But at least, it can be done after.
Regards
Pierre Leclerc
_______________________________________________________
If you like the help you got,
Click on the STAR "Add reputation" icon at the bottom.
Thanks this is exactly what I was looking for - could you please explain how you did this? I was able to convert to a table (see attached) but cannot update the cells accordingly to show the new cell names...(I have tried refreshing the table using F9 but to no avail)
Hi Pierre
Thanks for this, I will bear this in mind going forward - unfortunately this is a little inefficient when dealing with a large data set so I am looking for a blanket solution to apply when I have, say 25 named cells and want to update all the cells to become named cells.
On your sheet, highlight the $B$3 in your formula then select the cell B3, then do the same with the $B$4.
I think we are misinterpreting eachother - I am looking for a way to update my worksheet without having to go into my formula sheet as you describe above.
My problem is this - I have another worksheet with 24 different "Assumptions" which link to various other cells in the model and I haven't named any of the cells - I now want to name the cells accordingly but when I name the assumptions, the cells in the formula tab stay as Cell References (such as B3) instead of changing to NOI. If I create the table as you suggest then wouldn't I have to go into each and every cell that the assumptions link to and change them?
Attach a workbook with additional data that you are asking about? Sometimes it's a bit difficult to visualize what is needed without more info.
See attached - basically I want say, I4 to read "=J3/Exit Cap Rate" rather than "=J3/B11" or say, cell I9 to read "=FV(I-Rate,I2,D2,-Loan_Amount,0)-Loan_Amount" instead of the current "=FV(B13,I2,D2,-B7,0)-B7".
Because I have named the cells after doing all my calcs, the cells haven't updated to reflect the new names...
p24leclerc gave a good answer above saying I needed to "apply the names" using a function under the formulas tab, however this can only be done for 1 cell at a time - I was thinking of combining this with some find and replace function perhaps? Thoughts?
Ok, I see what you are wanting to do, but I'm not sure you can do it the way you describe.
No prob, appreciate the input.
No problem. Guess I should have added that there may be a way to do what you are describing, I just don't know how to. Maybe someone else will be able to help out if there is a way to do it.
Thank you for the rep...even though I wasn't able to get you where you wanted to go.
You can change one named range at a time but for the whole sheet. You have to select all the cells (top left end corner) and then open the APPLY NAMES window and select only one named range. Click OK.
Of coarse, you'll have to repeat this operation for all the named ranges you hae but the whole sheet is changed at once.
At least, I was able to do it on your sample workbook.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks