VBA or not? Absolute value question

[Iron Sheik]

Hi,

I've been trying to get my head around an absolute value problem but no luck.

I'm trying to get an IF statement with absolute values to copy down a few thousand cells in a spread sheet but can't think of a way to do it other than to manually go into each cell and change the reference.

The formula is:

=IF($M$1222=U$11,$H1222,0)

What I want to achieve is the first absolute value ($M$1222) to change each time I copy it down the column. So if I drag the formula down the column, the value $M$1222 will automatically update to $M$1223, $M$1224 and so on. The formula is then copied across the rest of a table which has about 100 columns so the value in column M needs to stay absolute as it is comparing its value to values in different cells across row 11.

Can this be done or have I painted myself into a corner? Do I have to use VBA or can it be done without VBA?


Cheers

[Aflatoon]

You want:
=IF($M1222=U$11,$H1222,0)
so the row adjusts as you copy down, but column M remains the same as you copy across.

[Iron Sheik]

Hi Aflatoon,

The problem I have with that solution is the value in cell $M$1222 needs to be constant as this is then copied across the table to the other cells.

If I don't absolute the whole value, I then have to make the value absolute in every other cell manually.

This is a sample of how the rest of the spread sheet looks going across the table from column U:

=IF($M$1222=U$11,$H1222,0)
=IF($M$1222=V$11,$H1222,0)
=IF($M$1222=W$11,$H1222,0)
=IF($M$1222=X$11,$H1222,0)

So when I go down to the next row, the formula should be (going across the table again):

=IF($M$1223=U$11,$H1223,0)
=IF($M$1223=V$11,$H1223,0)
=IF($M$1223=W$11,$H1223,0)
=IF($M$1223=X$11,$H1223,0)

Is there any way to make the formula work?

[Aflatoon]

That is what will happen if you do what I said.

[SamT]

I must be missing something. Why wouldn't this work?
=IF($M1222=U$11,$H1222,0)

Filling ToRight does not change the Row reference in a formula, just as Filling Down does not change the Column reference.

[Paul_Hossler]

BTW, the term is 'Absolute Reference' not 'Absolute Value' -- I were confuzed

=IF($M1222=U$11,$H1222,0)

Columns are fixed at M and at H, just like the row is fixed at 11


Just filling down the first col so M and H stay

=IF($M1222=U$11,$H1222,0)
=IF($M1223=U$11,$H1223,0)
=IF($M1224=U$11,$H1224,0)
=IF($M1225=U$11,$H1225,0)

and filling across (for example) the first row into the next columns so the column in row 11 changes


Just filling across the first row

=IF($M1222=U$11,$H1222,0)     =IF($M1222=V$11,$H1222,0)     =IF($M1222=W$11,$H1222,0)     =IF($M1222=X$11,$H1222,0)

So 1 JPG = 1K TXT in screen shot, the blue formula fills down and across

Col A is fixed, row updates
Row 1 is fixed, column updates
Col D is fixed, row updates

Try Afltoon's formula -- I should do what you said

[Iron Sheik]

Hello Aflatoon, SamT and Paul,

Thanks for all your input.

The suggested formula worked. I guess I had been staring at the screen for too long and missed the extra $ which made the whole reference absolute rather than just the row.

After coming back to the problem a few days later, I tried it and it worked.

Thanks again.