Possible VLOOKUP 1

[mosmas]

Here is what I need to accomplish:
I have a spreadsheet with the UID numbers like this:
UID 2/28/03 3/28/03 5/2/03 5/30/03 6/27/03 8/1/03
7162 0.1 0.8 0.9 0.8 0.8 0.9

On another worksheet, I have just the UIDs:

UID
7162
6239
3309


I want lookup the UID and if it finds it, I want to add the corresponding values. For example, if 7162 I would like to add 0.1+0.8+0.9+.8+.8 and so on.

Any ideas?

Mosmas

 

[Zathras]

Assuming your data begin in cell A1 with "UID" and A2 with 7162, etc., and the sheet name is "Sheet1", then you can set up Sheet2 this way:
[blue]

A1: 'UID
A2: 7162
A3: 6239
A4: 3309
B1: 'Row
B2: =MATCH(A2,Sheet1!$A$1:$A$8,0)
C1: 'Totals
C2: =SUM(INDIRECT("Sheet1!B"&B2&":AA"&B2))
  (copy B2:C2 into the next two rows)

[/color]
Adjust the "AA" in the INDIRECT formula to be whatever the last column you will need for your data.

If you don't want to use a temporary column to contain the row numbers, you can put it all together into one formula:
[blue]

  =SUM(INDIRECT("Sheet1!B"&MATCH(A2,Sheet1!$A$1:$A$8,0)&":AA"&MATCH(A2,Sheet1!$A$1:$A$8,0)))

[/color]
but that's a little harder to maintain and debug.

 

[mosmas]

Thanks... this worked. Now what I want to do is to control the last column of the sum. Here is the formula that I have depending on the data that I am working with:

SUM(INDIRECT("'Hrs Spreads'!j"&AD8&"Q"&AD8)))

DQ will be my last column of which the last column header is a date. I want to be able to type the date and the formula will return the sum up to that date.

Thanks for your input.

mosmas

 

[Zathras]

I added two more working columns:
[blue]

AC7: 'UID
AC8: 7162
AD7: 'Hrs Spread
AD8: =MATCH(AC8,'Hrs Spreads'!$I$1:$I$4366,0)
AE7: 10
AE8: =ADDRESS($AD8,AE$7,,,$AD$7)
AF7: =MATCH(AG7,'Hrs Spreads'!J1:DQ1,1)+AE7-1
AF8: =ADDRESS($AD8,AF$7,,,$AD$7)
AG7: 8/1/03
AG8: =SUM(INDIRECT(AE8):INDIRECT(AF8))

[/color]
The "10" in AE7 corresponds to column "J" (the first data column.)

The "AE7-1" in AF7 calculates the last data column number based on the date match and the first data column number. By using the value "1" as the third parameter for the MATCH function, it allows the user to enter a date between dates on the data sheet (Hrs Spreads) and still get a usable result. E.g., if the user enters 7/31/03, the result will be a sum thru 6/27/03.

The 8/1/03 in AG7 is just an example. Replace it with whatever date you need to sum thru.

You should look up the ADDRESS and MATCH functions in the help file in order to gain a complete understanding of how they work.

 

[bluedragon2]

Just an additional thought:

Why not have a total Column after the UID:

UID Total Date1 Date2 etc
7162 1.7 .8 .9

Then when you do a VLOOKUP on the UID, just return the total value that you want.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[mosmas]

Bluedragon2:

I wish it was the case. That would have easy. What I want to do is to be able to type a date, say Date2, the sum that I want would be for date1+date2. If I type in date3, I want the sum of date1+date2+date3 and so on. This is the ultimate goal. And from what I got from Zathras, I hope it will work. i am going to test it today. I will let you know what happened.

Zathras, thanks a lot for your assistance.

Regards,

Mosmas