Access and Excel

[OneRedLT4]

I've built a Database with Access and am trying to get one last thing o
my boss' excel spreadsheet to work. He's the expert in excel, but we'r
stumped, so I thought I'd try and post.

In an excel spreadsheet, I have a row of cells (we'll call it row #27
that show the results of the different inputs of it's column. Becaus
of the way the spreadsheet was designed (not by me) these are ever
other column and there are 16 of them. The last column (the 17th) ha
the totals of the rows going across. In this last column, cell 27 need
to equal the value of the nearest cell to it's left that's not Null, o
have the "#####" thingys. In otherwords, at any given time, not ever
column is filled with info, and not until the column is filled out doe
the cell #27 produce a running calculation.
Example: A27, C27, E27, G27 have values. I27, K27, and M27 does no
have values. So N27 needs to show the same value as G27, but when th
next update comes and I27 gets a value, N27 needs to show that. I hop
this makes sense. I was told that Excel has this formula built in, bu
I can't find it.
Thanks.
Ji

 

[Frank Kabel]

Hi Jim

the following formula will work for cells with value or which are
blank. So for N27 enter the following
=INDEX(A27:M27,1,COUNTA(A27:M27))

you can copy this formula for all other rows

Frank

 

[OneRedLT4]

I tried it, and I don't think that I described what I wanted very well.
I'm attaching one of the sheets for you to see. It's actually row 39
that's in use. Column AO39 will need to be were the code goes. As it
sits, AO39 should show 100%. When columns K and L get updated, L39 will
have a new number, and that is what AO39 should then show.

Thanks for the help
Jim

Attachment filename: temp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=410824

 

[OneRedLT4]

I guess that if this needs to be done, a big, long, confusing bunch o
nested "if" statements will have to be the answer. If no one here ca
figure it out, no way I'm going to

 

[Frank Kabel]

Hi
i posted an idea some days ago. If it does not work for you, maybe you
can describe what goes wrong

Frank

 

[OneRedLT4]

Hi Frank,
I tried it and it just showed a "0" rather than the disired value
I posted a follow up and attached one of the actual sheets that I'll b
using. I attached to this post the sheet again, with the function yo
suggested, but you'll see it doesn't provide the correct results. Th
explanation is as follows:

(-"I tried it, and I don't think that I described what I wanted ver
well. I'm attaching one of the sheets for you to see. It's actuall
row 39 that's in use. Cell AO39 will need to be were the code goes
As it sits, AO39 should show 100%. When columns K and L get updated
L39 will have a new number, and that is what AO39 should the
show."-) I did adjust it for the correct cell addresses

"=INDEX(H39:AL39,1,COUNTA(H39:AL39))"

Thanks
Ji

Attachment filename: temp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41176

 

[Frank Kabel]

Hi Jim

I see there the problem is. You have a lot of errors in cells H39 -
AL39. As my suggested formula only tested for blanks it counted all
errors, resulting in an '0'. I would suggest that you check for errors
in row 39. E.g. change H39 as follows:
=IF(ISERROR(H37/H38);"";H37/H38)
copy this for the entire row

Unfortunately COUNTA won't work anymore but you can change the INDEX
formula as follows:
=INDEX(G39:AL39,1,COUNTIF(G39:AL39,">0")*2)

I had to multiply with two according to your spreadsheet design. If you
have any problems, send me an email and I'll send the corrected
spreadsheet to you

HTH
Frank

 

[OneRedLT4]

Thanks Frank,
I;m going to try that when I get to work. A friend of mine that
don't see very often came up with

=OFFSET(H39,0,COUNT(H39:AL39)+COUNT(H39:AL39)-2)

and seems to work as well. I have no idea how you guys come up wit
these things, but I guess if you do it enough...


Thanks again, and hopefully today will all but finish up the database.

Ji