Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right
Thank you everyone. This did the trick:
In B1
=INDIRECT("A"&COLUMN(A1))
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right
Hi!
If you have more than one column of data in source file I think it can
be useful to use R1C1 reference style:
=INDIRECT("R"&COLUMN()&"C"&ROW(),0)
Regards,
J.
On 6 Gru, 17:53, roberto mensa wrote:
> In B1
> =INDIRECT("A"&COLUMN(A1))
>
> regards
> r
>
> 2010/12/6 0 1
>
>
>
>
>
>
>
> > The source document (sample.xls) contains these data:
>
> > A
> > 1
> > 2
> > 3
> > 4
>
> > In my destination workbook, I would like to put a reference in A1 so
> > when I copy the formula into B1, C1, etc., I get this:
>
> > A 1 2 3 etc.
>
> > Instead I get:
>
> > A 0 0 0 etc.
>
> > I've tried every variation of =sample.xls!$A$1 and none of them do it.
> > Is there another way? This is only needed one time but I'm trying to
> > avoid having to manually edit the formula across 20 columns, which is
> > how many the formula needs to be copied into.
>
> > --
>
> > ---
> > ---
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right
In B1
=INDIRECT("A"&COLUMN(A1))
regards
r
2010/12/6 0 1
> The source document (sample.xls) contains these data:
>
> A
> 1
> 2
> 3
> 4
>
> In my destination workbook, I would like to put a reference in A1 so
> when I copy the formula into B1, C1, etc., I get this:
>
> A 1 2 3 etc.
>
> Instead I get:
>
> A 0 0 0 etc.
>
> I've tried every variation of =sample.xls!$A$1 and none of them do it.
> Is there another way? This is only needed one time but I'm trying to
> avoid having to manually edit the formula across 20 columns, which is
> how many the formula needs to be copied into.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Copy an absolute column and relative row to the right
Let's take a look at this for a minute.
In Column A you have:
1 Heading
2 10
3 20
4 30
5 40
Now, in row 1, column 2 (Cell B1)
You want the contents of Cell A2
C1 = A3
Now, if we were to describe the RELATIONSHIP between these cells,
we could say that the COLUMN number of cell B1 is equal to the ROW number of
column A..
So... that's the same as saying:
B1 = "A" & column()
=column() returns the column NUMBER of the cell that has this formula.
Now, if you were to put that formula in B1
="A" & Column()
you would get a STRING "A2"
But what you WANT is for Excel to interpret the result of ="A" & Column() as
a cell reference, not a text string.
Now, Excel has a function that does this. It's called INDIRECT()
So, if in B1 you put:
=INDIRECT("A"&COLUMN())
It will return the value in A2.
Copy this formula across, and it will replace Column() with the current column.
See if it does what you're looking for.
At least it'll give you something to play with...
--
BTW: If you're wanting to copy the FORMULAS from Column A to row 1,
then you can simply hightlight the column A cells, hit ctrl-c,
select B1, right-click, select Paste Special, then select Transpose.
If you want the VALUES, then on the Paste Special panel, select Values as well
as Transpose.
Hope this helps..
Paul
- Original Message
> From: 0 1
> To: MS EXCEL AND VBA MACROS
> Sent: Mon, December 6, 2010 10:55:48 AM
> Subject: $$Excel-Macros$$ Copy an absolute column and relative row to the
right
>
> The source document (sample.xls) contains these data:
>
> A
> 1
> 2
> 3
> 4
>
> In my destination workbook, I would like to put a reference in A1 so
> when I copy the formula into B1, C1, etc., I get this:
>
> A 1 2 3 etc.
>
> Instead I get:
>
> A 0 0 0 etc.
>
> I've tried every variation of =sample.xls!$A$1 and none of them do it.
> Is there another way? This is only needed one time but I'm trying to
> avoid having to manually edit the formula across 20 columns, which is
> how many the formula needs to be copied into.
>
> --
>--
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
