Help : moving data to designated cell address

  • Thread starter Thread starter Tom Ogilvy
  • Start date Start date
T

Tom Ogilvy

Formulas work from the destination.

in the destination cells you might use something like

=sum((Sheet1!$B$1:$B$200=Column())*(Sheet1!$C1:$C200)=row())*Sheet1!$A$1:$A$
200)
Entered with Ctrl+shift+enter

or
=sumproduct(--(Sheet1!$B$1:$B$200=Column()),--(Sheet1!$C1:$C200)=row()),Shee
t1!$A$1:$A$200)

entered normally.

This would put zeros in cells where there is not match, but that may not be
an issue.
 
Thank you so much for your help, Mr. Ogilvy.
Since I am a newbie in this, I've got another question.

In my case, then, could I select columns C (y_axis) and D(x_axis) as the
column and row positions, and target value column B(Area)?
Would the following be alright?

=sum((Sheet1!$c$1:$c$200=Column())*(Sheet1!$d1:$d200)=row())*Sheet1!$b$1:$b$
200

Thank you again,

Sunny Yang
 
no, you would put the formula in the cells which you show in your column New
Cell Address.

So the formula assumes the table you provided is on Sheet1. On the
destination sheet, you would select R2C20 and enter the formula, then select
R3C5 and enter the formula, and so forth.

I would expect the new table to be filled in, so you could enter the formula
in the upper left cell and fill down and right.
 
Sunny,

Interestingly your second post has the correct time, while your first post
was "post-dated" to the 5th of January to ensure that your post remained at
the top.

If you look at the forum, most reasonable posts are answered quickly.
People here will attempt to answer all reasonable questions.

I hope in the future, you are not so rude as to place your post on top by
using post-dated messages. Imagine if everyone engaged in such behavior?
You might even find you get more response, more quickly if you simply post
your message in a normal manner.

Regards,
Kevin
 
Kevin,
This is also one of my pet peeves. I didn't notice this one. If I had I
simply would have deleted it.
I will now do so.
 
Don,

What do you delete it from?


Don Guillett said:
Kevin,
This is also one of my pet peeves. I didn't notice this one. If I had I
simply would have deleted it.
I will now do so.

--
Don Guillett
SalesAid Software
(e-mail address removed)

=sum((Sheet1!$c$1:$c$200=Column())*(Sheet1!$d1:$d200)=row())*Sheet1!$b$1:$b$
=sum((Sheet1!$B$1:$B$200=Column())*(Sheet1!$C1:$C200)=row())*Sheet1!$A$1:$A$
=sumproduct(--(Sheet1!$B$1:$B$200=Column()),--(Sheet1!$C1:$C200)=row()),Shee
Click to expand...
 
Dear all,

I am trying to put the values of the cells in the column "Area", shown
below, into the
corresponding cell address in another new worksheet.
For example, I'd like to put/relocate the value '290' to the cell r2c20 in a
new worksheet.
( I wish I don't have to copy/paste '290' to the cell r2c20.)
Likewise, '241' to the cell r3c5,and so on, until the data are consumed.

I guess there should be some ready-made worksheet functions in the Excel.
I'll appreciate it if you could give me some suggestions or comments.

Sunny Yang
==================== example ================
Count Area y_axis x_axis new cell address
1 290 2 20 R2C20
2 241 3 5 R3C5
3 89 2 14 R2C14
4 290 3 22 R3C22
5 513 3 17 R3C17
6 ..... ....
 
Back
Top