Named range in a different sheet

[Pereira]

Hi everyone!
I want to create a range (A1, B1, C1, D1, ...), containing X cells in
sheet1. I named it "Range".
I want to paste the value of those range to another X cells in sheet2.
I selected the X cells in sheet 2 and call it "Range2". I said Range2
is =Sheet1!Range.
And it appears an error #VALUE!
What am I doing wrong?
These are the only named ranges I have. I know I can do it
individually, but I want to do it all together...

Thanks in advance,
Bruno Pereira

 

[Bob Phillips]

Try

=Range

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pereira]

That doesn't work...it gives the same error...

Bob Phillips escreveu:

 

[Dave Peterson]

Say you have a named range that is A1:J1 (10 cells in a row) and you've named it
Range (workbook level name).

Then select the range you want to populate (say E5:N5 of Sheet2).

Type this in E5:
=INDEX(Range,COLUMN()-COLUMN($E$5)+1)
and hit ctrl-shift-enter to fill all of E5:N5

Or just put that formula in E5 (don't hit ctrl-shift-enter) and drag across to
N5.

 

[Pereira]

I'm sorry, but when I do that, it gives me an error on the formula

=INDEX(Range,COLUMN()-COLUMN($E$5)+1)

more exactly in "Range,COLUMN"

What can that be?I tried to solve it but I couldn't...

Thanks.

Dave Peterson escreveu:

 

[Dave Peterson]

I selected A1:J1 in a sheet.
I used Insert|Name|Define to name it Range.
(Just like you did???)

But my guess is that there is a difference between languages/settings.

I use excel with USA English and the comma as the list separator. I'm guessing
that you don't.

If that's true, try this.

Select A1:J1 on Sheet1
Insert Name|Define
Call it Range

Then insert a new worksheet (sheet2, say)
Select an empty cell.
Hit alt-f11 (to get to the VBE -- where macros live)
hit ctrl-g (to see the immediate window.

Type this and hit enter.
activecell.formula = "=INDEX(Range,COLUMN()-COLUMN($E$5)+1)"

Then hit alt-f11 to go back to excel.

Look at that formula. Excel will have translated any functions and used the
correct separator for your settings.

 

[Pereira]

This is great!!!
You explained every detail, thank you very much!!
Pereira


Dave Peterson escreveu: