Combine Values For Cell Name

Hi,
I have to combine two value and use them as a cell name,
So far this gives me the cell name =("A"&A3) where A3 has a value of 2 and the autcome for the code =("A"&A3) is A2 but I wanna use that as the cell name for my formula and not have it projected as a value only if you see what I mean.

Basicaly the reason for this is that when I write a formula in the cells and drug it down it moves 3 numbers at the time as the colomn H is split in 3 per row, so I have for example

=(B1)
=(B4)...

and so on, where I want it to be

=(B1)
=(B2)...

Therefore I am using the colom A to assign a row number, that the reason for the formula A"&A3

but when I try to write a formula it doesnt work as it doesnt use the outcome A2 as a cell name but as a text value only

Please help me...

Hi,
I was wondering why peoples are just reading my question with out trying to help me?

Thanks

Is this what you are trying to achieve:

In cell B4, enter this; IF(B4>1,"A"&VALUE(B4),"") and then copy it down as far as you need.

I wasn't entirely clear of your objective so I may have misunderstood what you're doing- reply if you need something else.

The formula you require is :
=INDIRECT(address)
which returns the value of the cell at the given address.

There is also the formula :
=ADDRESS(row,column,...)
Which can help you construct the address,
(but often it is just as easy to use the text funtions as you have).

Mark.

Re-reading your post and trying to interpret what you realy realy want,

The ROW() and COLUMN() functions might be useful.
ROW() gives you the row number of the cell
column() gives the column number of the cell.

for example If you are in CELL D6 then
=INDIRECT(ADDRESS(ROW()-3,COLUMN()+2))
will give you the value of the cell at F3.
The same formula in cell E7 will give you the value of the cell at G4.

does this help?

Mark.

Originally Posted by LondonVirus

Hi,
I was wondering why peoples are just reading my question with out trying to help me?

Thanks

Hello LondonVirus, I presume they either don't understand what you're trying to do or they understand but don't know how to achieve what you want. I think I know what you want to do. There are a few options, i.e.

=INDIRECT("A"&A3)

or (I prefer)

=INDEX(A:A,A3)

M aybe I didn't explain my self properly guys, I need to pick up the data from an other ****, now when I drag down to get the formula copied down I get:

=Sheet1!A2
=Sheet1!A5
=Sheet1!A8

I need it to copy down and say:

=Sheet1!A2
=Sheet1!A3
=Sheet1!A4
Even though each of the rows on the active **** are splitted in 3, bec ause on the other **** they are now and if I drag down the formula as it is I would be skipping 2 values at the time and collec t only once every 3 values...


Makes sence?


Say I have the names:

Mark
Luiggi
John
Paul

My coding now picks up

Mark
Paul...

Skipping 2 lines at the time...

By the ways thanks for trying to help me

Thanks Mark@Work , I will try that now, any other ideas are welcome

Try this formula in B4 copied down

=IF(MOD(ROW()-ROW(B$3)+1,3)<>2,"",INDEX(Sheet1!A:A,INT((ROW()-ROW(B$3)+5)/3)))

Originally Posted by LondonVirus

Maybe I didn't explain my self properly guys, I need to pick up the data from an other ****...

It's a dirty job, but someone's got to do it!