Excel - Automatic update of table

Microsoft Windows vista ultimate - 32-bi...
May 24, 2010 at 06:29:50
Specs: windows vista, i don't know!
Dear friends, I'd need some help on dealing with an "excel" issue.

I have 2 workbooks.
In the first one I have 2 columns:
A B C
Week Port 1 Port 2
1 345 280
2 400 560
3 367 467
etc..

In the second workbook I have the following table:

A B
Port 1 ____
Port 2 ____

I'm trying to create a formula that would automatically update the cells in clumn B of the table in workbook 2 as, week by week, I add a new value to the table in workbook one.

I've tried the following one:
=IF(A:A=WEEKNUM(TODAY()),B:B,0)
But it doesn't seem to work!
Does it depend on the fact that columns in workbook 1 retrieve the data from another workbook through a vlookup formula?



See More: Excel - Automatic update of table

Report •

#1
May 24, 2010 at 10:23:02
Let's clear up some confusion first:

A tip:

If you use the pre tags found above the Comments box, you can line up your data to make it easier for us to read:


       A        B        C
     Week     Port 1   Port 2
1      1       345      280
2      2       400      560
3      3       367      467


         A            B
 1     Port 1       ____
 2     Port 2       ____

Next, you said you have 2 workbooks but your formula looks like you are trying to access data on the same sheet.

What do you have? 2 workbooks? 2 worksheets?

re: I'm trying to create a formula that would automatically update the cells in clumn B of the table in workbook 2...

Update in what manner? In your first table you have multiple values under Port 1 and Port 2.

What are looking to put next to Port 1 and Port 2 in column B of your second table?


Report •

#2
May 24, 2010 at 10:46:20
Sorry for the confusion.
I'll try to explain the issue in a clearer way.

I have two workbooks.

The first one is the following:

     A           B        C
     Week   Port 1   Port 2
1      1       345      280
2      2       400      560
3      3       367      467

where I store my data.

The second one is the one in which I have the following table

        A            B
 1     Port 1       ____
 2     Port 2       ____

where every week I would like to report the latest week landings per port.

The formula I need is one that automatically updates the table in the second workbook week after week.

I tried the one I reported before =IF(A:A=WEEKNUM(TODAY()),B:B,0) (with the correct workbooks links) but it does not seem to work.
Surely there's something wrong with it.

Thanks for your time and help!!!!


Report •

#3
May 24, 2010 at 11:01:54
How about:

For Port 1:

=VLOOKUP(WEEKNUM(TODAY()),$A$2:$C$53,2,1)

For Port 2:

=VLOOKUP(WEEKNUM(TODAY()),$A$2:$C$53,3,1)

(with the correct workbooks links)


Report •

Related Solutions

#4
May 24, 2010 at 11:20:13
it works!!!
:)

thanks a lot!!


Report •

Ask Question