Replies

Ah. That would do it.
Thanks again for your help.

Brooke

Legare,

Many thanks indeed!
The final formula is:

{=INDIRECT(“data!”&ADDRESS(MATCH($A$9&$B15,data!$A$3:$A$200&data!$B$3:$B$200,0)+2,17))}

without the “data!”& it was picking up off the presentation sheet (that had me confused for a while!), the +2 is because my range started at row 3, not 1, and the 17 is because the text wasn’t in N as I suggested.

I don’t suppose you can suggest why the following was giving me intermittent results?

{=CONCATENATE(IF((($A$9=data!$A$3:$A$200)*($B15=data!$B$3:$B$200))*1=1,data!Q$3:Q$200,””))}

Thanks again

Brooke

Have you considered SUMIF()? It may not be appropriate but I was trying to get the same result a couple of weeks ago and realised that you can use sumif() as long as you have a column spare to put some indicators in.

The essence of it is as follows:

columns:
a b c
row
1 jan orders 6000
2 revenue 8000
3
4 feb orders 3000
5 revenue 4000
6
7 q1 orders =sumif(b$1:b$6,b7,c$1:c$6)
8 revenue =sumif(b$1:b$6,b8,c$1:c$6)

I hope that’s understandable. This works for rows as well as columns but as I said, it does require the labels, though if you don’t want them cluttering up the worksheet you could then hide the row or column you put them in.

HTH

Brooke

Geoff,

I don’t think there is a built in function. If anyone knows better, I would love to be corrected.

Brooke

I’d always assumed(!) that that was the Tools/Options/General/Username setting. Probably wrong though.

Brooke

That’s because we just spent

There’s nothing wrong with bean counters!

Brooke

That’s microsoft for you. There’s an earlier thread about this but in the meantime try using ctrl-alt-f9

Brooke

Hmm. I always thought there were two types of checkbox for excel…. now it turns out there are three? I’d best go read the manual again.

Brooke

Ok, so I didn’t have hold of the thread! Ignore me and I’ll go away, honest.

Brooke

Excuse me if I’ve not got hold of the thread properly, but If you’re in the vbe looking at your form you should be able to open up a properties box either from the standard toolbar or by right clicking on the offending checkbox and one of the properties displayed should be font.

HTH

Brooke

Would this method be affected by the user disabling macros?

Brooke

the thing that strikes me as perhaps the most annoying and least expected about turning on protection is the file bloat (i’ve seen up to 50% increases).

Brooke

I’ve seen something like your description in other office apps – powerpoint in particular. I believe it was connected to having a mouse with a wheel. It may be worth searching the other forums? FYI, my way of solving the problem was to change jobs and hence PC setup/ini files/registry settings or whatever was causing it – This seemed to work, if a bit drastic! Sorry I can’t be of more help.

Brooke

If you’re using 97 you should have data validation as well, leastways I do.

Brooke.