This is great!!!
You explained every detail, thank you very much!!
Pereira
Dave Peterson escreveu:
> 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 wrote:
> >
> > 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:
> > > 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 wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson
|
Similar Threads
