-1

I have a spreadsheet that acts as a drawing register.

The first 5 columns make up the drawing name/number. I place the revision number of the drawing in each subsequent column. Some of these columns appear blank, but have formulas for other functions in the worksheet.

I would like the first column after the drawing name/number (col F in this case) to display the latest revision number.

Essentially this means I need a formula (or VBA code) to search for the last non-empty cell in a row, but ignoring cells that have formulas in them (that appear blank).

Find the last not empty row in a range of cells holding a formula is very similar but refers to the last row in a column (I am looking for the last column in a row). I couldnt adapt it. I think my syntax was incorrect.

To mention a few extra points:

  1. The revisions are alphanumeric
  2. The revisions are in pairs (as each drawing has a status code and revision, both of which are alphanumeric) and in the first 2 columns (F and G) I need to show the latest status and revision respectively. The status and revision are placed in adjacent columns on the same row each time. So in reality I need a formula that will return the last 2 non-empty columns and the formula needs to ignore cells that contain formulas that return a blank value.
  3. I have tried various combinations of Lookup (similar to Ron's response below), index and VBA code as shown in the above link. If possible I would like to keep it as a formula but I am not adverse to using VBA.
  4. The formula only needs to apply to an individual row, but needs to be copied down as each separate drawing is on a new row.
Community
  • 1
  • 1
shay
  • 3
  • 2

2 Answers2

0

Please try (in F1 and copied down to suit):

=INDEX(G1:Z1,MATCH(1E+100,G1:Z1))

Z may be increased if you expect to require more columns.

Formulae that return blanks should be ignored by the above.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • thanks, this only seems to work for alpha or numeric. i need one that works for alphanumeric. i have updated the question to confirm this parameter. – shay Oct 05 '18 at 22:08
0

If the revision number might be either text or numeric, you can try:

=LOOKUP(2,1/(LEN($G5:$XFD5)>0),$G5:$XFD5)
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • this formula works well until i get to my first blank cell. once i locate my target range past a blank cell it ignores the values before the blank cell. i am not sure why, i have tried various versions of this formula. is there an edit to this formula that could work? (note when i say blank cell, some cells are empty and others have formulas that return blank values). an alternative would be if i could assign multiple ranges to this formula - i.e. just select the cells that will be populated with alphanumeric info? i cannot seem to break up the target range, only select a large single one – shay Oct 05 '18 at 22:14
  • @shay I don't understand. The formula is designed to find the last cell in a row that is not blank. (Where blank means `LEN(cell_displayed_contents)=0`, which will be the case whether the blank is due to nothing in the cell, or a formula displaying nothing). If that is not what you want, you will need to clarify and provide an example by editing your original question. Please see [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld Oct 05 '18 at 22:46
  • Ron, you are of course correct. my 'blank cells' were actually returning a value of zero, and i had turned off the 'display zero value' in worksheet settings. once i updated my formulas your proposal worked perfectly. Do you have a version of this formula that will do exactly the same but return the second last cell - i.e. the cell value immediately before the last non-blank cell? just to note (if it helps), the second last cell will always be non-blank. Much appreciated. – shay Oct 06 '18 at 08:01
  • @shay You would merely offset `result_vector` by one. For example: `=LOOKUP(2,1/(LEN($G5:$XFD5)>0),$F5:$XFC5)` – Ron Rosenfeld Oct 06 '18 at 10:45