Help Writing a Complicated Formula

[John Sofillas]

I am scratching my head on trying to get the correct result with what
I am trying to do. Below is an example of what I need. Is there any
kind of formula that would look up in a row and result another cell
value (the formula would not be in the same row as the lookup)?

ex:
Formula would be in E43. In Row 46 there would be several random
values (from P46 through DH46). I need DH46 value to be in E43, but
the kicker is DH46 will eventually get deleted and the E43 will need
to return DB46 and so on back. So simply linking E43 to DH46 works but
then when DH46 is deleted, the formula is an error. And then I need to
change it to DB46.

This is just a small portion of what this giant spreadsheet needs to
be, so maintaining it is a nightmare. Is there anyway E43 can have a
formula that reads the row range of 46 and returns the last #. And
work so that when the last # gets deleted, it is still returning the
last # in that row/range.

Thanks in advance for any help. If more detail is needed, let me know.

 

[Claus Busch]

Hi John,

Am Wed, 6 Jul 2011 13:23:28 -0700 (PDT) schrieb John Sofillas:

This is just a small portion of what this giant spreadsheet needs to
be, so maintaining it is a nightmare. Is there anyway E43 can have a
formula that reads the row range of 46 and returns the last #. And
work so that when the last # gets deleted, it is still returning the
last # in that row/range.

try:
=LOOKUP(9^99,46:46)


Regards
Claus Busch

 

[Gord Dibben]

In E43 enter =LOOKUP(2,1/(46:46<>""),46:46)

This will return the last value in row 46

Also see help on the INDIRECT function.


Gord Dibben MS Excel MVP

 

[John Sofillas]

In E43 enter   =LOOKUP(2,1/(46:46<>""),46:46)

This will return the last value in row 46

Also see help on the INDIRECT function.

Gord Dibben     MS Excel MVP






- Show quoted text -

Thank you both Claus and Gord. Both formulas work EXACTLY like I need
them to. As I implemented this formula, I came across ANOTHER
variation (or tweak) to it to do what I need.

So now, instead of it returning the value as previously mentioned, I
am looking for the sum of several numbers (with the intent that as I
delete #'s out of the look up, the formula doesn't result in an
error). To make things even tougher, there will be 2 formulas needed,
where each are looking for their own respective answers coming from
the same row. Confused? lol..... Below are examples of both.....

The 1st formula would go into cell D45. I need it to return the sum of
P42, V42, AB42, and so on (17 total cells). The 2nd formula would go
into cell D46. I need it to return the sum of Q42, W42, AC42 and so on
(17 total cells). Keep in mind that as I delete the 17th column, 16th
column and so on, that the formula still works.

I hope I explained this clear enough to you all. Any questions, let me
know. Thanks again in advance for your help.