Excel Question Du Jour

DanoooME

above replacement level
SoSH Member
Mar 16, 2008
19,831
Henderson, NV
I have a spreadsheet where I enter players for every team on one tab and then the next tab has those same players organized by position.  I use colors to note different things about the players on one tab(like guys on the DL, guys that play multiple positions, etc.) and I can't seem to find in Excel help how to get those colors to come over to the other tab.
 
The formula I use to carry the player name over from the previous tab is:
 
='By Team'!$O$3
 
For some reason, I can't find what I need to add to the formula to carry over the color from one tab to the other.  Anyone know?  Excel help hasn't been very useful; maybe I'm not using the correct terminology.
 
Thanks!
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
Why not use conditional formatting in the first tab to get the colors, and then just apply the same on tab 2?
 

tonyandpals

Well-Known Member
Lifetime Member
SoSH Member
Mar 18, 2004
7,856
Burlington
DanoooME said:
I have a spreadsheet where I enter players for every team on one tab and then the next tab has those same players organized by position.  I use colors to note different things about the players on one tab(like guys on the DL, guys that play multiple positions, etc.) and I can't seem to find in Excel help how to get those colors to come over to the other tab.
 
The formula I use to carry the player name over from the previous tab is:
 
='By Team'!$O$3
 
For some reason, I can't find what I need to add to the formula to carry over the color from one tab to the other.  Anyone know?  Excel help hasn't been very useful; maybe I'm not using the correct terminology.
 
Thanks!
 
I don't know that you can.  The formatting lives at the cell level, while the formula you are writing in just pulling in the data from the cell.  Usually there is a way to do everything though...
 
As mentioned above, conditional formatting may be your answer. Just apply the logic you are using in your head to color the cells and put it into the conditional formatting parameters.
 

savage362

Member
SoSH Member
Apr 16, 2003
1,389
Vermont
Conditional formatting is isolated to only the worksheet it's applied on. You cannot select a range from sheet1 and a range from sheet 2 and apply the same conditional formatting rule. You'll have to create two separate (but identical) conditional formatting rules.
 
I'd recommend applying the conditional formatting on the first tab, and then recreating the conditional formatting rules on the second tab.
 

DanoooME

above replacement level
SoSH Member
Mar 16, 2008
19,831
Henderson, NV
Yeah, I don't have any rules I used for the formatting.  I manually applied the colors based on different information I had.  I do have a column off to the side with those values and colors.  I guess I might be able to build a rule based on that.
 
M

MentalDisabldLst

Guest
If he does copy-and-paste and selects the paste option "Link Cells", would that preserve the formatting while giving him a link to the source data (rather than a copy)?
 
He might also be able to copy-and-paste the whole section, to preserve formatting in the paste, but then change the values by copying a reference and not fixing its location (i.e. ='By Team'!O3, rather than ='By Team'!$O$3) and let him fill the stuff like that more quickly.
 
[SIZE=12.727272033691406px]The formatting won't dynamically change when the underlying referenced cell changes (in value or format), however.  If you want formatting that varies based on the format or contents of another cell, use Conditional Formatting rules as discussed earlier.[/SIZE]
 

VTSox

Member
SoSH Member
Jun 27, 2006
293
Couldn't you just use 1 sheet and resort by whatever criteria you want to use at the time?
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
So I am not sure why I have never really run into this before, but I need to figure out how to increment cell #'s in situations like this:
 
I have two sheets. In sheet 2 I have formulas which pull data from sheet 1 if the cells are not blank. There is a second row which pulls data from sheet 2, like this:
 
Row 1:    =IF(Sheet1!$A2<>"",Sheet1!A2,"")
Row 2:    =IF(F2<>"","#   ","")
 
When I copy these down, I need the number in the first row to increment by one, and the number in row 2 is incrementing fine with Excel's default. 
 

kelpapa

Costanza's Hero
SoSH Member
Feb 15, 2010
4,637
Delete the dollar sign.

This seems like a dumb solution, so maybe I'm not understanding your question.

Edit. Dollar not number.
 

tonyandpals

Well-Known Member
Lifetime Member
SoSH Member
Mar 18, 2004
7,856
Burlington
mt8thsw9th said:
So I am not sure why I have never really run into this before, but I need to figure out how to increment cell #'s in situations like this:
 
I have two sheets. In sheet 2 I have formulas which pull data from sheet 1 if the cells are not blank. There is a second row which pulls data from sheet 2, like this:
 
Row 1:    =IF(Sheet1!$A2<>"",Sheet1!A2,"")
Row 2:    =IF(F2<>"","#   ","")
 
When I copy these down, I need the number in the first row to increment by one, and the number in row 2 is incrementing fine with Excel's default. 
 
I'm having trouble following. What are you copying down? Just row 2?
 
When you say you need the number in the 1st row to increment by 1, where do you want that result?
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
tonyandpals said:
 
I'm having trouble following. What are you copying down? Just row 2?
 
