Excel 2010: Forcing Row number to change but keeping column (on different sheet)

[Maillme]

Hi there,

I have this formula:

=IF(All!C1="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))

I'd like to drag this accross columns in one sheet, but would like the highlighted reference to change in row number only.

So, for example:

Sheet 1:

A1: =IF(Sheet2!C1="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))
A2:=IF(Sheet2!C2="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))
A3:=IF(Sheet2!C3="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))

I tried changing it to:

A2:=IF(Sheet2!$C2="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))

But this did not work.

thanks for any help,
Neil

 

[Maillme]

Sorry, first reference to sheet name is wrong. I left "All" but this should be "Sheet2" (I replaced for examples / simplicity sake,

Neil

 

[Maillme]

Oh, and forget the rest of the formula, I copy/paste without actually taking from the excel itself (just this window here), as the references further on in the formula woudl change... but they aren't relevant for this example.

Neil

 

[zelgar]

If I'm understanding properly, you're wanting to have the formula in A1 and copy/paste it in A2 - A3 without having to edit the formulae afterwards. The problem with when you added the $ after the Sheet2! is that you didn't make the reference fixed to the rows of the data being evaluated (you only fixed that you'd be looking in Col C when you copy/pasted for looking for "Employees". I think the following is what you want:

A1: =IF(Sheet2!C1="Employees",SUMIF(D$2$25,1,$B$2:$B$25)/(SUMIF(D$2$25,1,$B$2:$B$25)+SUMIF(D$2$25,2,$B$2:$B$25)),SUMIF(D$2$25,1,$C$2:$C$25)/(SUMIF(D$2$25,1,$C$2:$C$25)+SUMIF(D$2$25,2,$C$2:$C$25)))

Alternative, you can name the ranges $D2:$D$25 and $B$2:$B$25 to make the formula look a lot neater and simple to copy/paste
Select range B2:B25 with mouse and type in the box (the name box) to the left of the formula bar with the name for the range (e.g., B_range) & do the same with the C and D ranges
A1: =IF(Sheet2!C1="Employees",SUMIF(D_range,1,B_range)/(SUMIF(D_range,1,B_range)+SUMIF(D_range,2,B_range)),SUMIF(D_range,1,C_range)/(SUMIF(D_range,1,C_range)+SUMIF(D_range,2,C_range)))

 

[Maillme]

Thanks Zelgar,

Sorry - I think because I didn't copy the actual formulas for the 2nd and 3rd examples it is not clear. (although yes, I do want to drag the formula across, but I want the row to change that is referenced in the sheet called: All

Here is the actual formulae, and highlighted what I would like to see - everything not highlighted is actual.....

=IF(All!C2="Employees",SUMIF(D225,1,$B$2:$B$25)/(SUMIF(D225,1,$B$2:$B$25)+SUMIF(D225,2,$B$2:$B$25)),SUMIF(D225,1,$C$2:$C$25)/(SUMIF(D225,1,$C$2:$C$25)+SUMIF(D225,2,$C$2:$C$25)))
=IF(All!C3="Employees",SUMIF(E2:E25,1,$B$2:$B$25)/(SUMIF(E2:E25,1,$B$2:$B$25)+SUMIF(E2:E25,2,$B$2:$B$25)),SUMIF(E2:E25,1,$C$2:$C$25)/(SUMIF(E2:E25,1,$C$2:$C$25)+SUMIF(E2:E25,2,$C$2:$C$25)))
=IF(All!C4="Employees",SUMIF(F2:F25,1,$B$2:$B$25)/(SUMIF(F2:F25,1,$B$2:$B$25)+SUMIF(F2:F25,2,$B$2:$B$25)),SUMIF(F2:F25,1,$C$2:$C$25)/(SUMIF(F2:F25,1,$C$2:$C$25)+SUMIF(F2:F25,2,$C$2:$C$25)))

I guess, because my column changes, the named ranges will not work.. right?

thanks again,
Neil

 

[SkipVought]

Well then apply the same technique to that reference that zelgar applied in his example.

 

[Maillme]

hi Skip,

thanks for your reply. The example doesn't work, because my columns are changing also, as per my 2nd example (I confused things by duplicating formulas on 1st post). Here is a screen capture of my excel, if it helps illustrate the point better:

http://s1.postimg.org/qj1fhvpvz/export.png

thanks again,
Neil

 

[SkipVought]

Give this a try.

1) SELECT any reference in a formula in a cell.

2) Hit F4, and observe what happens.

Look up in Excel HELP, Absolute & relative reference and read what the dollar sign means.

Now you have the tools to figure out your problem, because the information you have given us is INCOMPLETE to us, but your information is COMPLETE to you.

 

[zelgar]

I don't think there's an automatic way of doing this since you're wanting to copy down, but the referenced columns are changing. The easiest way of doing what you want is the following:
1. Use the current formula in A1
2. Copy formula in A1 to Cells B1 and C1
3. Cut formula from B1 to A2
4. Cut formula from C1 to A3