Excel Question #46 - Page 3

Betty · 01-27-2009, 08:38 PM

Quote:

Originally Posted by Drince88

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

That's a great trick!

Kevy Baby · 01-27-2009, 09:49 PM

Quote:

Originally Posted by Drince88

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

Great tip - I shall try this tomorrow.

ETA: I just tested and it works!

Alex · 01-27-2009, 10:20 PM

That said, if you're going to group every cell each time you want to update it would probably be about as fast to just copy and paste into each cell.

Though I'd completely forgotten about grouping.

Kevy Baby · 01-27-2009, 10:22 PM

Quote:

Originally Posted by Alex

That said, if you're going to group every cell each time you want to update it would probably be about as fast to just copy and paste into each cell.

I disagree. Since there are 27 tabs, grouping the tabs is much quicker.

Alex · 01-27-2009, 10:25 PM

Yes, but if you want to highlight each cell you still have to click on it (and make sure you correctly ctrl-click with each so you don't clear the selections you've already made and then find that you had halfway through and your group only applied to the last six you selected).

If you're going to leave the cells grouped permanently then I agree it is a great solution.

Kevy Baby · 01-27-2009, 10:34 PM

If I first click on the last tab, then shift-click on the second tab (the first I would need to modify), make the change in the cell(s) on that tab, it makes the global change pretty quickly.

Global changes are fairly minimal (simply to accommodate the every-changing whims of the boss), so I believe this should be a good solution.

Ghoulish Delight · 02-02-2009, 11:42 AM

My turn. I'm sure this one is blindingly stupid, but I can't for the life of me come up with the solution.

Cell A1 has a formula that results in a column letter.
Cell A2 has a formula that results in a row number.

I want cell A3 to be the value of the cell that would be referenced by the values of A1 and A2.

So if the formula in A1 results in "C", and the formula in A2 results in "6", I want A3 to be the value of cell C6.

Moonliner · 02-02-2009, 12:00 PM

Quote:

Originally Posted by Ghoulish Delight

My turn. I'm sure this one is blindingly stupid, but I can't for the life of me come up with the solution.

Cell A1 has a formula that results in a column letter.
Cell A2 has a formula that results in a row number.

I want cell A3 to be the value of the cell that would be referenced by the values of A1 and A2.

So if the formula in A1 results in "C", and the formula in A2 results in "6", I want A3 to be the value of cell C6.

=INDIRECT(A1&A2)

Ghoulish Delight · 02-02-2009, 12:06 PM

I knew there had to be away to do it in one line. Thanks Moonie.

Kevy Baby · 02-03-2009, 08:28 PM

Okay, I know I am going to smack my forehead when the answer gets posted because I know the answer is painfully simple and I am just clueless on this one.

I want the text in cell A1 to not print if the value in cell A2 is blank. I thought the easiest way to accomplish this was to conditionally format cell A1 with a white font if A2 is = 0. However, what is the stinking formula I would need to enter into the "Formula Is" cell in Condition 1 to accomplish this? I know how to set the formatting once I have a True result.

ETA: I found my own answer. The syntax was what was confusing me. In the cell, I had to enter =(A2=0)

01-27-2009, 10:20 PM	#23
Alex . Join Date: Feb 2005 Posts: 13,354	That said, if you're going to group every cell each time you want to update it would probably be about as fast to just copy and paste into each cell. Though I'd completely forgotten about grouping.
	Submit to Quotes

01-27-2009, 10:25 PM	#25
Alex . Join Date: Feb 2005 Posts: 13,354	Yes, but if you want to highlight each cell you still have to click on it (and make sure you correctly ctrl-click with each so you don't clear the selections you've already made and then find that you had halfway through and your group only applied to the last six you selected). If you're going to leave the cells grouped permanently then I agree it is a great solution.
	Submit to Quotes

01-27-2009, 10:34 PM	#26
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	If I first click on the last tab, then shift-click on the second tab (the first I would need to modify), make the change in the cell(s) on that tab, it makes the global change pretty quickly. Global changes are fairly minimal (simply to accommodate the every-changing whims of the boss), so I believe this should be a good solution. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

02-02-2009, 11:42 AM	#27
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	My turn. I'm sure this one is blindingly stupid, but I can't for the life of me come up with the solution. Cell A1 has a formula that results in a column letter. Cell A2 has a formula that results in a row number. I want cell A3 to be the value of the cell that would be referenced by the values of A1 and A2. So if the formula in A1 results in "C", and the formula in A2 results in "6", I want A3 to be the value of cell C6. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

02-02-2009, 12:06 PM	#29
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	I knew there had to be away to do it in one line. Thanks Moonie. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

02-03-2009, 08:28 PM	#30
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Okay, I know I am going to smack my forehead when the answer gets posted because I know the answer is painfully simple and I am just clueless on this one. I want the text in cell A1 to not print if the value in cell A2 is blank. I thought the easiest way to accomplish this was to conditionally format cell A1 with a white font if A2 is = 0. However, what is the stinking formula I would need to enter into the "Formula Is" cell in Condition 1 to accomplish this? I know how to set the formatting once I have a True result. ETA: I found my own answer. The syntax was what was confusing me. In the cell, I had to enter =(A2=0) __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln Last edited by Kevy Baby : 02-03-2009 at 08:35 PM.
	Submit to Quotes