When you say you need the number in the 1st row to increment by 1, where do you want that result?
 
I'm copying down both. What is listed in Row 1 is actually what F2 references.
 
What happens when I copy down:
 
Sheet1!A2
F2
Sheet1!A4
F4
Sheet1!A6
F6
 
The F2 and so on are correct, it's just looking at the cell above it. However, Sheet1!A2 needs to increment by one given that it is looking at each line in Sheet1 and asking if the cell is blank or populated.
 

tonyandpals

Well-Known Member
Lifetime Member
SoSH Member
Mar 18, 2004
7,856
Burlington
mt8thsw9th said:
 
I'm copying down both. What is listed in Row 1 is actually what F2 references.
 
What happens when I copy down:
 
Sheet1!A2
F2
Sheet1!A4
F4
Sheet1!A6
F6
 
The F2 and so on are correct, it's just looking at the cell above it. However, Sheet1!A2 needs to increment by one given that it is looking at each line in Sheet1 and asking if the cell is blank or populated.
 
Gotcha
 
Can you move Row 2 to Row 1, Column B?
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
In theory, but Sheet2 feeds into Sheet3 which then pulls in what results from the above formulas (I have a macro which filters to non-blank cells and copies to the clipboard--I am scanning a big spreadsheet and mapping it to a format which an EHR system can read). In order to get both of those results, you'd need to have the first line look for A1, and the second look for B1, thus when you copy these down you'll have the same sequential issue.
 
The workbook works and works well...but the issue this is causing is that it takes a ton of manual rework to fix the cell increments in row 1. As it stands I can only process 200 rows of data with the workbook because I have to manually update all of the formulas that do the heavy lifting.
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
I'll give a look when I get home, but thank you.

