• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Writing output of cells to an empty table cell

J

Jan Renaud

New Member
Hello all,


I have a table with 10 row headings (person names) and 5 column headings (course names)

All cells in the table are initially blank.


1. Outside the table I use a cell with a formula generating a row number.

2. In other cell I use a formula generating a column number.

3. In a third cell I have formula that generates a value.

I want to know if there is a way - preferably without VBA else with VBA - that does the following:


Go to Row (value calculated in 1.) in the (empty) table

Go to Column (value calculated in 2.)in the (empty) table

Write or paste in the cell at the intersection of the table row and column the value calculated in 3.


This table value must remain there: when I repeat the operation leading to another row number, column number and value, it must not lead to a deletion or change of the table cell value obtained at the first operation.

So after repeating the operation 50 times all table cells are filled with data.


Looking forward to your suggestion.


Jan
 
Hi Jan,


Welcome to the forum..


As a Welcome Kit.. just few suggestion..

Don't hesitate to search for any query in the top right GOOGLE boxbefore posting..

and try to elaborate more briefly regarding your query.. (what you have already done).. :)


Now when you want to change table's cell..

* When # 1 changes (ROW)

* When # 2 Changes (COL)

or * When the Value..


If you have formula to generate ROW & COL, why don't you adapt the same in table also...

as lots of reference formula's are in excel..


Can you please show us the Formula to set ROW / COLUMN / VALUE.. or sample sheet is better..


To upload sample sheet.. please visit. below.

http://chandoo.org/forums/topic/posting-a-sample-workbook


PS: oohhh BTW.. for the time being play with attached file..

as via Formula.. I don't hope it is possible..


http://dl.dropbox.com/u/78831150/Excel/Writing%20output%20of%20cells%20to%20an%20empty%20table%20cell%20(Jan%20Renaud).xlsm


Regards,

Deb

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$D$10"
[dr].Offset([d9].Value, [d10].Value).Cells(1).Value = [d11].Value
End Select
End Sub
[/pre]

where [dr] is NamedRange (!0 x 5)

D9,D10,D11 are Row,Column & Value Respectively,.
 
Hi Jan ,


To add to what Deb has posted , if you want a formula-based solution , you have to accept two things :


1. If your calculation is Automatic , then you will have problems. Even when you enable Iterative Calculation , to avoid the problem of Circular Referencing , you cannot ensure that the row number and column number change simultaneously ; the moment one of these changes , the concerned cell will immediately get its value !


2. If your calculation is Manual ( you can select the Automatic except for Data Tables option ) , then you will have to remember to press F9 at every stage , since if you miss a stage , the concerned cell will not get its intended value.


Let us assune your table is B2:N20. Let us assume your row number will be in A1 , column number in A2 , and the cell value in A3.


In the first cell of your table , B3 , put in the following formula , and copy it across :


=IF(AND(B3<>"",B3<>0),B3,IF(ROW(B3)=$A$1,IF(COLUMN(B3)=$A$2,$A$3,""),""))


Now , if your calculation is not Automatic , then each time you change A1 through A3 , and only thereafter press F9 , the intended cell will get its value.


Narayan
 
You must log in or register to reply here.
Back
Top