Duplicate "Drag" feature

[Randaylb]

I am currently working within a very large spreadsheet which incorporates the
following formula:

=CONCATENATE("",'Sheet 1'!BC5,"")

This populates data into a printable sheet for a multitude of employees.
There are 150 cells below this one that should display the next cell in
sequence:

=CONCATENATE("",'Sheet 1'!BC6,"")
=CONCATENATE("",'Sheet 1'!BC7,"")
=CONCATENATE("",'Sheet 1'!BC8,"")
....etc

The "BC" part of the fomula must be changed manually, so I've been using the
drag feature to do this, but I also have the cells shaded so that I have to
select "Fill without formatting" each time and it's getting to be a drag (pun
intended).

I tried to record a macro with no success. Any ideas on how to duplicate
the drag feature with a formula?

Alternative: Can I reference a cell in such a way that I can simply input
"BC" into the cell and the formulae will pull that value in as the reference?

EXAMPLE:
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]5,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]6,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]7,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]8,"")

 

[Luke M]

Curious, what are concatenating "" onto the data?
Anyway, let's say you place "BC" into cell A2.

One way:
=""&INDIRECT("'Sheet 1'!"&A2&ROW(A5))&""

or perhaps:
=TEXT(INDIRECT("'Sheet 1'!"&A2&ROW(A5)),"@")

Note that the ROW function will determine which row of data to pull, and
since it has a floating cell reference, it will change as you copy the cell
downward.

 

[Randaylb]

Luke,

That worked like a charm. Thank you.

--
R

Curious, what are concatenating "" onto the data?
Anyway, let's say you place "BC" into cell A2.

One way:
=""&INDIRECT("'Sheet 1'!"&A2&ROW(A5))&""

or perhaps:
=TEXT(INDIRECT("'Sheet 1'!"&A2&ROW(A5)),"@")

Note that the ROW function will determine which row of data to pull, and
since it has a floating cell reference, it will change as you copy the cell
downward.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I am currently working within a very large spreadsheet which incorporates the
following formula:

=CONCATENATE("",'Sheet 1'!BC5,"")

This populates data into a printable sheet for a multitude of employees.
There are 150 cells below this one that should display the next cell in
sequence:

=CONCATENATE("",'Sheet 1'!BC6,"")
=CONCATENATE("",'Sheet 1'!BC7,"")
=CONCATENATE("",'Sheet 1'!BC8,"")
...etc

The "BC" part of the fomula must be changed manually, so I've been using the
drag feature to do this, but I also have the cells shaded so that I have to
select "Fill without formatting" each time and it's getting to be a drag (pun
intended).

I tried to record a macro with no success. Any ideas on how to duplicate
the drag feature with a formula?

Alternative: Can I reference a cell in such a way that I can simply input
"BC" into the cell and the formulae will pull that value in as the reference?

EXAMPLE:
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]5,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]6,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]7,"")
=CONCATENATE("",'Sheet 1'![reference text "BC" in cell A1]8,"")