Perhaps a vlookup question which also involves label

M

mpjohnston

So here is the newest in my silly crazy questions. I have a worksheet
that contains a varying amount of data. At the end of the data in
Column A is "Grand Total:" then some proceding data (column B, C,D,
etc.)

I would like to find that "Grand Total:" cell and label the row as
grand_total.... or perhaps put each none blank cell in that row in an
array (perhaps another option)

I can find the "Grand Total:" by doing:
=VLOOKUP("Grand Total:",A:B,1,FALSE)

but I am at a loss as to how to find the cell identification (it could
be in A2345 or A9111.... completely arbitrary but always the same
text)

Thanks, Mike
 
D

Dave Peterson

I don't quite get what you're doing, but if you wanted to return the value in
column B of that Grand total: row:

=INDEX(B:B,MATCH("grand total:",A:A,0))

If you're building those totals via data|subtotal, "grand total" doesn't have a
colon in it.

And you may want to take a look at Data|pivottable to get your summaries. It's
darn powerful after a little learning.
 
M

mpjohnston

That little tidbit of code is exactly what I was looking for.

A brief summary of what I am doing is that I am running a macro fo
about 5 reports. Each report is run weekly and we make a master repor
of them all. So in this perticular report we are just looking for th
grand total to put on the master report, all other information i
useless.

Thank you so much for your help.... I really need to check out a boo
and look into the MATCH and INDEX command

Mik
 
K

kraljb

You were also very close with your original formula all that you neede
to change was the
*A:B,1,FALSE)*
to
*A:B,2,FALSE)*

The 2 is the column number in the table range (in your case A:B). S
you can increase that number to pull in other information in othe
columns, but you also have to increase the range A:B as well
 
M

mpjohnston

New problem. May start a new thread if I dont see a response since thi
is kind of buried in.

Anywho I have come to realize one of my reports has a sub grand tota
field. Thus I need the match:

=INDEX(D:D, MATCH("grand total:",A:A,0))

to be the second, or last occurance. Is this possible?

Thanks,
Mik
 
D

Dave Peterson

Sometimes, I'll put totals in row 1. Then they're always visible and I know
where they are.

(I'll use windows|freeze panes to always show those totals and the header rows,
too.)
 
D

Dave Peterson

I think I'd cheat and change that value in the macro (to SubGrand Total???), but
this worked ok for me:

=LOOKUP(2,1/(A1:A1000="Grand Total:"),d1:d1000)

Use enough rows to include all your data.
 
M

mpjohnston

I like the idea but it is not an option. These are generated report
through various programs that I have no control over, nor can I get th
budget to go to my supervisor to contract out the companies to creat
new reports.

One other thing I noticed is that the first "Grand Total:" listing i
bolded whereas the second is not bolded, perhaps I can throw in an i
statement that would allow me to ignore the first occurance
 
M

mpjohnston

Thanks Dave that seems to work.

On another note, I was just putting together in the macro to have i
find the first occurance and change the text. My one concern with tha
method is that if the user does clicks the macro a second time then i
would change the text that I actually need. My solution was going to b
to only chnage the text if the text is bold. Not sure how I was going t
do it but seemed plausible.

Mik
 
Top