Urgent Project, Please Help: Copying Formulas

K

Ksenya

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
 
F

Frank Kabel

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

--
Regards
Frank Kabel
Frankfurt, Germany

Ksenya said:
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)
 
F

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

Ksenya said:
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...
 
P

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top