How to get Row, Column from a reference within a cell.

[showgun3]

Hi I have several sheets that I gets data from for sales and then copy them through referencing to other sheets. I would like to use offset functions to get the remainder of the data once I copy the first column automatically.

Usually I would do =+'Sheet1'!A1, from here on onwards I would like to use offset function or any other function like Address or Indirect functions to get the sheet to auto update all data in cells C3, D3, and E3. What I cant figure out is how to get the relevant sheet name, row and column numbers from A3. If there is a way through <acronym title="visual basic for applications">VBA</acronym> or just a inbuilt function, help me please.

Example below:

	A	B	C	D	E
1	10	20	30	40	50
2
3	=Sheet1!A1	=+B1
4
5

<tbody>
</tbody>

The result in A3 will be 10 and B3 will be 20, now I want a function which can analyze A3 or B3 and autofill C3, D3 and E3 with 30, 40 and 50 respectively.

 

[antthorne]

Hi showgun3

I'm sure someone will come up with a better way of doing this but this is how I would do it.

Create a new function within Excel Vba as per below

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

Input your first sum as per above in Cell A3

Below (Cell A4) this put the following code

=Getformula(a3")

In Cell A5 put

=mid(a4,2,find("!",A7,1)-1)

In Cell B6 put

=cell("address",b1)

Then copy this across to all your cells

In Cell B5 place

=indirect(a5&b6)

Really long winded!!

Thanks

Anthony

 

[showgun3]

Thanks, I will try it. I have been trying to solve this for the longest time. It will save me so much time.

 

[showgun3]

Is it possible to get and use the sheets address as well, so that I can get data from other sheets?

 

[antthorne]

What you could do is use this Macro to give you all the sheet names from your workbook

Sub SheetNames()
'change the sheet to the one you want and change cells(i,50) to change the column cells(i,1) would be column A
sheets("sheet1").select
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 50) = Sheets(i).Name
Next i
End Sub

This will then get you the sheets names in your workbook (I would suggest creating a button to run this macro)

You would then change your indirect formula to include these if your first sheet name was in cell AA1 then it would look something like this

=indirect("'"&AA1&"'!"&b6)

I think this should work then

Thanks

Anthony

 

[showgun3]

Thanks I will give it a try. I played around with your formula yesterday and I had to make several changes but I got it to work, thanks to you if it is on the same sheet. Now I am trying to get it done from a different sheet because that is where I copy it from. I am also looking into doing it all from VBA, I think that will be more convenient and efficient.

My final formula, all in one looks some thing like this:

=INDIRECT(ADDRESS(ROW(INDIRECT(MID(getformula($A$12),3,FIND("+",getformula($A$12),2)+1))),COLUMN(INDIRECT(MID(getformula($A$12),3,FIND("+",getformula($A$12),2)+1)))+2))

 

[konew1]

Hi
This all seems terribly complicated, and I wonder if a simpler solution might be found with more information.
In the first posting you have "10" in A1 so you want 10 in A3, but the real data source is a different sheet. What is the criteria that you use to determine the row that you want the data from? I wonder if index might be a solution

 

[showgun3]

i am definitely hoping for a simpler solution. That is why I am looking into VBA now, but I am not very experienced in it. The above table is only an example. What I need is once I enter in A3 a reference to another cell which is also on another sheet, then I want to automatically copy it into B3, C3, D3 and so on. But this can only be done once the contents of A3 is analyzed to see which sheet is came from, then the row number and column number is analyzed. Once I have all the three needed information then I need to apply to next B3 by just increasing the column and keep sheet and row number the same.

I have now been able to get the data and update it automatically in the same sheet with Anthony's help but now I am trying for getting the data from a different sheet. Hope this clarifies.

 

[konew1]

why don't you find a working space (spare cell, out of the way) and put =Cell("Address",click_on_cell_on_other_sheet") Say that is cell X1, This will return the address of the first cell of the required line from the other sheet.
Then, using a combination of indirect and offset, something like =OFFSET(INDIRECT($X$1),0,COLUMN()) in column A of the row where you want the result, and copy across.

This is all sheet formula not vba

 

[showgun3]

Thank you konew1, this is absolutely awesome. This definitely works much better than the other one as well as more simpler. Thank you.