Better Way than If Statements???

[kls]

Have spreadsheet with 17 tabs. Each one has the same 14 columns with the same week ending $. The last spreadsheet, tab 18 is a summary. The summary needs to pick up the “last” week ending $ for each column. I am not adding the $ together for the summary. Right now, there is no limit on the rows of week endings; could be 30 per sheet. I can do a million (!) “If” statements to make this work; but am looking for a better way to handle this and have not been able to come up with one yet. All help is appreciated.

Tab 1 Grocery
we 5/3/03 $500,000
we 5/10/03 $480,000
we 5/17/03
we 5/24/03
we 5/31/03

Tab2 Grocery
we 5/3/03 $200,000
we 5/10/03 $600,000
we 5/17/03
we 5/24/03
we 5/31/03

Tab 18 Grocery
Tab1 $480,000
Tab2 $600,000
Tab3
Tab4

 

[IML]

You could use
=INDEX(INDIRECT(A2&"!B1:B10"),MATCH(MAX(INDIRECT(A2&"!A1:a10")),INDIRECT(A2&"!A1:A10"),0))
where your tab name is in a2 and the maximum range on the sheets is A1:B10. Adjust this to suit, but do not use A:A or B:B to select the whole column.

 

[kls]

Thank you so much....This Worked GREAT!!!

 

[Damon Ostrander]

Hi klf,

I know you already have a quite acceptable solution, but thought you might be interested in this because it makes doing things like this so easy. Here is a VBA user-defined function (UDF) that yields the last value in a column. In your case

=LastVal(Sheet1!C1)

should yield the value $480,000, etc. Hopefully it is fairly obvious how you could use this UDF to solve your problem.

If you want to try it, here's the code:

Function LastVal(Rin As Range) As Variant
'This UDF returns the last value in the same column
'as the input range
'Usage: =LastVal(Sheet1!C1) returns the last value in column C
Application.Volatile
LastVal = Cells(65536, Rin.Columns(1).Column).End(xlUp).Value
End Function


To install it, go to the VBE (keyboard Alt-TMV, insert a new macro module (Alt-IM), and paste this code into the VBE code pane.

 

[IML]

Another reason to use Damon's approach. I just noticed my formula aren't necessarily pulling the last value in your column, but rather picking the latest date and returning the corresponding cell next to it. If you have dates filled out with no values next to them (as in your sample date), my approach would fail.

 

[kls]

The last value in each column is a sum formula for that column so pulling the last date will work. If there's another way to do this via vba...I'm interested.

 

[Dave Patton]

On the Summary sheet and with data on Sheet named 1b

I used

=LOOKUP(BigNum,'1b'!B1:B1000)

or with the sheet name in A2

=LOOKUP(BigNum,INDIRECT("'"&A2&"'"&"!B1:B1000"))


I did not get Damon Ostrander's UDF to give a result.

I am at a PC with Excel 97, The UDF seems to yield information from the active sheet.

Did I mis read or am I missing something?

Dave

 

[Damon Ostrander]

Hi kls,

Please excuse my very stupid error. This is one that I lecture others about.


Here is the fixed code:

Function LastVal(Rin) As Variant
'This UDF returns the last value in the same column
'as the input range
'Usage: =LastVal(Sheet1!C1) returns the last value in column C
Application.Volatile
LastVal = Rin.Cells(1).EntireColumn.Cells(65536).End(xlUp).Value
End Function

Damon

 

[Dave Patton]

Thanks for the revision.

I revised your UDF to access the information on another sheet but I
used separate sheet and column references.

You solution is cleaner.

Dave

 

[kls]

Thanks Damon, I also revised your UDF to include only the range with the dates. This is definitely an easy way to pull this info.