Dynamically enter value in a cell

[Piyushsha]

hi Lets say I have a value of 5 in B1 column and I want to set value of A"X" cell where X = 1 + cell(B,1).value so in this case I want to set value of A6 cell. If B1 has 7 then I need to set value for A8 cell.

Any help Much appreciated


thanks

 

[WarPigl3t]

I'm not a master but your answer lies with the INDIRECT function.

 

[johnmpl]

Hi!

With INDIRECT (like WarPigl3t said):
=INDIRECT("A" & 1+B1)

Blessings!

 

[Piyushsha]

thanks John but I am still confused.
A B C D
1 7
2
3
4
5
6
7
8
9
10
Now in the above example I want to set A8="SomeThing" A8 because A1 contains 7 , if A1 is 5 then I want to set A6="Something". Can you please explain how to use Indirect in this case

thanks

 

[mikerickson]

Excel formulas don't put values in cells, they read values from cells and return the calculated result.
To put values in cells, you need VBA.

Per the example in post #4, you have 1 in A1.

What do you want to happen when the user puts a 7 in B1?
Do you want the user to have to press a button or some other action to cause the result you want.

What do you want to happen when the user changes the 1 in A1 to 5?

 

[Piyushsha]

Mike-

Basically what I am looking for is lets say when user open this excel in the very first row 1 st col I will have some heading as "enter number of rows to insert" and lets say in second col User enter 5. then from row 2 (static) I will have some headers (think of it as table so column headers) and then from row 3 user can enter values for 5 records. So in the 8 row columns 1 I want to type some heading as all the values inserted. Its like if I want to capture data from user for multiple tables in one tab but I dont know how many values aka rows users will insert so at the beginning I am prompting user to enter number of values to insert and then based on this number I will add details of other tables

 

[Piyushsha]

And sorry my lat example is like this

A B C D
1 7
2
3
4
5
6
7
8
9
10
Now in the above example I want to set A8="SomeThing" A8 because A1 contains 7 , if A1 is 5 then I want to set A6="Something". Can you please explain how to use Indirect in this case

 

[Piyushsha]

**** formatting

Col-->A B C D

1 2(A1)
2 Name Id
3 XYZ 10
4 ABC 20
4 All Values Entered(A4 this cell value I need to set based on values from A1)
5

 

[Piyushsha]

Sorry new to forum so all rookie mistakes
[TABLE="width: 500"]
<tbody>[TR]
[TD]Enter Number of Vals For Table1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Id[/TD]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]10[/TD]
[TD]DLS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]20[/TD]
[TD]HOU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Enter number of values for Table2[/TD]
[TD]3

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now as you can see above is B1 user will enter any number which is 2 in this case so next line I put all table column headers and then next 2 lines they can insert data. After this in A4 I put some label , if user put 3 in B1 then I need to put this header in A5 and so on.

 

[mikerickson]

Native Excel formulas don't work like that.
It sounds like you want something like a user form rather than a worksheet.