Help with a formula

V

Victor Delta

I have an Excel spreadsheet which records monthly meetings with clients. The
clients' names are listed in column A and each column in a certain range
(say G to Z) represents a month. I periodically insert new columns on the
end as necessary (and I should mention that columns B to F and AA and beyond
are used for other data. Meetings are indicated by inserting the date or
some text in the appropriate column. There are often several months between
meetings with a particular clients - indicated by empty cells.

I would like to show in, say, column B, either the month of the last meeting
OR the number of months since the last meeting (it doesn't matter which as
the other can be calculated easily from the first) but am not sure to do
this. I've tried a couple of formulae which did not do what I wanted.

Can anyone please suggest a simple method.

TIA

V
 
J

Jarek Kujawa

"the month of the last meeting" with a particular client would be:

=MAX(IF(ISBLANK(G1:Z1),"",COLUMN(G1:Z1)))-6

provided G coulmn is for month 1
adjust G1:Z1 to suit

array-enter this formula, i.e. with CTRL+SHIFT+ENTER (instead of using
Enter only) as it is an array formula
if the formula is inserted correctly then curly brackets will show in
the formula {beginning and end}

pls click YES it this post helped
 
V

Victor Delta

Jarek Kujawa said:
"the month of the last meeting" with a particular client would be:

=MAX(IF(ISBLANK(G1:Z1),"",COLUMN(G1:Z1)))-6

provided G coulmn is for month 1
adjust G1:Z1 to suit

array-enter this formula, i.e. with CTRL+SHIFT+ENTER (instead of using
Enter only) as it is an array formula
if the formula is inserted correctly then curly brackets will show in
the formula {beginning and end}

Many thanks, just the job!

V
 
J

Jarek Kujawa

welcome, thks for the feedback
;-)
would you mind clicking some stars next time? to evaluate my post
thanks
 
V

Victor Delta

Jarek Kujawa said:
welcome, thks for the feedback
;-)
would you mind clicking some stars next time? to evaluate my post
thanks

What stars? I'm using Windows Mail to access this Usenet group and cannot
'see' any stars...!

Otherwise, I would give you 5 *****.

V
 
J

Jarek Kujawa

I use a fake address
or slightly change it

The only time I used Google Groups for Usenet, I ended up exposing my email
address to lots of spam which has never gone away!

How do you overcome that problem?

V
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top