=IF('Sheet1'!H2="","",'Sheet1'!H2) formula

[matrix26]

Hi,
I'm using this formula to check if a specific cel reference on a sheet has any data in it and if it does then that is copied to the relevant cell
=IF('Sheet1'!H2="","",'Sheet1'!H2)

How would I amend this so that if it found a blank cell on Sheet1 it filled the relevant cell with the word PAUSE and stopped processing any following lines, I don't want lots of lines with PAUSE?

Is this even possible?

Thanks in advance

 

[Toadstool]

Hi Matrix26,

If this is your Sheet1

Matrix26.xlsx
	H
1	Data
2	Dog
3	Cat
4	Goat
5
6
7
8
9
Sheet1

Then this would do as you ask

Matrix26.xlsx
	H
1	Pull Data
2	Dog
3	Cat
4	Goat
5	PAUSE
6
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=CHOOSE(MIN(3,COUNTIF(Sheet1!$H$2:$H2,"")+1),Sheet1!H2,"PAUSE","")

 

[matrix26]

Hi Matrix26,

If this is your Sheet1

Matrix26.xlsx
	H
1	Data
2	Dog
3	Cat
4	Goat
5
6
7
8
9
Sheet1

Then this would do as you ask

Matrix26.xlsx
	H
1	Pull Data
2	Dog
3	Cat
4	Goat
5	PAUSE
6
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=CHOOSE(MIN(3,COUNTIF(Sheet1!$H$2:$H2,"")+1),Sheet1!H2,"PAUSE","")

This is both my sheets.

Sheet 1 user input is in columns B and D. This input can vary, as can the number of rows of data entered.
Column H on sheet 1 has this =IF(B2="","",CONCATENATE(A2,(B2),(C2),(D2),(E2),(F2)))

Sheet 2, from cell F2 should pull the data from Sheet1 column H until it finds a blank cell then it should fill that cell with the word PAUSE.

Would the above still work?

 

[Toadstool]

I can't answer that as I don't know what your .bat icon does with cells containing a formula.

 

[matrix26]

I can't answer that as I don't know what your .bat icon does with cells containing a formuThe .bat

 

[matrix26]

Hi,

.bat will transfer an IOS file from a server location on to a device.

Technically I don't really need the PAUSE command at the end.
I'd just like to have it so the user can see the transfers have completed.

 

[matrix26]

=IF(ISNUMBER(SEARCH("pscp -pw Pa$$word101 -scp L:/stablenet_backups/",Sheet1!H2)),Sheet1!H2,"PAUSE")

Gives me this

:: This batch file will transfer IOS

ECHO OFF

:: TRANSFERRING

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 1 USERNAME@TEST:TEST 1

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 2 USERNAMETEST:TEST 2

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 3 USERNAME@TEST:TEST 3

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 4 USERNAME@TEST:TEST 4

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 5 USERNAME@TEST:TEST 5

pscp -pw Pa$$word101 -scp L:/stablenet_backups/TEST 6 USERNAME@TEST:TEST 6

PAUSE

PAUSE

PAUSE

PAUSE

PAUSE

PAUSE

PAUSE

PAUSE

How do I get rid of the all the PAUSES after the 1st one?

 

[hrayani]

see if this works

=IF(OR(H1="PAUSE",H1=""),"",IF(ISNUMBER(SEARCH("pscp -pw Saturd@y101 -scp L:/stablenet_backups/",Sheet1!H2)),Sheet1!H2,"PAUSE"))

EDIT

I do not see any problem in the formula which @Toadstool provided in POST # 2

What is wrong in that ????

 

[matrix26]

see if this works

=IF(OR(H1="PAUSE",H1=""),"",IF(ISNUMBER(SEARCH("pscp -pw Saturd@y101 -scp L:/stablenet_backups/",Sheet1!H2)),Sheet1!H2,"PAUSE"))

EDIT

I do not see any problem in the formula which @Toadstool provided in POST # 2

What is wrong in that ????

Hi Matrix26,

If this is your Sheet1

Matrix26.xlsx
	H
1	Data
2	Dog
3	Cat
4	Goat
5
6
7
8
9
Sheet1

Then this would do as you ask

Matrix26.xlsx
	H
1	Pull Data
2	Dog
3	Cat
4	Goat
5	PAUSE
6
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=CHOOSE(MIN(3,COUNTIF(Sheet1!$H$2:$H2,"")+1),Sheet1!H2,"PAUSE","")

GENIUS, THANK YOU

 

[matrix26]

I can't answer that as I don't know what your .bat icon does with cells containing a formula.

Just realised what you meant.

The excel doc shows the following