Finding an intersection point using labels

  • Thread starter Thread starter mpjohnston
  • Start date Start date
M

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
 
mpjohnston wrote...
...
For instance =intersect("total","mike") would produce 1,000
Click to expand...
...

Name your entire original table TBL, including the top row with th
column labels and the left column with the names. Then you could use
formula like

=INDEX(TBL,0,MATCH("total",INDEX(TBL,1,0),0)) INDEX(TBL,
MATCH("mike",INDEX(TBL,0,1),0),0)

Note that the space just before the second INDEX call is INTENTIONAL
The space is Excel's range intersection operator
 
I am not familiar with the index and match function so definately
something I will be looking in to.

When I tried your initial code, it did not seem to identify my labels.
Thus I got rid of the quotes and it did seem to identify them. But now
it gives me an "N/A" value not available.
 
mpjohnston > said:
I am not familiar with the index and match function so definately
something I will be looking in to.

When I tried your initial code, it did not seem to identify my labels.
Thus I got rid of the quotes and it did seem to identify them. But now
it gives me an "N/A" value not available.
Click to expand...

This is supposed to do something like

=SheetX!B1:B100 SheetX!A3:Z3

(note the space!) which resolves to SheetX!B3. INDEX(Data,0,j) returns the
range that's the entire j_th column of Data, and INDEX(Data,i,0) returns the
range that's the entire i_th row of Data. The MATCH calls in my formula were
intended to locate the column or row needed by searching for matching text
in the top row or left column of Data, respectively.

If you remove the double quotes in my formula, and if you have any cells
containing 'total' or 'mike' (without the quotes) in the worksheet in which
you're entering the formula, Excel will use those as labels within that
worksheet (the one containing the formulas), not the worksheet containing
Data. That's definitely NOT what I meant.

It's possible you have stray spaces in your column headings in the top row
or names in the left column of Data. That'd screw up my formulas. Either
make sure there are no leading or trailing spaces in the top row or left
column of your Data range, or use text constants INSIDE DOUBLE QUOTES that
exactly match the column heading or names in Data including any & all
spaces.
 
Also see the responses in .misc and .programming.

--
Regards,

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

mpjohnston said:
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
Click to expand...
{snip}
 
Back
Top