Indirect addressing?

[David Morrison]

I know it is possible to use indirect addressing to use values in cells
as a location to get something from.

So if cell A1 contains a cell address (eg, b6), I can use the expression

=INDIRECT(A1)

to refer to the value in cell B6.

Is it possible to use an address in a cell to place a value in the cell
it is referring to?

So in the example above, I would like to place a value in the cell
referred to by the value in A1.

I suspect that this is totally contrary to the concept of spreadsheets,
but Excel has so many other unusual features that I thought I would ask.

This is Excel 2004.

Thanks

David

 

[Carl Witthoft]

I know it is possible to use indirect addressing to use values in cells
as a location to get something from.

So if cell A1 contains a cell address (eg, b6), I can use the expression

=INDIRECT(A1)

to refer to the value in cell B6.

Is it possible to use an address in a cell to place a value in the cell
it is referring to?

So in the example above, I would like to place a value in the cell
referred to by the value in A1.

I suspect that this is totally contrary to the concept of spreadsheets,
but Excel has so many other unusual features that I thought I would ask.

This is Excel 2004.

Thanks

David

No, you can't create any formula that will write to the contents of
another cell. (Except of course via macros.... which leads to the
usual grousing about Office2008 :-( )

But you can write a formula in cell B6 that sets its value based on the
contents of cell A1, so maybe if you sort of 'reverse' your thinking you
can get your final desired result. Can you post a small example of what
your final product should do / look like?

 

[JE McGimpsey]

No, you can't create any formula that will write to the contents of
another cell. (Except of course via macros.... which leads to the
usual grousing about Office2008 :-( )

But you can write a formula in cell B6 that sets its value based on the
contents of cell A1, so maybe if you sort of 'reverse' your thinking you
can get your final desired result. Can you post a small example of what
your final product should do / look like?

It's rather a good thing that one *can't* do that - what would happen
here:

A1: B6
A2: =PUT_INDIRECT(1, $A$1)
A2: =PUT_INDIRECT(2, $A$1)

i.e., if two cells tried to write to a cell, which one wins?

However, if the value in a cel should be 0 or blank unless A1 referred
to it, one might be able to use something like:


B6: =IF($A$1=ADDRESS(ROW(),COLUMN(), 4), $A$2, "")

Which would put the value in A2 in B6 if A1="B6", but leave B6 blank
otherwise.

 

[David Morrison]

No, you can't create any formula that will write to the contents of
another cell. (Except of course via macros.... which leads to the
usual grousing about Office2008 :-( )

But you can write a formula in cell B6 that sets its value based on the
contents of cell A1, so maybe if you sort of 'reverse' your thinking you
can get your final desired result. Can you post a small example of what
your final product should do / look like?

It's just an idea I have at the moment for creating a sort of map. I've
tried using the Chart capabilities of Excel, but they don't really meet
my needs as far as I can see.

Say I have a set of row and column references, together with a name and
possibly other information for each one. (There is only a single entry
for each cell, to answer the other reply.)

I want to see these things laid out on a grid, with the name associated
with each data point.

I was thinking of a spreadsheet as a clumsy but functional way to do
this. But I cannot see how given the row and column references, to get
the name to appear in the appropriate cell.

I did try thinking in reverse, but felt that the formula in each cell
would be horrendous. Maybe there is some function that I am not aware of
that would do it.

It would need to be something like:

if a value in the row column of the input data was the same as the row
this cell is in, AND the corresponding column value was the same as the
column this cell is in, then set the cell to the label.

If you have any ideas...

Cheers

David

 

[Shane Devenshire]

Hi,

Why don't you show us a dummy sample of data and also how you would like to
see it. I suspect we may be able to come up with something involving MATCH
and INDEX or some such.

 

[David Morrison]

Hi,

Why don't you show us a dummy sample of data and also how you would like to
see it. I suspect we may be able to come up with something involving MATCH
and INDEX or some such.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Ok, data in three columns like this:
Label,Row,Column
cat,3,13 Value "cat" is to go in cell c13
dog,5,17 Value "dog" is to go in cell e17
horse,18,3 Value "horse" is to go in cell r3

Now each cell in the "map" would have essentially the same formula.

So the formula needs to scan this list to see if the row and column
values correspond to the row and column of each cell containing the
formula. If there is one, the formula returns the corresponding label
field.

Is that enough of a description?

Thanks

David

 

[JE McGimpsey]

Ok, data in three columns like this:
Label,Row,Column
cat,3,13 Value "cat" is to go in cell c13
dog,5,17 Value "dog" is to go in cell e17
horse,18,3 Value "horse" is to go in cell r3

Now each cell in the "map" would have essentially the same formula.

So the formula needs to scan this list to see if the row and column
values correspond to the row and column of each cell containing the
formula. If there is one, the formula returns the corresponding label
field.

Is that enough of a description?

One way:

Assume the list is in the three columns AA:AC

Then in C3, array enter (CMD-RETURN):

=IF(ISNUMBER(MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3, FALSE)),
INDEX($AA$1:$AA$3, MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3,
FALSE)),"")

If you array-enter correctly, brackets - { ... } - will appear around
the formula.

Copy C3 right to R3, then copy C3:R3 down to row 17.

Adjust your ranges as needed.

 

[David Morrison]

=IF(ISNUMBER(MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3, FALSE)),
INDEX($AA$1:$AA$3, MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3,
FALSE)),"")