As it stands most of what the workbook is used for is to process 20-100 records, but we may end up using this company-wide and some groups will need to be able to process a couple thousand records at a time. I am giving a demo on Friday and it's not crucial that I find the fix, but it would be nice to have.
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
Alright, so I am still lost. How do I combine that with an IF statement? On its own that just points to a cell that is not the one you are referencing (so it's worthless), but I understand that you can combine this to a formula to have it point back a cell when you're copy/pasting the formula. I am just trying to wrap my head around it, and all of the examples I can find online don't deal with IF functions.
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
And my workaround*, which would be to do what you suggested, i.e., have the values in A1 and B1, and then to concatenate them in the sheet that I use for the output file and replace a character (which is text 2 in the concat) with a carriage return (this will have the values in separate rows in the txt file) doesn't work because for some reason Excel embeds ""s around the values when you paste into Notepad.
 
*so what in theory would work is the above, the A1/B1, and I have a macro which copies/pastes the values, and then does a replace of a special character with the carriage return. While it looks like this in Excel:
 

ABF,33322100,1
#   CLM 02-12 CMS 1500
 
Which is what I need, it does this when I paste into Notepad to create the txt import file:
 
"ABF,33322100,1
#   CLM 02-12 CMS 1500"
,
 
This kicks out of the system due to the unneeded " and ,
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
Alright, so I came up with a workaround which accomplishes what I needed, though it made things a bit more busy. I ended up "solving" the increment bit by plugging numbers into a column in increments of .5, and added a column next to it which concatenated the Sheet1!A with the number, which since it's incrementing in 2 when I copy the formulas, that will make it go 1, 2, 3 since it is skipping over the .5s. 
 
I used the INDIRECT function to then point at the cell with the correct target, i.e.
 
=IF(Sheet1!$A2<>"",Sheet1!A2,"")
 
became
 
=IF(INDIRECT(J2,TRUE)<>"",INDIRECT(J2,TRUE),"")
 
In cell J2 is the text Sheet1!$A2
 

mt8thsw9th

anti-SoSHal
SoSH Member
Jul 17, 2005
17,120
Brooklyn
Just an FYI for anyone trying to use CTRL+G /special/blanks to fill blank cells: it doesn't work on big documents, and the VBA I found, well they've been mostly garbage. I kind of stumbled upon doing this:
 
CTRL+F
 
Find what: blanks
Replace with: =INDIRECT(ADDRESS(ROW()-1,COLUMN()))
 
Seriously, that just saved me hours of trying to get VBA to work, or trying to condense documents enough to use special/blanks.
 

TroyOLeary

New Member
Jul 22, 2005
178
mt8thsw9th said:
Alright, so I came up with a workaround which accomplishes what I needed, though it made things a bit more busy. I ended up "solving" the increment bit by plugging numbers into a column in increments of .5, and added a column next to it which concatenated the Sheet1!A with the number, which since it's incrementing in 2 when I copy the formulas, that will make it go 1, 2, 3 since it is skipping over the .5s. 
 
I used the INDIRECT function to then point at the cell with the correct target, i.e.
 
=IF(Sheet1!$A2<>"",Sheet1!A2,"")
 
became
 
=IF(INDIRECT(J2,TRUE)<>"",INDIRECT(J2,TRUE),"")
 
In cell J2 is the text Sheet1!$A2
 
Probably too little too late, and your solution certainly works, but you can do the above without the extra columns:
 
=INDIRECT("Sheet1!A"&ROUND((ROW()/2),0))
 
or
 
=INDEX(Sheet1!A:A,ROUND(ROW()/2,0))  
 
The latter you can extended to other columns much more easily than INDIRECT.
 

24JoshuaPoint

Grand Theft Duvet
SoSH Member
Nov 4, 2004
4,944
Cell Block C
Stupid question. I recently was given a newer version of Excel. I'm on 2013 now. For some reason every worksheet i open it opens another whole instance of the program. So now when i try arrange multiple worksheets horizontally or vertically i get all the commands on every sheet.
 
How do i change it so i'm only running one instance with one set of commands for all sheets?
 

Caspir

Member
SoSH Member
Jul 16, 2005
6,886
So I'm an Excel dummy heading into a toolkit at work to give me a crash course. One thing they asked me to do before hand involved some basic stuff to demonstrate that I knew some formulas, etc., but one thing I'm completely stuck on and can't figure out.
 
So I want to run 1000 simulations of a hypothetical 7 game series between Team A and Team B based on a probability (can be any prob.), then graph out how many series end in 4, 5, 6 or 7 games. The issue I have is that I can't figure out how to run a sim like that. How do I set something up to where the sim will stop once one team wins 4 games, etc? Is there a simple command or something that I'm missing here? I know, sompletely newb question, but it's pissing me off more than anything at this point.
 

TroyOLeary

New Member
Jul 22, 2005
178
Caspir said:
So I'm an Excel dummy heading into a toolkit at work to give me a crash course. One thing they asked me to do before hand involved some basic stuff to demonstrate that I knew some formulas, etc., but one thing I'm completely stuck on and can't figure out.
 
So I want to run 1000 simulations of a hypothetical 7 game series between Team A and Team B based on a probability (can be any prob.), then graph out how many series end in 4, 5, 6 or 7 games. The issue I have is that I can't figure out how to run a sim like that. How do I set something up to where the sim will stop once one team wins 4 games, etc? Is there a simple command or something that I'm missing here? I know, sompletely newb question, but it's pissing me off more than anything at this point.
 
It's probably easiest to do in a macro, but assuming you want to use excel formulas you could do something like this:
 
Columns A:G will be your 7 game series, so you have something like =LOOKUP(RAND(),{0,0.5},{"A","B"}) in each of those columns.
 
Then in columns H:N have a series of countifs that progress through the number of game possibilities:
 
Identifying the 4 game series:
Column H: =COUNTIF(A1:D1,"A")
Column I: =IF(OR(H1=0,H1=4),"4 games","")
 
Identifying the 5 game series:
Column J: =IF(I1="",COUNTIF(A1:E1,"A"),"")
Column K: =IF(OR(J1=1,J1=4),"5 games","")
 
Identifying the 6 game series:
Column L: =IF(AND(I1="",K1=""),COUNTIF(A1:F1,"A"),"")
Column M: =IF(OR(L1=2,L1=4),"6 games","")
 
 
Identifying the 7 game series:
Column N: =IF(AND(I1="",K1="",M1=""),"7 games","")
 
You would then copy those down 1000 rows, or however many trials you're doing.
 
It's possible excel has some built-in functions I'm not aware of that make this easier, or I'm just missing an easier algorithmic way to do it, but it doesn't seem like that simple a problem for a relative beginner.
 

Caspir

Member
SoSH Member
Jul 16, 2005
6,886
TroyOLeary said:
 
It's probably easiest to do in a macro, but assuming you want to use excel formulas you could do something like this:
 
Columns A:G will be your 7 game series, so you have something like =LOOKUP(RAND(),{0,0.5},{"A","B"}) in each of those columns.
 
Then in columns H:N have a series of countifs that progress through the number of game possibilities:
 
Identifying the 4 game series:
Column H: =COUNTIF(A1:D1,"A")
Column I: =IF(OR(H1=0,H1=4),"4 games","")
 
Identifying the 5 game series:
Column J: =IF(I1="",COUNTIF(A1:E1,"A"),"")
Column K: =IF(OR(J1=1,J1=4),"5 games","")
 
Identifying the 6 game series:
Column L: =IF(AND(I1="",K1=""),COUNTIF(A1:F1,"A"),"")
Column M: =IF(OR(L1=2,L1=4),"6 games","")
 
 
Identifying the 7 game series:
Column N: =IF(AND(I1="",K1="",M1=""),"7 games","")
 
You would then copy those down 1000 rows, or however many trials you're doing.
 
It's possible excel has some built-in functions I'm not aware of that make this easier, or I'm just missing an easier algorithmic way to do it, but it doesn't seem like that simple a problem for a relative beginner.
 
 
Thanks a million for this. I figured Macro was the way to go, but I feel more comfortable using formulas so this is exactly what I was looking for. Excel is a confusing beast.