• 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.

How to write a value in a particular Address?

Krishna20

Krishna20

New Member
Hi,

I would like to know how to goto a particular cell and write the value in that particular cell using Excel functions?

For example, if I have a number, say 45, that is generated in a random cell using RANDBETWEEN function, and I want to write this number in a particular cell, 4th row and 5th column.

I have done it using VBA, but I find it a bit challenging using Excel functions.

Thank you
 
Hi Krishna,

Not sure what you are doing, you cannot generate an amount in a random cell, the RANDBETWEEN formula must be in a cell for that value to appear on the sheet, so if the amount of the generated is in say cell B50 and you want the amount in A1, you can either enter in A1 =B50, or you can copy/paste value from B50 to A1.

cheers

kanti
 
Hi Kanti,

Let me give you how I want this.
I am using RANDBETWEEN function in cell "K10"
If I get a value, say 28, I want this value to be written in B8
I have done using IF, ADDRESS, ROUNDDOWN, MOD functions, but its very much cumbersome. Just want to know if there is any formula to do the above exercise.

Thank you

Krishna
 
Krishna
What is the relationship between the value and the address?
 
Hi, Krishna20!

As there's no Excel function to inverse / reverse address of a cell or something alike, you can't do that.

The only workaround maybe this:
- you place a =RANDBETWEEN(11;99) formula in J10 (row 10, column 10)
- you place this formula in all cells from A1:I9 (rows 1 thru 9, columns 1 thru 9):
=SI(Y(ENTERO($J$10/10)=FILA();RESIDUO($J$10;10)=COLUMNA());$J$10;"") -----> in english: =IF(AND(INT($J$10/10)=ROW(),MOD($J$10,10)=COLUMN()),$J$10,"")

Play with F9 to recalculate and see how the displayed cell changes.

Regards!
 
SirJB7
But that doesn't fit with his first example "For example, if I have a number, say 45, that is generated in a random cell using RANDBETWEEN function, and I want to write this number in a particular cell, 4th row and 5th column."
 
Well I have written the functions as follows

Cell M5=RANDBETWEEN(0,100)
M6 = ADDRESS(ROUNDDOWN(M5/10,0)+1,MOD(M5,10)+1)
(For say 29, M6 would be $J$3)
Then I have used in Cell A1:K10
=IF(CELL("address",A1)=$M$6,$M$5,"")

I could solve it but I would like to know if there is any way to retain a number once I have generated using the above set of functions. E.g. I have 29 filled in the J3, and when I refresh I get say 50, it would be generated in K5 but I can see what number I have previously generated
I would like to know the solution for this issue.

Thanks

Krishna

P.S. This is my second post, so please tell me how to attach an excel file?
 
@Hui
Hi!
Doesn't it? I think it does, check the last post from the OP. J3 "equals" to 29, plus 1 for row/col then 3-10.
However with the last post I'm not sure of much more than that equivalence, let us wait & see the uploaded file.
Regards!

Hi, Krishna20!
Use the button "Upload a file" located at bottom right of the reply text box.
Regards!
 
Hi, Krishna20!
It's the same implementation as in my example, with only an intermediate cell plus. So back to your original question: no, you can't; unless you mine all the area with the same formula.
Regards!
 
You must log in or register to reply here.
Back
Top