Urgent Project, Please Help: Copying Formulas

[Guest]

Hi
I am working with a spreadsheet that was created by someone else, this is the forumla that is being used in the worksheet
=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9)

I want to
Drag and Drop this formula in a column where column reference changes, i.e. B, C, D, E, F; but the row reference stays the same, i.e. B9. However, the exact oposite happends when i do this, column does not change, but the reference to the row does

Now, I 've tried drag and drop across the row, and the column reference doesn' changed, but the row reference stays contstant.

How can I solve this problem
Also, when I use cut and past of these formulas into the next column for some reason some of the cell references seem to jump all over the place. Example: I have a column
=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9)
=IF(ISBLANK('Raw Data'!$C9), "N/A", ('Raw Data'!C9)
=IF(ISBLANK('Raw Data'!$D9), "N/A", ('Raw Data'!D9)

when I copy it over in the exact same order, but new column, it does things like
=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9)
=IF(ISBLANK('Raw Data'!$E9), "N/A", ('Raw Data'!E9)
=IF(ISBLANK('Raw Data'!$J9), "N/A", ('Raw Data'!J9)

i.e. changes column reference

I would really appreciate any tips with this, right now i am doing what I can (i.e. like a total idiot sitting there and manually changing column reference in each cell

Thanks
K

 

[Frank Kabel]

Hi
try
=IF(ISBLANK('Raw Data'!B$9), "N/A", ('Raw Data'!B$9))

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,
I am working with a spreadsheet that was created by someone else,

this is the forumla that is being used in the worksheet:

=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))

I want to:
Drag and Drop this formula in a column where column reference

changes, i.e. B, C, D, E, F; but the row reference stays the same, i.e.
B9. However, the exact oposite happends when i do this, column does not
change, but the reference to the row does.

Now, I 've tried drag and drop across the row, and the column

reference doesn' changed, but the row reference stays contstant.

How can I solve this problem?
Also, when I use cut and past of these formulas into the next column

for some reason some of the cell references seem to jump all over the
place. Example: I have a column:

=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))
=IF(ISBLANK('Raw Data'!$C9), "N/A", ('Raw Data'!C9))
=IF(ISBLANK('Raw Data'!$D9), "N/A", ('Raw Data'!D9))

when I copy it over in the exact same order, but new column, it does things like:
=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))
=IF(ISBLANK('Raw Data'!$E9), "N/A", ('Raw Data'!E9))
=IF(ISBLANK('Raw Data'!$J9), "N/A", ('Raw Data'!J9))

i.e. changes column reference.

I would really appreciate any tips with this, right now i am doing

what I can (i.e. like a total idiot sitting there and manually
changing column reference in each cell)

 

[Frank Kabel]

Hi
are you copying this down per column?. If yes try
=IF(ISBLANK(OFFSET('Raw Data'!$B$9,0,ROW(1:1)-1)), "N/A", (OFFSET('Raw
Data'!$B$9,0,ROW(1:1)-1)))


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Unfortunately that didn't work, all it does it keeps both the Row and

Column values of B$9 all the way down to the last entry. Where as I
need the $9 value to stay constant but Bto change to C to DFE, etc...

 

[Paul]

Maybe I am missing something, but what happens if you use:

=IF(ISBLANK('raw data'!B9), "N/A", ('raw data'!B9))

-----Original Message-----
Hi,
I am working with a spreadsheet that was created by

someone else, this is the forumla that is being used in
the worksheet:

=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))

I want to:
Drag and Drop this formula in a column where column

reference changes, i.e. B, C, D, E, F; but the row
reference stays the same, i.e. B9. However, the exact
oposite happends when i do this, column does not change,
but the reference to the row does.

Now, I 've tried drag and drop across the row, and the

column reference doesn' changed, but the row reference
stays contstant.

How can I solve this problem?
Also, when I use cut and past of these formulas into the

next column for some reason some of the cell references
seem to jump all over the place. Example: I have a column:

=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))
=IF(ISBLANK('Raw Data'!$C9), "N/A", ('Raw Data'!C9))
=IF(ISBLANK('Raw Data'!$D9), "N/A", ('Raw Data'!D9))

when I copy it over in the exact same order, but new column, it does things like:
=IF(ISBLANK('Raw Data'!$B9), "N/A", ('Raw Data'!B9))
=IF(ISBLANK('Raw Data'!$E9), "N/A", ('Raw Data'!E9))
=IF(ISBLANK('Raw Data'!$J9), "N/A", ('Raw Data'!J9))

i.e. changes column reference.

I would really appreciate any tips with this, right now i

am doing what I can (i.e. like a total idiot sitting
there and manually changing column reference in each cell)