Generate Formula on the fly

[wally2u]

I need to generate a formula on the fly based on thelast entry in the worksheet. I have the last row number thanks to a posting byPeter_SSs; =MATCH(TRUE,INDEX(A2:A3200="",0),0). I found in reading olderpost. Thanks Peter.
This last row number is in H1, the array of datais B2:Fnnn last row number in H1 value is 692 on this run. This number will changeeach time the data is accumulated. This data is exported from another system ina CSV file format which is brought into this worksheet.
The formula is =COUNTIF($B$2:$F$692,"="&$K1)in Column M.
My question: is there a way to use some functionto have the F$nnn to change as H1 is updated without having to do a FIND andREPLACE on column M each run.
Thanks
Walter

 

[Johnny C]

Re: Generate Formular on the fly

You could try using a dynamic range. Go into Names manager, add a name (e.g. CSVData) and in the 'refers to' box put

=OFFSET([worksheet name]$B$2,0,0,COUNTA([worksheetname]!$B2:$B99999),5)

Use your mouse to click the B2 and B2:B9999 range to get the right syntax, remember that if you want to edit the formula manually you need to press F2 to stop the cursor keys selecting a cell.

Then your formula becomes
=COUNTIF(CSVData,"="&$K1)
(or whatever name you gave to the dynamic range)

 

[Johnny C]

Re: Generate Formular on the fly

Another way is to use Indirect

=COUNTIF(INDIRECT("$B$2:$F$"&$H$1),"="&$K1)

Indirect lets you turn text into a reference. Normally I would say use the method in the post above but this is easier and quicker if it's a spreadsheet only you use. Note it will throw an error if you don't have a positive number in H1

 

[Peter_SSs]

Re: Generate Formular on the fly

Do you really need to keep changing the formula? Doesn't this return the correct results anyway?

=COUNTIF($B$2:$F$3200,"="&$K1)

 

[wally2u]

Re: Generate Formular on the fly

Thanks Johnny, I'm using the second option works perfect. I will be getting a new CSV file in about 2 hours, I'll try the first later to learn OFFSET. Thanks again.
Walter

 

[wally2u]

Re: Generate Formular on the fly

Hi Paul, it just may. I wanted to know the exact number of lines in the file for some of the other columns comparisons %'s and averages. I did not give all the details of the application. But the MATCH was what I needed to get that number. Thanks again
Walter

 

[Peter_SSs]

Re: Generate Formular on the fly

Hi Paul, it just may.

I presume you meant Peter.

If you did want to do the COUNTIF over the exact range, here is a formula that avoids volatile functions like INDIRECT or OFFSET which can slow your sheet if used a lot. Note also that you don't need the "="& part of your formula.

=COUNTIF($B$2:INDEX($F:$F,$H$1),$K1)

 

[wally2u]

Re: Generate Formular on the fly

Sorry about the name, at 71 I'm doing good to remember mine . I will use that tip also, looks much cleaner. Thanks again
Walter

 

[Peter_SSs]

Re: Generate Formular on the fly

Sorry about the name, at 71 I'm doing good to remember mine .

I know the feeling! - I'm actually not very far behind you. (My picture at left is not very recent )