Excel help

[Gelion]

I've run into a little problem with the excel help program I've been designing. What I want Excel to be able to do is assign variables to cells on a specific sheet (out of many identical ones) after I specify in another cell what sheet I want values assigned to.

To give an example:
I have 5 sheets: "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5" with identical fields in them: Dollars, Franks, Roubles, Yens etc. In "Sheet0" in the cell "Market" I write "Sheet1". When I add or subtract values of Dollars, Franks and whatnot I want the results to be written to Sheet1 (formulas for these changes are of course witten in Sheet0).

Any help is appreciated.

 

[Gelion]

Better try my luck here.

I've run into a little problem with the excel help program I've been designing. What I want Excel to be able to do is assign variables to cells on a specific sheet (out of many identical ones) after I specify in another cell what sheet I want values assigned to.

To give an example:
I have 5 sheets: "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5" with identical fields in them: Dollars, Franks, Roubles, Yens etc. In "Sheet0" in the cell "Market" I write "Sheet1". When I add or subtract values of Dollars, Franks and whatnot I want the results to be written to Sheet1 (formulas for these changes are of course witten in Sheet0).

Any help is appreciated.

 

[Souron]

Better try my luck here.

I've run into a little problem with the excel help program I've been designing. What I want Excel to be able to do is assign variables to cells on a specific sheet (out of many identical ones) after I specify in another cell what sheet I want values assigned to.

To give an example:
I have 5 sheets: "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5" with identical fields in them: Dollars, Franks, Roubles, Yens etc. In "Sheet0" in the cell "Market" I write "Sheet1". When I add or subtract values of Dollars, Franks and whatnot I want the results to be written to Sheet1 (formulas for these changes are of course witten in Sheet0).

Any help is appreciated.

So sheets 1-5 must each have their own area to print the results. Since the formulas are on Sheet0, these must be simple references to Sheet0. Then you have a square for which sheet you want the results to be in, such that a sheet will not display a the result unless that square has the right value. Sheet0 will always have the unit-less results, but these can be in hidden cells.

Note that Excel does not have variables, only cells.

 

[Mr. Keith]

Your post was dated 12/27, so I am sure you found your solution. I don't know if I fully understand your question, but as I understand it, you can probably use some =IF() functions.

 

[Souron]

I answered this in the "Computer Questions Not Worth Their Own Thread." thread.

 

[Gelion]

So sheets 1-5 must each have their own area to print the results. Since the formulas are on Sheet0, these must be simple references to Sheet0. Then you have a square for which sheet you want the results to be in, such that a sheet will not display a the result unless that square has the right value. Sheet0 will always have the unit-less results, but these can be in hidden cells.

Note that Excel does not have variables, only cells.

I know its been a while, but I still haven't been able to do what you describe. Could you re-explain somehow?

If you want my question to be simpler:
How can I set Sheet name in a cell? (for a formula)

 

[Turner]

Right click on the sheet tab, select rename. Your formula should probably look something like this:

=+Sheet2!A1+Sheet3!A1

This is for Excel2003.

 

[Gelion]

Your post was dated 12/27, so I am sure you found your solution. I don't know if I fully understand your question, but as I understand it, you can probably use some =IF() functions.

What I want is to set sheet name in a cell (formula). I.e =
Cell A2 would contain: "=Sheet0(B1+B3) where Sheet0 would be changeable according to whatever is entered in another cell (i.e. A1=Sheet0 or Sheet1 or Sheet3)

 

[Gelion]

Right click on the sheet tab, select rename. Your formula should probably look something like this:

=+Sheet2!A1+Sheet3!A1

This is for Excel2003.

...double post... see below...

 

[Gelion]

I think I found a better way of explaining:

