ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Help with a formula (https://www.excelbanter.com/excel-discussion-misc-queries/227212-help-formula.html)

Victor Delta[_2_]

Help with a formula
 
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


Jarek Kujawa[_2_]

Help with a formula
 
"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



On 9 Kwi, 18:58, "Victor Delta" wrote:
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



Victor Delta[_2_]

Help with a formula
 
"Jarek Kujawa" wrote in message
...
"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


Jarek Kujawa[_2_]

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



On 10 Kwi, 01:10, "Victor Delta" wrote:
"Jarek Kujawa" wrote in message

...

"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



Victor Delta[_2_]

Help with a formula
 
"Jarek Kujawa" wrote in message
...
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


Jarek Kujawa[_2_]

Help with a formula
 
sorry
thought you were using Google
http://groups.google.pl/group/micros...92c2b0c?hl=pl#
thanks anyway
;-)

On 10 Kwi, 13:42, "Victor Delta" wrote:
"Jarek Kujawa" wrote in message

...

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



Victor Delta[_2_]

Help with a formula
 

"Jarek Kujawa" wrote in message
...
sorry
thought you were using Google
http://groups.google.pl/group/micros...92c2b0c?hl=pl#
thanks anyway
;-)


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


Jarek Kujawa[_2_]

Help with a formula
 
I use a fake address
or slightly change it

On 11 Kwi, 11:35, "Victor Delta" wrote:
"Jarek Kujawa" wrote in message

...

sorry
thought you were using Google
http://groups.google.pl/group/micros...sc/browse_thre...
thanks anyway
;-)


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




All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com