I have an estimating program file with 30+ worksheets in which I need to be
able to specify a specific range name from four available, then paste the
data from a 5 column by 6 row range relating to that named range to a
different worksheet to calculate overheads, field supervision, AFDC and
profit using the specified range name (the four range names have different
values). I think an 'IF' statement would work but I do not know how to tell
it to choose the range and then paste it to the specific area on the 1st
worksheet and be able to overwrite that data if another range name is used.
I originally created this program in Lotus 1-2-3 using a macro.
--
Lori

Perhaps something like this ..

In the target sheet,

Assuming B2 will house the input of the range name,

Put in say, the formula bar for B4:
=IF($B$2="","",OFFSET(INDIRECT($B$2),ROW(A1)-1,COLUMN(A1)-1))

Array-enter the formula in B4 by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Copy B4 across to F4, fill down to F9
to populate the range size (5C x 6R)

B4:F9 will return the required results* depending on the input in B2
*the contents of the named range

For a neater look, suppress display of extraneous zeros in the sheet
via clicking Tools Options View tab Uncheck "Zero values" OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lori" wrote:
I have an estimating program file with 30+ worksheets in which I need to be
able to specify a specific range name from four available, then paste the
data from a 5 column by 6 row range relating to that named range to a
different worksheet to calculate overheads, field supervision, AFDC and
profit using the specified range name (the four range names have different
values). I think an 'IF' statement would work but I do not know how to tell
it to choose the range and then paste it to the specific area on the 1st
worksheet and be able to overwrite that data if another range name is used.
I originally created this program in Lotus 1-2-3 using a macro.
--
Lori

Tried your answer and got a partial response. Here is the data that should
appear:

55.0% 55.0% 55.0%
30.0% N/A 30.0%
8.0% N/A N/A
N/A
N/A

The data that prints out is 55% in all cells. What could I be doing wrong?

--
Lori


"Max" wrote:

Perhaps something like this ..

In the target sheet,

Assuming B2 will house the input of the range name,

Put in say, the formula bar for B4:
=IF($B$2="","",OFFSET(INDIRECT($B$2),ROW(A1)-1,COLUMN(A1)-1))

Array-enter the formula in B4 by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Copy B4 across to F4, fill down to F9
to populate the range size (5C x 6R)

B4:F9 will return the required results* depending on the input in B2
*the contents of the named range

For a neater look, suppress display of extraneous zeros in the sheet
via clicking Tools Options View tab Uncheck "Zero values" OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lori" wrote:
I have an estimating program file with 30+ worksheets in which I need to be
able to specify a specific range name from four available, then paste the
data from a 5 column by 6 row range relating to that named range to a
different worksheet to calculate overheads, field supervision, AFDC and
profit using the specified range name (the four range names have different
values). I think an 'IF' statement would work but I do not know how to tell
it to choose the range and then paste it to the specific area on the 1st
worksheet and be able to overwrite that data if another range name is used.
I originally created this program in Lotus 1-2-3 using a macro.
--
Lori

The data that prints out is 55% in all cells...

Is the calc mode accidentally set to Manual?
Check Tools Options Calculation tab

Pl post the actual formula you are using over there
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lori" wrote in message
...
Tried your answer and got a partial response. Here is the data that
should
appear:

55.0% 55.0% 55.0%
30.0% N/A 30.0%
8.0% N/A N/A
N/A
N/A

The data that prints out is 55% in all cells. What could I be doing wrong?

--
Lori

The calc mode is set to automatic.
formula: (cell C16 is where BPOH choice is entered)
=IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1))

--
Lori


"Max" wrote:

The data that prints out is 55% in all cells...

Is the calc mode accidentally set to Manual?
Check Tools Options Calculation tab

Pl post the actual formula you are using over there
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lori" wrote in message
...
Tried your answer and got a partial response. Here is the data that
should
appear:

55.0% 55.0% 55.0%
30.0% N/A 30.0%
8.0% N/A N/A
N/A
N/A

The data that prints out is 55% in all cells. What could I be doing wrong?

--
Lori

=IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1))

The above wasn't consistent with what was suggested earlier

=IF($C$16="BPOH",BPOH, ...
should read as:

=IF($C$16="","", ...

Try this working sample file which illustrates the earlier suggestion:
http://www.savefile.com/files/387065
Display specified name range.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Lori wrote:
The calc mode is set to automatic.
formula: (cell C16 is where BPOH choice is entered)
=IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1))

--
Lori