Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 204
Default References that move down as formula moves across - and vice versa?

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default References that move down as formula moves across - and vice versa

Do you mean the below demonstration..

Col A Col B Col C Col D Col E
1 = = = =
2
3
4
5
6
7

Try the formula in place of = in cell B1 and copy to right as required which
will copy the values from A1,A2, A3 etc;

=INDIRECT("A" & COLUMN(B1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Michelle" wrote:

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

  #3   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default References that move down as formula moves across - and vice versa

Can use INDIRECT

=INDIRECT("R"&COLUMN(A1)&"C"&ROW(A1),FALSE)

To explain:
This formula is creating a cell reference using R1C1 style referencing (the
false at end).

By referencing COLUMN in the R spot, you cause formula to move vertically
when you copy horizontally. Conversely, the ROW function in the C spot causes
formula to move horizontally when you copy vertically.

The formula I gave then will work in both directions (although I admit, I
would find this very confusing to work with on a large scale)
--
Best Regards,

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


"Michelle" wrote:

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default References that move down as formula moves across - and vice versa?

Probably the easiest to explain to novices might be the Index() function.

The range you're copying *from* is the range you enter first.

Column:
=Index($A1:$A100
=Index($K20:$K50

Row:
=Index(A$1:Z$1
=index(J$41:AM$41

The second part of the formula will *automatically* increment as it's copied
*across*:
=Index($A1:$A100,Columns($A:A))
=index($K20:$K50,Columns($A:A))

OR, for copying *down*:

=Index(A$1:Z$1,Rows($1:1))
=Index(J$41:AM$41,Rows($1:1))

You see here, that the second part *never* changes, dependent on copying
either across or down.

Also, these formulas can be entered *anywhere* on the sheet, without having
to be changed in any way.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Michelle" wrote in message
...
Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if
i move it RIGHT the references move DOWN (could the same function work in
both directions)

I am interested in the best worksheet.function way of doing it, but
also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hexadecimal to Decimal (and vice versa) Toria Excel Worksheet Functions 5 January 22nd 09 10:43 PM
FirstName, LastName and vice versa Bob Excel Worksheet Functions 3 October 27th 06 06:54 PM
From pricelist to productslist and vice versa... Herman56 Excel Discussion (Misc queries) 0 March 29th 06 05:40 PM
How do I move excel address lists to outlook. and vice versa. Lost in Office Excel Discussion (Misc queries) 1 April 9th 05 04:35 PM
Formula to convert/transpose columns to rows (and vice versa) markx Excel Worksheet Functions 5 March 10th 05 03:18 PM


All times are GMT +1. The time now is 12:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"