Having a brain freeze in excel...

BJIII

New Member
Joined
Nov 8, 2010
Messages
26
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Hover over the lower-right-hand corner of the cell and drag across your range.
 

BJIII

New Member
Joined
Nov 8, 2010
Messages
26
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

Well-known Member
Joined
Mar 16, 2011
Messages
640

ADVERTISEMENT

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

BJIII

New Member
Joined
Nov 8, 2010
Messages
26
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

Well-known Member
Joined
Apr 4, 2011
Messages
660

ADVERTISEMENT

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

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
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

Well-known Member
Joined
Apr 4, 2011
Messages
660
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top