Excel help

Gelion

Gelion

Retired Captain
Joined
Jul 25, 2004
Messages
12,958
Location
Earth Dome
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.
 
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.
 
Gelion said:
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.
Click to expand...
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.
 
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 said:
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.
Click to expand...

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)
 
Right click on the sheet tab, select rename. Your formula should probably look something like this:
Code: 
=+Sheet2!A1+Sheet3!A1

This is for Excel2003.
 
Mr. Keith said:
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.
Click to expand...

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)
 
Turner said:
Right click on the sheet tab, select rename. Your formula should probably look something like this:
Code: 
=+Sheet2!A1+Sheet3!A1

This is for Excel2003.
Click to expand...
...double post... see below...
 
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 said:
Dunno. I would assume some kind of If/Then logic.
Click to expand...
I thought so too, but I'd have to do about 200 IFs for every state.... have to find another way :(
 
Gelion said:
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.
Click to expand...

Gelion said:
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.....:blush:
Click to expand...

Gelion said:
I thought so too, but I'd have to do about 200 IFs for every state.... have to find another way :(
Click to expand...

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 said:
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?
Click to expand...
Exactly!
BTW, mrexcel.com is a pretty good source for excel help.
Click to expand...
I will look, whats one more site? ;) Thanks!
 
Gelion said:
Exactly!
Click to expand...

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.
 
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 ;)
 

Attachments

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.
 
Mise said:
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 ;)
Click to expand...
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.
 
Mise said:
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 ;)
Click to expand...

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

It works, it really works :goodjob:
 
You must log in or register to reply here.

Similar threads

M
Mod: Full Civilization Progression Plan (120 Civs) (5 ages)
2
Replies
39
Views
7K
mclaw
M
Xefjord
Civ 7 and Ara: A Cross Comparison 100 hours in.
3 4 5
Replies
82
Views
9K
KayAU
KayAU
S
Help in modding the map
Replies
0
Views
677
sanjay_111
S
AOS9001
A case for a revised naval unit tree
Replies
14
Views
2K
AOS9001
AOS9001
A
New Version – 5.0-alpha.03
11 12 13
Replies
254
Views
38K
andersw
A
Back
Top Bottom