# Posts by jindon

morsejon

MAX(IF(Sheet1!A\$1:A1<>"",ROW(A\$1:A1)))

Since Sheet!A\$1 and ROW(A\$1 are fixed, they won't change as you go down the row, but A1s change, increase by 1 as you go down.
Formula above returns the corresponding row number out of 1:1 (ROW(A\$1:A1)) is equal to maximum row which contains any value within the range of A1 to A and current row on Sheet1.

Therefore, if you need to modify above portion, you need to match dynamic part of range which is A1 part to the first refernce row address.
In case of A7, the portion is like
MAX(IF(Sheet1!A\$1:A7<>"",ROW(A\$1:A7)))
then, in any cell on Sheet2
=IF(Sheet1!A7<>"",Sheet1!A7,INDEX(Sheet1!A\$1:A7,MAX(IF(Sheet1!A\$1:A7<>"",ROW(A\$1:A7))),1))

Or, is this easier to understand?
=IF(Sheet1!A7<>"",Sheet1!A7,INDIRECT("Sheet1!A"&MAX(IF(Sheet1!A\$1:A7<>"",ROW(A\$1:A7)))))
Good luck

Jindon

Hi,

Sounds like

=SUMPRODUCT((Sheet!C1:C1000=Sheet2!A1)*(Sheet3!F1:F1000="sick"))

Jindon

1) I used column L in "People in Shift" sheet as working column to find rank if cells in column A has value
[INDENT]e.g. If A8 is Brenda, L8 finds Brenda's rank from Ranking Sheet which is 5.
Each cell up to L23 does the same for corresponding A value.
And I made the column invisible setting font colour with white.[/INDENT]

2) Cells in Column F then find the smallest value within column L and display corresponding value in column G in Ranking sheet.
Actually, I'd like to cahnge the formula for column F as shown below
[INDENT]Cell F8:
=IF(A8="","",INDEX(Ranking!G:I,MATCH(SMALL(L:L,ROW(A1)),Ranking!I:I,0),1))
Then just Enter instead of Ctrl+Shift+Enter.
SMALL(L:L,ROW(A1))
ROW(A1) is the substitute of 1 and it increases as you drag down, therefore if you go next row, it will be the second smallest and so forth.
ROW(A1), therefore, nothing to do with actual cell A1.[/INDENT]

Jindon

Hi, mosejon

I hope the formula below does good for you

In cell A1 of the Sheet2

=IF(Sheet1!A1<>"",Sheet1!A1,INDEX(Sheet1!A\$1:A1,MAX(IF(Sheet1!A\$1:A1<>"",ROW(A\$1:A1))),1))

Ctrl + Shift and Enter to get out from the formula bar.
Then drag down as you want.

Jindon

Hi, ComaGlove

I hope the following set of formula will help you to do what you want.
If you have your sentence in Col A then
B1:
=IF(COUNTA(A1),LEFT(A1,FIND(" ",A1,1)-1),"")
C1:
=IF(COUNTA(A1),RIGHT(A1,LEN(A1)-LEN(B1)-1),"")

Jindon

The formula I used is formula array, you need to get out of the formular bar by holding down Ctrl + Shift keys and press Enter after you paste the following:

Formula in cell E2

=IF(COUNTIF(\$A\$1:\$A\$6,\$E\$1)>=ROW(A1),INDEX(\$A\$1:\$C\$6,SMALL(IF(\$A\$1:\$A\$6=\$E\$1,ROW(\$A\$1:\$A\$6),""),ROW(A1)),COLUMN(A1)),"")

Good luck!

Jindon

## Files

I hope the attached is what you want in the second sheet, but I don't understand how you want the data to be appeared on the third sheet.

Jindon

## Files

Hi, Eagle

I wonder if the formula right?

I'm using comma, NOT SEMICOLON, as a separater like:
=VLOOKUP(A2,\$L\$2:\$M\$554,2,FALSE)

If it doesn't help, would you check the cell format?
When you set "Text" on that cell, it looks like that.
You need to set it back to General and also go back to the cell and enter once.
I hope that will give you the right answer

Jindon

Hi, brerigo

This is how it works: =INDIRECT("A"&ROUNDUP(ROW(A1)/\$C\$1,0))
1) Function INDIRECT gives you the value of the cell you named.
e.g. If you put =INDIRECT("A1") in any cell, you can get the value of the cell A1
2) ROUNDUP(ROW(A1)/\$C\$1,0) part is the key.
ROW(A1) is in substitute for 1, and it increases as you fill down.
Assume 3 entered in cell C1. It gives you the calculation of ROUNDUP OF 1/3 with no decimal.
If you put that part of the fomula =ROUNDUP(ROW(A1)/\$C\$1,0), you can get 1 as a result. So, simply, if you want to start the duplicate data from the second row, put the given formula in the second row and it starts from there.

3) If the original data has heading in the first row, you need to add 1 in that part
That is ROUNDUP(ROW(A1)/\$C\$1,0)+1
So the formula looks like =INDIRECT("A"&ROUNDUP(ROW(A1)/\$C\$1,0)+1)

I hope you can now manage it as you like.

Jindon

Following is the way to do it with the formula

1) Assuming original data begins from cell A1
2) Enter the number that you want to duplicate in cell C1
3) Enter following formula in first row in any column
=INDIRECT("A"&ROUNDUP(ROW(A1)/\$C\$1,0))
and then fill down as you want the data