Ohio Chatter
Ohio Chatter

Forums

All Threads Serious Business Sports Politics Archive Pick 'ems
Log In Create Account

Excel help needed

Home ▸ Archive ▸ Serious Business ▸ Excel help needed
Pick6's avatar

Pick6

A USA American

14,946 posts
Sep 11, 2012 1:38 PM
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.
Sep 11, 2012 1:38pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Sep 11, 2012 1:42 PM
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.
Sep 11, 2012 1:42pm
J

jmog

Senior Member

6,567 posts
Sep 11, 2012 1:45 PM
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.
Sep 11, 2012 1:45pm
Pick6's avatar

Pick6

A USA American

14,946 posts
Sep 11, 2012 1:47 PM
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.
Sep 11, 2012 1:47pm
G

gut

Senior Member

15,058 posts
Sep 11, 2012 1:55 PM
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.
Sep 11, 2012 1:55pm
gorocks99's avatar

gorocks99

Senior Member

10,760 posts
Sep 11, 2012 1:58 PM
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.
Sep 11, 2012 1:58pm
G

gut

Senior Member

15,058 posts
Sep 11, 2012 2:02 PM
why not just make a copy of the sheet with the work, and on that second sheet show the formula instead of the result?
Sep 11, 2012 2:02pm
Pick6's avatar

Pick6

A USA American

14,946 posts
Sep 11, 2012 2:04 PM
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!"
Sep 11, 2012 2:04pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Sep 11, 2012 2:06 PM
I'm having no luck finding a way to do that.
Sep 11, 2012 2:06pm
J

jmog

Senior Member

6,567 posts
Sep 11, 2012 2:09 PM
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.
Sep 11, 2012 2:09pm
G

gut

Senior Member

15,058 posts
Sep 11, 2012 2:11 PM
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.
Sep 11, 2012 2:11pm
Pick6's avatar

Pick6

A USA American

14,946 posts
Sep 11, 2012 2:14 PM
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.
Sep 11, 2012 2:14pm
J

jmog

Senior Member

6,567 posts
Sep 11, 2012 2:29 PM
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.
Sep 11, 2012 2:29pm
Pick6's avatar

Pick6

A USA American

14,946 posts
Sep 11, 2012 3:01 PM
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.
Sep 11, 2012 3:01pm
J

jmog

Senior Member

6,567 posts
Sep 11, 2012 3:13 PM
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.
Sep 11, 2012 3:13pm