Excel help needed

• Pick6

  

  Do not have my main computer so I am working with 2007.
  
  I am trying to link cells from one sheet to another on the same document keeping the same formula properties.
• FatHobbit

  

  Pick6;1267817 wrote:Do not have my main computer so I am working with 2007.
  
  I am trying to link cells from one sheet to another on the same document keeping the same formula properties.

  I'm not sure what you mean by "keeping the same formula properties" but you can reference another sheet by using something like =sheet1!a1
  
  If you don't want the formula to change when you fill down/copy, you can say =sheet1!$A$1.
  
  But I have a feeling I've completely misunderstood what you are asking.
• jmog

  Elaborate what you mean by the "same formula properties", I am fairly good with excel and know how to use many of its functions and even program using VBA in excel. However, I need more information on what you mean.
• Pick6

  

  FatHobbit;1267821 wrote:I'm not sure what you mean by "keeping the same formula properties" but you can reference another sheet by using something like =sheet1!a1
  
  If you don't want the formula to change when you fill down, you can say =sheet1!$A$1.
  
  But I have a feeling I've completely misunderstood what you are asking.

  Ok. I have the info on one sheet with the formula and am trying to copy it to sheet 2 and then show the formula. For some reason my prof wants a sheet that just shows the formula used to see how we got our answer. I know how to make it show the formula after I have done this. I've tried linking it like you said and when clicking on the cell it shows =sheet1!$A$1. Instead, I need it to show (B2-B3) for example.
• gut

  I'm not aware of a way to do that. The formula on your other sheet is precisely "=sheet1!$A$1". Maybe instead of a simple link you can do something like "=indirect(sheet1!$A$1)". No idea which function you need, probably not indirect and I don't think text will work, but google is your friend.
• gorocks99

  

  Only way I can think to do it is to copy and paste formulas (Alt+E+S+F), then display formulas on the second sheet. You won't get an outcome - because you'd only be copying the one column/row of formulas - but the formulas themselves should be there.
  
  That is, lets say you have "1" in A1, "2" in B1, and "=B1-A1" in C1 on Sheet1. On the sheet with all your data, it will display "1" in cell C1. But you can copy and paste the formula from C1 (Sheet1) into C1 (Sheet2). It will still be =B1-A1, but the outcome will be zero unless you put anything in B1 or A1 on Sheet2.
• gut

  why not just make a copy of the sheet with the work, and on that second sheet show the formula instead of the result?
• Pick6

  

  gut;1267848 wrote:why not just make a copy of the sheet with the work, and on that second sheet show the formula instead of the result?

  that was the first thing I tried. However, the cells that have values in them just come up "#REF!"
• FatHobbit

  

  I'm having no luck finding a way to do that.
• jmog

  At the bottom where the sheets are listed, right click on the sheet and copy it (make sure to click the box that says create a copy).
  
  Once you copy it, click on the copied sheet and then go to the "Formulas' tab in the ribbon. On that one click on the "Show Formulas".
  
  Once you do this the cells that were formulas will show the formula text, the cells that were numbers will show the number.
• gut

  Pick6;1267853 wrote:that was the first thing I tried. However, the cells that have values in them just come up "#REF!"

  I'm not sure what your prof is trying to accomplish. All he has to do is click on the "show formulas" in the auditing toolbar.
  
  The whole point of a formula is to calculate a value to pass to another cell, and I'm not aware of a function that looks below that formula result.
• Pick6

  

  jmog;1267859 wrote:At the bottom where the sheets are listed, right click on the sheet and copy it (make sure to click the box that says create a copy).
  
  Once you copy it, click on the copied sheet and then go to the "Formulas' tab in the ribbon. On that one click on the "Show Formulas".
  
  Once you do this the cells that were formulas will show the formula text, the cells that were numbers will show the number.

  Thanks, I definitely over complicated it I think.

  gut;1267862 wrote:I'm not sure what your prof is trying to accomplish. All he has to do is click on the "show formulas" in the auditing toolbar.
  
  The whole point of a formula is to calculate a value to pass to another cell, and I'm not aware of a function that looks below that formula result.

  Yea I dont understand it either. All you have to do is click on the cell and it shows the formula up top anyways.
  
  Thanks for the help everyone.
• jmog

  No problem...are you handing in an electronic file or printed file? Back when I was in college (1997-2003) transferring files and email/etc wasn't as easy (we thought Zip drives were the best things since sliced bread because they could hold 100 MB!). So, we'd typically have to do something like this and hand in a printed sheet with the numbers and a printed sheet with the formulas.
• Pick6

  

  jmog;1267877 wrote:No problem...are you handing in an electronic file or printed file? Back when I was in college (1997-2003) transferring files and email/etc wasn't as easy (we thought Zip drives were the best things since sliced bread because they could hold 100 MB!). So, we'd typically have to do something like this and hand in a printed sheet with the numbers and a printed sheet with the formulas.

  electronically. thankfully that is what about 95% of my professors require.
• jmog

  Pick6;1267932 wrote:electronically. thankfully that is what about 95% of my professors require.

  Then he's just lazy since he can take 2 seconds to click the "Show Formulas" button after looking over the numbers.