Microsoft Excel question - PeachParts Mercedes-Benz Forum

TheDon · #1 01-31-2012, 02:20 PM

Quote:

Originally Posted by Yak

Does "page" substitute for "sheet"?

Hypothetically, say you've got your variables in sheet 1. cost/kwh is sheet 1 cell A4. Sheet 2 is server X, sheet 3 server Y, etc. Say you've got forecast kW in B4 on sheet 2 and you need a dollar value in cell C4. Is that sort of how you're setting it up?

I think tbo's comments are basically correct and to calculate the rate*demand you just need

for sheet 2 cell c4 it would be =sheet1!A4*B4

Copying and pasting SHOULD do this for you. If the sheets have names like "variables, server X, server Y" then you may have to replace the sheet number with the name. Don't underestimate the power of "paste special" and then pick your options, like "paste values". That simply pastes the numeric value of the cell and reduces broken references. But that's a one shot deal. If you change the original cell later on, it won't propagate.

So, there is no way in Excel to assign the variables in my variables sheet an integer value? I cant stop thinking how to do it in C or Java, if you know what I mean.

I just want to do

newServerCost = 114196.00

(for example)

then be able to use that in my other sheets to perform math on.

MercFan · #2 01-31-2012, 05:07 PM

Don -

The suggestions above SHOULD work for you - the best way to do this is like this below.

1) Start on the worksheet page that you want to produce the formula on (say Page2). Go to a cell and type "=" to start the formula. Then, while the formula is activated (will have a blinking border around it), navigate to the other page of the worksheet (say Page2) and click on the Cell that you want to use in your formula. This will pickup that cell from that page and place it in your formula - no problem. Formulas will looks something like the section shown below:

Code:

sheet1!A4*B4

The above reads: Contents of Cell A4 on Sheet1 multiplied by contents of B4 cell on current sheet.... etc

Continue adding arithmetic signs as needed with values from any place on the worksheet - including different pages as you see fit. Use of parenthesis is useful to avoid confusion (to you, not the program)...

I've done this successfully with a work sheet that analyzes RE investment potential: property information all on one page; mortgage schedule an another (for readability). Values on one worksheet page, reference cells on another worksheet page. Works well.

Good luck -
James

Yak · #3 01-31-2012, 09:18 PM

Quote:

Originally Posted by TheDon

So, there is no way in Excel to assign the variables in my variables sheet an integer value? I cant stop thinking how to do it in C or Java, if you know what I mean.

I just want to do

newServerCost = 114196.00

(for example)

then be able to use that in my other sheets to perform math on.

If all you want to do is have the variables visible in every sheet, a simple copy and past ought to do that. Highlight the relevant cells on sheet 1, paste them into sheets 2 and 3. You should see a number (integer) in the spreadsheet itself, but a reference to the location of the original number will be in the field above the table.

If you only want to paste the integer value, copy, then navigate to where you want to paste, right click, select "paste special", select "values" and that will only paste the current integer value of the copied cell into the pasted cell. This is useful for copying info from one file into another file. If you try to paste across files you can get errors or broken links.

I'm not sure I follow your example exactly, but putting an = in the beginning of a cell value tells that cell you're going to do something to it. When you say power (fixed number?) in A4 and variable in A5, all you need to do to multiply the constant times the variable is type in a different cell =A4*A5. If A4 is on sheet 1, that becomes =sheet1!A4*A5.

Think of each cell on each sheet as an address or location. The software assumes the cell is on the same sheet unless you tell it otherwise. =locationX[some math function]locationY should get it done.

I've done a mini template. Notice the sheets are named for the info on them. This affects the reference names. Sometimes it's a helpful touch if you're navigating through different items. I added a roundup function reference since you can't buy a fraction of a server. Excel, when used properly can be pretty powerful but you have to learn the quirks.