=+Sheet2!A1+Sheet3!A1 is good but I need excel to be able to change sheet name in the formula if I change 1 other value on this sheet.
Here (each line is a cell(
Sheet=SetSheet
SetSheet=RUS
CountryTanks=SetSheet!B1+entryvalue
Entryvalue=100
Which would give me a chance to add 100to Russian tanks on RUS sheet.
If I were to add tanks to germany, then I would change just one line:
Sheet=SetSheet
SetSheet=GER
CountryTanks=SetSheet!B1+entryvalue
Entryvalue=100
Thus for each of the cases the "actual" formula is:
CountryTanks=RUS!B1+entryvalue
or
CountryTanks=GER!B1+entryvalue
but I want it to be customizable....

 

[Turner]

Dunno. I would assume some kind of If/Then logic.

 

[Gelion]

Dunno. I would assume some kind of If/Then logic.

I thought so too, but I'd have to do about 200 IFs for every state.... have to find another way

 

[Birdjaguar]

Better try my luck here.

I've run into a little problem with the excel help program I've been designing. What I want Excel to be able to do is assign variables to cells on a specific sheet (out of many identical ones) after I specify in another cell what sheet I want values assigned to.

To give an example:
I have 5 sheets: "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5" with identical fields in them: Dollars, Franks, Roubles, Yens etc. In "Sheet0" in the cell "Market" I write "Sheet1". When I add or subtract values of Dollars, Franks and whatnot I want the results to be written to Sheet1 (formulas for these changes are of course witten in Sheet0).

Any help is appreciated.

Perhaps I am not clear again, but its a bit hard to explain.
=+Sheet2!A1+Sheet3!A1 is good but I need excel to be able to change sheet name in the formula if I change 1 other value on this sheet.
Here (each line is a cell(
Sheet=SetSheet
SetSheet=RUS
CountryTanks=SetSheet!B1+entryvalue
Entryvalue=100
Which would give me a chance to add 100to Russian tanks on RUS sheet.
If I were to add tanks to germany, then I would change just one line:
Sheet=SetSheet
SetSheet=GER
CountryTanks=SetSheet!B1+entryvalue
Entryvalue=100
Thus for each of the cases the "actual" formula is:
CountryTanks=RUS!B1+entryvalue
or
CountryTanks=GER!B1+entryvalue
but I want it to be customizable. There.....

I thought so too, but I'd have to do about 200 IFs for every state.... have to find another way

Gelion, it sounds like you are trying to update specific "nation" worksheets from a different worksheet so you don't have to go to those individual worksheets. Is that correct?

BTW, mrexcel.com is a pretty good source for excel help.

 

[Birdjaguar]

http://forums.civfanatics.com/showpost.php?p=7616524&postcount=149

I didn't see this thread.

 

[Gelion]

Gelion, it sounds like you are trying to update specific "nation" worksheets from a different worksheet so you don't have to go to those individual worksheets. Is that correct?

Exactly!

BTW, mrexcel.com is a pretty good source for excel help.

I will look, whats one more site? Thanks!

 

[Birdjaguar]

Exactly!

Since you want to update maybe 40 worksheets from a single one, why not just set up a specific cell (or row of cells) on the update sheet for each of the nation tabs. That way all your updates happen from a single sheet with any need to change any links.

 

[Mise]

Can you upload the spreadsheet (or a demo/example if you don't want to upload the whole thing)? I can't picture it in my head.

Try looking at the "INDIRECT" function, not sure if that will help though.

Also, have a look at the attached to see if it's what you want. You will need to go into tools -> options -> calculation and select "manual calculation", check "enable iteration", type "1" into the iteration box (I think it defaults to 100 but you need to change it to 1), and uncheck "recalculate before save". You need to do this before opening the spreadsheet or else it will give you an error.

You type the sheet name in B1 and the value in B2 of the UpdSht. Then, every time you press F9, it will update the sheet in B1 by that value. (Actually it doesn't use sheet names at all because I don't know what excel function returns the current sheet's name. It just uses the cell in B2 of each sheet instead.)


Alternatively, you will no doubt be able to do exactly what you want with VBA. Learning VBA will be a lot easier than trying to hack stuff together with excel functions

 

[warpus]

Can't you store the name of the Sheet in a variable, and then reference that in a SheetCurrent!Whatever type way?

There must be a way to do that.

Is there a way to evaluate expressions in Excel before they're parsed? That would do it.

 

[Birdjaguar]

Can you upload the spreadsheet (or a demo/example if you don't want to upload the whole thing)? I can't picture it in my head.

Try looking at the "INDIRECT" function, not sure if that will help though.

Also, have a look at the attached to see if it's what you want. You will need to go into tools -> options -> calculation and select "manual calculation", check "enable iteration", type "1" into the iteration box (I think it defaults to 100 but you need to change it to 1), and uncheck "recalculate before save". You need to do this before opening the spreadsheet or else it will give you an error.

You type the sheet name in B1 and the value in B2 of the UpdSht. Then, every time you press F9, it will update the sheet in B1 by that value. (Actually it doesn't use sheet names at all because I don't know what excel function returns the current sheet's name. It just uses the cell in B2 of each sheet instead.)


Alternatively, you will no doubt be able to do exactly what you want with VBA. Learning VBA will be a lot easier than trying to hack stuff together with excel functions

That is an interesting solution and if Gelion's application is not too complicated, it might work. A lot may hang on whether or not having to manually recalc will create additional work to offset this labor saving routine. Given what he is trying to do, it is very possible that he could have many cells on each nation tab to update.

 

[Gelion]

Can you upload the spreadsheet (or a demo/example if you don't want to upload the whole thing)? I can't picture it in my head.

Try looking at the "INDIRECT" function, not sure if that will help though.

Also, have a look at the attached to see if it's what you want. You will need to go into tools -> options -> calculation and select "manual calculation", check "enable iteration", type "1" into the iteration box (I think it defaults to 100 but you need to change it to 1), and uncheck "recalculate before save". You need to do this before opening the spreadsheet or else it will give you an error.

You type the sheet name in B1 and the value in B2 of the UpdSht. Then, every time you press F9, it will update the sheet in B1 by that value. (Actually it doesn't use sheet names at all because I don't know what excel function returns the current sheet's name. It just uses the cell in B2 of each sheet instead.)


Alternatively, you will no doubt be able to do exactly what you want with VBA. Learning VBA will be a lot easier than trying to hack stuff together with excel functions

Better late than never. I recently got to work on an excel project again and your solution worked like magic. Thank you! If you ever want to join any of my games you'll get the best place possible

It works, it really works