Yes! Thank you.

David

 

[David Morrison]

One way:

Assume the list is in the three columns AA:AC

Then in C3, array enter (CMD-RETURN):

=IF(ISNUMBER(MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3, FALSE)),
INDEX($AA$1:$AA$3, MATCH(COLUMN()&ROW(), $AB$1:$AB$3&$AC$1:$AC$3,
FALSE)),"")

If you array-enter correctly, brackets - { ... } - will appear around
the formula.

Copy C3 right to R3, then copy C3:R3 down to row 17.

Adjust your ranges as needed.

Urk! Spoke too soon.

I put my data above into AA:AC beginning at row 2.

I modified the formula for data to be in rows 2-100:

=IF(ISNUMBER(MATCH(COLUMN()&ROW(), $AB$2:$AB$100&$AC$2:$AC$100, FALSE)),
INDEX($AA$2:$AA$100, MATCH(COLUMN()&ROW(), $AB$2:$AB$100&$AC$2:$AC$100,
FALSE)),"")

I put this in all cells A1:S90, command/return to make it an array
formula.

I get cat in C13, dog in E17 and horse in r3. All good!

But I also get horse in A83?????????

Cannot see why that should happen.....

David

 

[JE McGimpsey]

Urk! Spoke too soon.

I put my data above into AA:AC beginning at row 2.

I modified the formula for data to be in rows 2-100:

=IF(ISNUMBER(MATCH(COLUMN()&ROW(), $AB$2:$AB$100&$AC$2:$AC$100, FALSE)),
INDEX($AA$2:$AA$100, MATCH(COLUMN()&ROW(), $AB$2:$AB$100&$AC$2:$AC$100,
FALSE)),"")

I put this in all cells A1:S90, command/return to make it an array
formula.

I get cat in C13, dog in E17 and horse in r3. All good!

But I also get horse in A83?????????

Cannot see why that should happen.....

Well, it happens because

R3: COLUMN()&ROW() ==> (18) & (3) ==> 183
A83: COLUMN()&ROW() ==> (1) & (83) ==> 183

which I might have anticipated if Id thought about the n

You *could* extend that formula:

=IF(ISNUMBER(MATCH(TEXT(COLUMN(),"0000") & TEXT(ROW(), "000000"),
TEXT($AB$2:$AB$100, "0000") & TEXT($AC$2:$AC$100, "000000"), FALSE)),
INDEX($AA$2:$AA$100, MATCH(TEXT(COLUMN(), "0000") & TEXT(ROW(),
"000000"), TEXT($AB$2:$AB$100, "0000") & TEXT($AC$2:$AC$100, "000000"),
FALSE)),"")

 

[David Morrison]

Well, it happens because

R3: COLUMN()&ROW() ==> (18) & (3) ==> 183
A83: COLUMN()&ROW() ==> (1) & (83) ==> 183

which I might have anticipated if Id thought about the n

You *could* extend that formula:

=IF(ISNUMBER(MATCH(TEXT(COLUMN(),"0000") & TEXT(ROW(), "000000"),
TEXT($AB$2:$AB$100, "0000") & TEXT($AC$2:$AC$100, "000000"), FALSE)),
INDEX($AA$2:$AA$100, MATCH(TEXT(COLUMN(), "0000") & TEXT(ROW(),
"000000"), TEXT($AB$2:$AB$100, "0000") & TEXT($AC$2:$AC$100, "000000"),
FALSE)),"")

Ok, I have just worked out what your formula does. How about just
concatenating a character between each row/column pair, ie,

=IF(ISNUMBER(MATCH(COLUMN()&"-"&ROW(), $AB$2:$AB$100&"-"&$AC$2:$AC$100,
FALSE)), INDEX($AA$2:$AA$100, MATCH(COLUMN()&"-"&ROW(),
$AB$2:$AB$100&"-"&$AC$2:$AC$100, FALSE)),"")

This seems to work, but there maybe issues I do not foresee.

Thanks for your help.

 

[Bob Greenblatt]

I know it is possible to use indirect addressing to use values in cells
as a location to get something from.

So if cell A1 contains a cell address (eg, b6), I can use the expression

=INDIRECT(A1)

to refer to the value in cell B6.

Is it possible to use an address in a cell to place a value in the cell
it is referring to?

So in the example above, I would like to place a value in the cell
referred to by the value in A1.

I suspect that this is totally contrary to the concept of spreadsheets,
but Excel has so many other unusual features that I thought I would ask.

This is Excel 2004.

Thanks

David

No, that is not possible. A formula in a cell can only change the value of
the cell it is in. You will need AppleScript or a macro to do what you are
asking.