Finding intersection point in rows and columns

[mpjohnston]

I am trying to work a macro which will do the work for me (go figure).

Here is the easy example. I have a worksheet which has a 3 columns an
a bunch of rows: the column headings are " ", "total", "add-on"
the rows then consist of "mike","1,000","200"... then next ro
"cindy","1,230","23" etc

I would like a different worksheet to have a macro button that ca
take the data and put it into a new table and stuff.

I was thinking along the lines of creating labels on the top and left
and then referencing the numbers according to the labels.

For instance =intersect("total","mike") would produce 1,000

Any ideas on how to get this to work? Obviously using absolut
refernces like "B2" would work but the worksheets cell locations ma
vary thats why I was thinking making labels would work.

Thanks a bunch!
Mik

 

[JulieD]

Hi Mike

you can use the offset & match functions, e.g.

=OFFSET(Sheet1!A2,MATCH("Cindy",Sheet1!A3:A5,0),MATCH("Add-on",Sheet1!B2:G2,
0))

where your original data starts in sheet 1 at A2

Hope this helps
Cheers
JulieD

 

[Camilo]

Are you familiar with the 'vlookup' Function?

VLOOKUP: Searches for a value in the first column of a table array and
returns a value in the same row from another column in the table array.

 

[Alan Beban]

If you select your range of data, including the row and column headers,
and click on Insert|Name|Create and then check Top row and Left column, then

=total mike and =add-on cindy (or =mike total and =add-on cindy)

will return the value of the cell at the intersection.

Alan Beban

 

[Tushar Mehta]

Also see the responses in .programming and .worksheet.functions

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I am trying to work a macro which will do the work for me (go figure).

Here is the easy example. I have a worksheet which has a 3 columns and
a bunch of rows: the column headings are " ", "total", "add-on"
the rows then consist of "mike","1,000","200"... then next row
"cindy","1,230","23" etc

{snip}

 

[mpjohnston]

First off, Thanks all!!!!!

I am going to use the "=total mike" seems stupid simple now. At th
same time I will probably be looking into those other options if I fin
some problems.

Thanks again!
Mik