Excel - ISBlank ? 1

[Lukey]

Any help would be greatly appreciated. I have an excel sheet with products on and the columns track the movement of those products.
I have a final column that is filled in when the product has been returned.
I would like a formula to get the data in the last filled in column (in order) and return it to the last one.
I was trying to use IF and ISBLANK but wasn't sure if I could do multiple ones and couldn't seem to get it to do what I wanted.
For example, depending on how many times an item has moved, there could be data in E,G,J,L,N and then the final column is V. I want the last entry to be copied across to column V.

Hope that makes sense.
Thanks

 

[DirkStruan]

What happens in the columns between those non-empty columns in your example, i.e. what is in column F,H,I,K,M? Are these blank?


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[SkipVought]

Hi,

It sounds to me like your table is fundamentally flawed in its design.

From a properly designed table, the answer would be a fairly simple lookup.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_[red][/red]

 

[Lukey]

Hi,
Thanks for the prompt replies.
DirkStruan - The other columns have coresponding dates in but I don't really need those.

Skip - Tell me about it, unfortunately it is inherited and it is too old to start changing it...

Any ideas ?

Cheers

 

[Fenrirshowl]

If the in-between columns all have values in them you can use
=Index(E2:U2,CountA(E2:U2))

to return the value in the last completed column - but if there are blanks it will fall over, if the values to the right of the "last" column hold functions returning "" it will fall over.

 

[Lukey]

Unfortunately those columns are only filled in with the date once there is something in the column to the left.

Cheers for that though

 

[DirkStruan]

Ok

Here's a crappy solution:

Copy the sheet / set up another sheet showing the data in the original
Working with this new sheet:
1)Change the titles to the column numbers on the copied data (make sure this is text and not a formula).
2)Delete the columns you don't want to check
3)Perform the concatenate and Match functions on this new range from my earlier post. This give the actual column in the copied data sheet.
4)Perform an Index function along the top row (containing the column numbers of original sheet) to obtain the column where the data resides.
5) On the original sheet, pull this final value through and reference the required range via an Index function on the relevant row.

D

 

[KenWright]

Lukey, following up on Fen's suggestion, if the dates only get filled in when there is data to the left, then is it fair to sya that the cells are truly blank until that point, ie you do not have formulas in there waiting for a value before they return a date?

If so then what he has posted is fine, though you may need a -1 in there, eg;-

=Index(E2:U2,COUNTA(E2:U2)-1)

This does however assume NO blanks to the left of your last entry.

If you DO have blanks in there however, then add a row at the top of your data (or the bottom if you prefer), and in say cells EE2:U2, put the values 1,2,3,4,5.....17 respectively. Now you can use a formula such as:-

=INDEX(E3:U3,,SUMPRODUCT(MAX((E3:U3<>"")*$E$2:$U$2))-1)

copied down as far as necessary.

Probably an easier solution than that floating around as well, but I'm up to my neck in it at work at the moment.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

This does of course assume that you have at least one entry plus date in every row, else I'll need to tweak it to accommodate.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

OK this one will annoy some of the purists, but assuming you have real blanks to the right of your data, then the following should get you what you want:-

=LOOKUP(99^99,F3:U3,E3:T3)

Yes I know the ranges are different by 1 cell, but according to your data that shouldn't matter and it is necessary to ensure you get the right value anyway.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Fenrirshowl]

Hi Ken

I've never seen 99^99 used before. Can you explain how that works as I doubt 3.69E+197 is in F3:U3.

Fen

 

[Lukey]

Genius Ken !
That works a treat. Apologies again for the poor sheet design but it was dumped on me and I hope to get rid of it soon.

Any light on the 99^99 function would be great, at least I'd kind of know what it does...

Thanks again
Luke

 

[KenWright]

9.99999999999999E+307 is the highest value that a cell can contain, so 99^99 is a close approximation and more than adequate for this purpose.

LOOKUP(999^99...

would expect your list to be ordered in ascending order, and is simply doing a match on the 'largest value' that is less than or equal to that amount, and if it isn't found in the range specified it will give you the next value it does find in the list, working backwards from the end. It think this is the next largest because it expects your data to be ordered.

You could actually make it even smaller and just use the following:-

=LOOKUP(REPT("z",255),E3:U3)

or even probably just

=LOOKUP("zzzz",E3:U3)

Regards
Ken.............




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------