Getting Row and Column information from a reference

[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 one I copy the first column.

Usually I would do =+'Sales'!C11, from here on onwards I would like to use offset function to get the sheet to auto update all data. What I cant figure out is how to get the relevant sheet name, row and column numbers. If there is a way through VBA or just a inbuilt function, help me please.

Thanks.

 

[Robert Mika]

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 one I copy the first column.

Usually I would do =+'Sales'!C11, from here on onwards I would like to use offset function to get the sheet to auto update all data. What I cant figure out is how to get the relevant sheet name, row and column numbers. If there is a way through VBA or just a inbuilt function, help me please.

Thanks.

Hello and welcome.
Could you provide an example?
To post data use method in my signature.

 

[showgun3]

Hello and welcome.
Could you provide an example?
To post data use method in my signature.

In the screen shot attached, I referenced B2 in B4, now I want to analyze B4 and get value b2, i.e. row 2 and column b from it, then auto fill the remainder of the values from c2, d2 and e2 into c4, d4, e4 but all based on the value of B4. Same goes for when I am bringing in data from another sheet, I want to find out which sheet the data is coming from in B6 so I can get the remainder automatically.
Hope this clarifies.

A	B	C	D	E
	10	20	30
	=+B2

<tbody>
</tbody>

 

[Robert Mika]

In the screen shot attached, I referenced B2 in B4, now I want to analyze B4 and get value b2, i.e. row 2 and column b from it, then auto fill the remainder of the values from c2, d2 and e2 into c4, d4, e4 but all based on the value of B4. Same goes for when I am bringing in data from another sheet, I want to find out which sheet the data is coming from in B6 so I can get the remainder automatically.
Hope this clarifies.

A	B	C	D	E
	10	20	30
	=+B2

<tbody>
</tbody>

Not really clear.
Try here:
Excel INDIRECT Function Tutorial
Does this help?

 

[showgun3]

Indirect I can only use, if I know that the answer is in Column B and row 2, but if I dont then i only want to provide the first one which is B2, then the rest of the data should be picked up automatically. That mean I need to break B2 some how and take B and 2 apart, that is row and column, then I can just add or delete more rows and columns using indirect or offset functions.

 

[showgun3]

I guess I can also play around with Row() and Column() Functions as well.

 

[Robert Mika]

, then I can just add or delete more rows and columns using indirect or offset functions.

Is this going to be a part of a macro?
You can not add or delete rows or columns based on a function/formula only.
Could you provide another example?

 

[showgun3]

No, I cant think of another example. This is exactly what I need. In my workbook, I have to enter data in one sheet and from there I transfer data to several different reports. The things is once I do a reference on the first columns, all subsequent columns should take the reference from the first column to the original sheet and row and add one or more columns and get the data.