Excel question....help its really bugging me

[FTSEBOY]

Hi there

I would appreciate it if there is an excel guru out who could help me with a formula issue I have in excel. On the attached spreadsheet, each day I create a new column. What I am trying to get excel to do is to always look at the last populated column and perform the calculation. Does anyone know how I can get the formula to always look at the furthest column on the right everyday?

Many thanks

FTSEBOY

 

[shadowninja]

Dunno how to do it without running a macro. If you can write a macro (am very rusty so not much help), just get it to move along until it reads an empty cell, then refer to the previous column and work with the figures in that.

There may be a function that automatically checks the value in the cell furthest on the right, of course.

 

[the blades]

Hi there

I would appreciate it if there is an excel guru out who could help me with a formula issue I have in excel. On the attached spreadsheet, each day I create a new column. What I am trying to get excel to do is to always look at the last populated column and perform the calculation. Does anyone know how I can get the formula to always look at the furthest column on the right everyday?

Many thanks

FTSEBOY

I'm struggling for time to do this fully, but I've done something similar using the Vlookup function to drive the inputs to your formula.

If you number each subsequent column you can then set the lookup to max.

If it were ordered in rows it would look something like;

=VLOOKUP(MAX(A:A),A3:B629,2)

this would return the value from column B that was adjacent to the largest number in colum A - in this case your l;atest entry.

Hope this helps? If not, I'll try and expand further later.

UTB

 

[A Dashing Blade]

Dynamic range names sound like what you want
Reasonably sure you can use something like the technique described here
Willow Solutions, Inc.

 

[swandro]

Here is another solution. Post this into cell C8 on the DIFF sheet

=INDIRECT("Portfolio!" & ADDRESS(6,(2+COUNT(PORTFOLIO!6:6)))) -INDIRECT("Portfolio!" & ADDRESS(7,(2+COUNT(PORTFOLIO!7:7))))

Note: the & symbol is the ampersand.

How does it work? I am glad you asked. As you can see, it is in 2 similar halves because you are getting the 2 numbers off the Portfolio sheet and subtracting one from the other. Each part uses the COUNT function to count the number of numeric cells in the appropriate row (6 or 7). It then adds 2 because you have a couple of spare columns on the left. This is what gives you the rightmost numerical value in the row. Then it uses ADDRESS to build a reference to the cell, for example D6, but of course we have to add the sheet name on the front. The last job is to use INDIRECT to return the value contained in the cell address we have just built.

Hope this helps.