Probem referencing a cell

gwizrus

New Member
Joined
Sep 26, 2006
Messages
12
I have a title row and 4 rows of data in 6 columns. The key field I’m focusing on is in A2. The value in A2 is manually typed into the cell. Column 2 is hidden and is only used for finding data in another worksheet based on the data in A2 and is done for each row of data. Also, each row in column 2 must reference the value only in A2 by using $A$2.

Certain times, I must take the 6 columns and copy them over to the side leaving one column blank just for formatting. I enter the new data in H2. My problem is I have to unhide column 2 and change the $A$2 to the new value (which would be $H$2 in our example) and apply it to each row in that column.

How can I make it easier without having to update the hidden row with new location after coping the data over?

Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Since you have the formula involving cell A2 only in column B, you do not have to use the absolute $A$2. Instead, use A$2. That will allow the proper H$2 to appear automatically in your example.

Give it a try, and let us know if that solved your problem.
 

gwizrus

New Member
Joined
Sep 26, 2006
Messages
12
RalphA,

It worked. Thanks a bunch. Can't believe I never tried that. It makes sense now.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
That's great! Sometimes the obvious escapes us, util someone else points it out to us. It happens frequently to me.
 

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top