Having a brain freeze in excel...

[BJIII]

This question is about copy / pasting formulas across cells.


I have two sheets in the same workbook:

Feature Sheet
Submission Sheet

I want to copy a column from the Feature sheet and paste it into a row on the submisson sheet and have it update automatically on the submission sheet if I change the feature sheet. I would rather just link the cells then use a macro to do it.

I made the formula:

=IF('Feature Sheet'!A19>0.5,'Feature Sheet'!A19," ")

and it works fine as if there is the issue is with copying this formula across the rows on the Submission sheet without having the modify the formula for each cell.

Example I would need to do the following:

Cell B10: =IF('Feature Sheet'!A19>0.5,'Feature Sheet'!A19," ")
Cell C10: =IF('Feature Sheet'!A20>0.5,'Feature Sheet'!A20," ")
Cell D10: =IF('Feature Sheet'!A21>0.5,'Feature Sheet'!A21," ")
Cell E10: =IF('Feature Sheet'!A22>0.5,'Feature Sheet'!A22," ")

I don't want to have to manually type the updated cell number as I could have a few hundred cells to edit in some cases.

Any help is much appreciated and please let me know if you need an other information!

Thanks,
BJW

 

[dannac]

Just put the formula in the top cell needed and copy down

 

[Glory]

Hover over the lower-right-hand corner of the cell and drag across your range.

 

[BJIII]

I don't think I was clear in my original post. I know how to drag / copy cells and formulas. What I need is the row# in the formula to change not the column.


What I don't know how to do without a macro is to keep the same column will call it A and as it increase in rows A10, A11, A12, A13, etc... I want to paste those rows respectively into another sheet but as the first sheet increases in row # I want the second sheet to increase in column # but know what row it came from.

Previous example:

Cell B10: =IF('Feature Sheet'!A19>0.5,'Feature Sheet'!A19," ")
Cell C10: =IF('Feature Sheet'!A20>0.5,'Feature Sheet'!A20," ")
Cell D10: =IF('Feature Sheet'!A21>0.5,'Feature Sheet'!A21," ")
Cell E10: =IF('Feature Sheet'!A22>0.5,'Feature Sheet'!A22," ")

So in sheet1 (Feature sheet) I am going down rows but staying in the same column and in sheet2 *Submission sheet) I am copying across columns but staying in the same row.

I would like a way to copy the formula from cell B10 on the Submission sheet to cell C10 on the submission sheet and have the formula read: =IF('Feature Sheet'!A20>0.5,'Feature Sheet'!A20," ") instead of reading =IF('Feature Sheet'!B20>0.5,'Feature Sheet'!B20," "). The normal drag and copy or copy paste changes the COLUMN not the ROW which I need.

Thanks,
BJW

 

[Glory]

Use $ before the thing you don't want to change.

 

[BJIII]

Tried that also I locked the column but it doesn't change the row number basically just copies the formula exactly.

example:
Cell B10: =IF('Feature Sheet'!$A19>0.5,'Feature Sheet'!$A19," ")
Cell C10: =IF('Feature Sheet'!$A19>0.5,'Feature Sheet'!$A19," ")
Cell D10: =IF('Feature Sheet'!$A19>0.5,'Feature Sheet'!$A19," ")
Cell E10: =IF('Feature Sheet'!$A19>0.5,'Feature Sheet'!$A19," ")

 

[gsistek]

Try using (in B10): =IF(OFFSET('Feature Sheet'!$A$19,COLUMN(B10),0)>0.5,OFFSET('Feature Sheet'!$A$19,COLUMN(B10),0)," ")

 

[Glory]

It shouldn't. $A19 should not preserve both, $A$19 should do that.

I just tried it. It looks like when you drag across instead of down, placing the single $ in front of the range preserves both the column and the row.

Can't help you, son. Sorry about that.

 

[gsistek]

Try using (in B10): =IF(OFFSET('Feature Sheet'!$A$19,COLUMN(B10),0)>0.5,OFFSET('Feature Sheet'!$A$19,COLUMN(B10),0)," ")

Let me correct myself: =IF(OFFSET('Feature Sheet'!$A$19,COLUMN(B10)-2,0)>0.5,OFFSET('Feature Sheet'!$A$19,COLUMN(B10)-2,0)," ")

 

[MrKowz]

try:

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=IF(<font color="Blue">OFFSET(<font color="Red">'Feature Sheet'!A19,COLUMNS(<font color="Green">$B$10:B10</font>),0</font>)>0.5,OFFSET(<font color="Red">'Feature Sheet'!A19,COLUMNS(<font color="Green">$B$10:B10</font>),0</font>)," "</font>)
</td></tr></tbody></table></td></tr></table><br />

then copy across.