Silly Excel Question or maybe iam going mad 1

[Chance1234]

I have a list of data on one sheet, and on another sheet i have a serious of formulas that carrys out calculations on the sheet with the data.

the only problem is, the amount of data in the list changes reguarly and the updatres are done through code, so excel doesnt automaticly change the references in the formulas on the next sheet.

Am i going mad or missing the obvious but i was sure you could do this, re below


in my formulas

i have a formula like this

=SUMIF(data!N2:$N15,"bob",data!P215)


now the next day i only have 10 records, now its not a problem with teh criteria being bob, but lets say it is "Is blank" that is going to give me incorrect results.

am i imaging things or is it possible to say have another cell on the sheet which contains the number of rowss on the data sheet and use that in the reference of the formula.

so lets say i stuck the number in cell a1, then with my formula change it to

=SUMIF(data!N2:$N(a1),"bob",data!P2(a1))

but it doesnt work !

iI know a way to do it code, but it is friday and im tired and bored,

 

[Goska]

=IF(O2<>0,COUNTIF(O2:INDEX(O:O,$P$1),O2),0)

=SUMIF(data!N2:INDEX(data!N:N,$A$1),&quot;bob&quot;,data!P2:INDEX(data!P,$A$1))


Try this. I put some of my own code above if that will help, as I am not quite sure what you are doing with this.

 

[Chance1234]

cheers that done the trick i can now get that the pub earlier

thanks

 

[Goska]

If by &quot;pub&quot; you mean &quot;bar&quot;, I will be doing the same thing over here tonight.

Enjoy your weekend.

 

[DaleWatson123321]

Hi chance1234,

This will probably NOT be a short-term solution. Therefore, you probably should leave this for next week.

BUT, I feel the need to suggest to you that you SERIOUSLY look at the option of a MORE DYNAMIC, MORE FLEXIBLE, MORE POWERFUL way of dealing with database data within Excel.

Excel has database functions such as =DSUM atabase,offset,crit) - where these three arguments are arbitrary-named Range Names. You would assign the (range) name &quot;database&quot; (or name it whatever you want) to the data. You can (should) assign the name to additional rows to allow for the MAXIMUM number of potential records (rows) which could occupy this &quot;database&quot;.

The second argument (&quot;offset&quot refers to the field name on which you want to have your database formula perform its calculation.

The third argument (&quot;crit&quot is a range (named &quot;crit&quot; or whatever you want) which includes the field name(s) which you need to use to isolate the type of data you want your formula to include. Below the field name(s), enter the type of data you want included. If you want your criteria to include additional types of data, you can include additional row(s) in your criteria range.

Caution: There are a small number of &quot;reserved names&quot; which Excel uses internally. This includes the name &quot;criteria&quot;, so do NOT use &quot;criteria&quot; as a range name. Also, do NOT use &quot;database&quot;.

Normally, one would need to have different formulas to thoroughly summarize the data. Therefore, you would need to set up SEPARATE criteria for each formula. You should assign a range name to each separate criteria (and then of course use those names in your formulas).

Basically, this &quot;database&quot; option is a very powerful option, which ALSO includes the option of extracting the records which are producing the values in your formulas.

The records can be extracted to a separate sheet. The following code is a brief example of code to do so. It also sets up the print range.

Dim FirstCell As String
Dim LastCell As String
Dim LastRow As String
Dim LastColumn As String
Dim Print_Range As String

Sub Extract_Company()
Range(&quot;data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=&quot;crit&quot;, _
CopyToRange:=Range(&quot;output&quot, _
Unique:=False
Range(&quot;company_top&quot.Select
Set_Print_Range
End Sub

Sub Set_Print_Range()
ActiveCell.Offset(4, 0).Select
FirstCell = ActiveCell.Address
Get_Last_Row
LastColumn = &quot;G&quot;
LastCell = LastColumn & LastRow
Print_Range = FirstCell & &quot; : &quot; & LastCell
Range(Print_Range).Name = &quot;PR&quot;
ActiveSheet.PageSetup.PrintArea = &quot;PR&quot;
ActiveSheet.PageSetup.PrintTitleRows = &quot;$1:$4&quot;
End Sub

Sub Get_Last_Row()
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
End Sub


You will need to &quot;invest&quot; a little time in learning all of the functionality of these database features, but it will be time well invested.

There is also the option of using the &quot;Data Filter IN PLACE&quot; option, which is useful in some situations, but I highly recommend you do NOT limit yourself to filtering IN PLACE. Explore the extraction option as it can offer more flexibility and options.

I just wanted to give you a &quot;taste&quot; of the power of database formulas and functionality. I hope I have succeeded.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca