B
Brian Smeenk
Hi all, I consider myself fluent in Excel, but I've developed a situation
that has stumped me. Any help would be much appreciated.
I might be able to solve this issue if somebody could show me how to add a
number to a column. For example, if I want Excel to pull data from Column
D, how can I get Excel to realize that column D is really the same thing as
Column A + 3? I know you can use the column() command to get the numerical
value for a column, but is there a way to have it do that in reverse, such
that you could tell it the column number is 4 and it would know that you are
referring to column D?
The specifics of my situation are as follows, although it may sound a bit
complex. Sheet 1 of my workbook is a data entry sheet, where you enter the
specifics for various real estate properties. Column A is the header
column, and columns B thru D are merged into one column for property #1 for
reasons that I require. Columns E thru G are then merged in the same way
for property #2, and so on. So each property requires 3 adjacent columns.
You enter the property data as you move down the column.
Now, on Sheet 2, I start analysing these properties with formulas that refer
back to Sheet 1, only on Sheet 2, my properties are listed in rows, and not
columns. The issue that I am having is that when I enter a formula in, say
cell A7 of Sheet 2, which refers to cell B3 of Sheet 1 (which is really 3
cells merged into 1), how do I copy that formula down to property #2 in cell
A8 of Sheet 2? Excel is going to copy the formula so that now it refers to
cell B4 of Sheet 1, since it will move in the same direction that you are
copying the formula, which in this case would be downward. What I need it
to do is to actually pull data from cell E3 of Sheet 1, being that cells B-D
are used for property #1, and the data is arranged in columns and not rows.
I know it sounds complicated so I've attached screen shots for my Input
page, and then one for Sheet 2, which is an analysis. Properties are
entered into Sheet 1 in columns, and the rows of Sheet 2 are populated from
the columns in Sheet 1.
Many thanks in advance if anybody can help me figure this one out.
Brian Smeenk
that has stumped me. Any help would be much appreciated.
I might be able to solve this issue if somebody could show me how to add a
number to a column. For example, if I want Excel to pull data from Column
D, how can I get Excel to realize that column D is really the same thing as
Column A + 3? I know you can use the column() command to get the numerical
value for a column, but is there a way to have it do that in reverse, such
that you could tell it the column number is 4 and it would know that you are
referring to column D?
The specifics of my situation are as follows, although it may sound a bit
complex. Sheet 1 of my workbook is a data entry sheet, where you enter the
specifics for various real estate properties. Column A is the header
column, and columns B thru D are merged into one column for property #1 for
reasons that I require. Columns E thru G are then merged in the same way
for property #2, and so on. So each property requires 3 adjacent columns.
You enter the property data as you move down the column.
Now, on Sheet 2, I start analysing these properties with formulas that refer
back to Sheet 1, only on Sheet 2, my properties are listed in rows, and not
columns. The issue that I am having is that when I enter a formula in, say
cell A7 of Sheet 2, which refers to cell B3 of Sheet 1 (which is really 3
cells merged into 1), how do I copy that formula down to property #2 in cell
A8 of Sheet 2? Excel is going to copy the formula so that now it refers to
cell B4 of Sheet 1, since it will move in the same direction that you are
copying the formula, which in this case would be downward. What I need it
to do is to actually pull data from cell E3 of Sheet 1, being that cells B-D
are used for property #1, and the data is arranged in columns and not rows.
I know it sounds complicated so I've attached screen shots for my Input
page, and then one for Sheet 2, which is an analysis. Properties are
entered into Sheet 1 in columns, and the rows of Sheet 2 are populated from
the columns in Sheet 1.
Many thanks in advance if anybody can help me figure this one out.
Brian Smeenk