• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert reference cells to actual values

A

anoopbal

New Member
Hello,


I have a lot of tables that I have copied to excel. I have used another formuale to reference these values in the tables. Does anyone know how to have the actual values show up in my new formulae than the cell references (like B6 or C8)?


I just want to save time by not retyping each value and also not make mistakes when I type it in. Also don't want to clutter my page with the reference tables which don't have to be seen.


Thanks!
 
Hi anoop,


Place your cell references in INDIRECT() function like INDIRECT(B6). It will give u ur required result.


Regards,

Faseeh
 
Hey Faseeh,


Thanks! But I want to delete the reference table after I insert them into the formuale. But if I use Indirect and then delete, the values get deleted too.


Basically, I am asking if I can pull the actual values from the reference cells and then I can delete the reference cells. Hope it makes some sense.
 
Hi Anoop,


As you have already pulled out you data.. Now you can..

* Press Ctrl + A. (may be 2 3 4 times..)

* Press Ctrl + C.

* Press Alt + E + S + V ... Enter..


Now you can delete the Reference Table..


PS: Welcome back.. Its almost 1 year.. , Please come frequently.. :)


Regards,

Deb
 
I want to keep the forumlae too. For example, I want to calculate the Z score which is =(Raw Score-C1)/D2. I want the actual values from the reference cells to replace C1 & D2. So when they enter the raw score, they get the answer. These values are from different tables that I can easily paste it into the excel sheet. By using a reference, I can easily get these values int the formuale. But don't want the tables stay hanging in there. I think it will be cleaner if I could delete the tables.


Hope it makes sense.or Does it?


Thank you Debraj!
 
Hi anoopbal,


If you want to keep formulas then you can create a sought of look-up table somewhat else and hide the sheet. You can "Paste Values Only" and keep the formulas at the same time. I think i have understood your query completely, am I??


Faseeh
 
You must log in or register to reply here.
Back
Top