My area is A1:M60. I have 10 sets of 6 rows within that area with only 2 row from each set actually being searched (its ok to search entire area though as there will never be a match in the other rows). I am trying to search the area for the first instance of a reference cell and return the value of the cell that is 2 cells above it. I thought I could do it with an OR() and a series of HLOOKUPs but that didn't work. The result is always "0". Also, it looked really bulky for this function. Here is the function I have now:

=OR(HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$7:$9, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$13:$15, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$19:$21, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$25:$27, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$31:$33, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$37:$39, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$43:$45, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$49:$51, 1, FALSE))

Any help would be greatly appreciated.

]]>=OR(HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$7:$9, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$13:$15, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$19:$21, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$25:$27, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$31:$33, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$37:$39, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$43:$45, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$49:$51, 1, FALSE))

Any help would be greatly appreciated.

For example:

Cell A1 has:

Peter is walking his dog

Cell B1 has:

walking his dog

preferably how can I remove the "walking his dog" from cell A1

or to create cell C1 to read "Peter is"

]]>Cell A1 has:

Peter is walking his dog

Cell B1 has:

walking his dog

preferably how can I remove the "walking his dog" from cell A1

or to create cell C1 to read "Peter is"

Hi, I have a spreadsheet with thousands of product codes, and corresponding data of stock on hand and weekly orders.

I would like to match the product codes with each other to automatically align, in each row, the data of stock on hand and orders.

Screen Shot 2018-08-20 at 7.50.55 am.png

For example column C has the product codes for the spreadsheet, while column K and L include the copied and pasted stock on hand values.

I would like to align, say the BPLOBL value in K, and its 15 value, with the BPLOBL in Column C.

Screen Shot 2018-08-20 at 7.59.52 am.png

From what I gather, I need to be using a combination of the INDEX and MATCH functions but I cant get the syntax right!!

Thanks for your help!

]]>I would like to match the product codes with each other to automatically align, in each row, the data of stock on hand and orders.

Screen Shot 2018-08-20 at 7.50.55 am.png

For example column C has the product codes for the spreadsheet, while column K and L include the copied and pasted stock on hand values.

I would like to align, say the BPLOBL value in K, and its 15 value, with the BPLOBL in Column C.

Screen Shot 2018-08-20 at 7.59.52 am.png

From what I gather, I need to be using a combination of the INDEX and MATCH functions but I cant get the syntax right!!

Thanks for your help!

Hi,

Hope this makes sense

I'm trying to get a formula in column B on worksheet 2 that will look for "B" in a range from Y6 to DX73 on worksheet 1, and if "B" is found, use the value in column B of the found "B" row in worksheet 1 to populate worksheet 2 column B. There would be blank, D, and N cells within the above range to ignore.

Thanks for any help

Hope this makes sense

I'm trying to get a formula in column B on worksheet 2 that will look for "B" in a range from Y6 to DX73 on worksheet 1, and if "B" is found, use the value in column B of the found "B" row in worksheet 1 to populate worksheet 2 column B. There would be blank, D, and N cells within the above range to ignore.

Thanks for any help

Hello,

I want to copy a formula down skipping 3 cells each time, as follows:

=IF(OR(ISBLANK(Sheet1!D10),ISBLANK(Sheet1!E10)),"",(Sheet1!D10+Sheet1!E10)-(Sheet1!$D$7+Sheet1!$E$7))

=IF(OR(ISBLANK(Sheet1!D13),ISBLANK(Sheet1!E13)),"",(Sheet1!D13+Sheet1!E13)-(Sheet1!$D$7+Sheet1!$E$7))

=IF(OR(ISBLANK(Sheet1!D16),ISBLANK(Sheet1!E16)),"",(Sheet1!D16+Sheet1!E16)-(Sheet1!$D$7+Sheet1!$E$7))

It will not skip the 3 cells each time down unless I manually type all of them in. Instead it just goes in order like from 10 to 11 to 12. I have to do this to a lot of cells. Please help.

]]>I want to copy a formula down skipping 3 cells each time, as follows:

=IF(OR(ISBLANK(Sheet1!D10),ISBLANK(Sheet1!E10)),"",(Sheet1!D10+Sheet1!E10)-(Sheet1!$D$7+Sheet1!$E$7))

=IF(OR(ISBLANK(Sheet1!D13),ISBLANK(Sheet1!E13)),"",(Sheet1!D13+Sheet1!E13)-(Sheet1!$D$7+Sheet1!$E$7))

=IF(OR(ISBLANK(Sheet1!D16),ISBLANK(Sheet1!E16)),"",(Sheet1!D16+Sheet1!E16)-(Sheet1!$D$7+Sheet1!$E$7))

It will not skip the 3 cells each time down unless I manually type all of them in. Instead it just goes in order like from 10 to 11 to 12. I have to do this to a lot of cells. Please help.

Hi

I have a table which has 7 rows in it. I want to apply conditional formatting to it for 3 different formulas that show a RAG status.

So if a value in a cell in Row 2 has a low value it is red - a medium value, amber and a high value green.

Then I want this check repeated for Row 3 etc

I have tried to set up Conditional formatting for all rows. but without creating 3 rules for each row, I cant see how to make my formula clever enough to know that it is on Row 5 so only look at a cell in Row 5 - At present they all look at a cell in Row 2, so when that is changed all RAGs for al Rows change at once.

Any help would be appreciated.

Rob

I have a table which has 7 rows in it. I want to apply conditional formatting to it for 3 different formulas that show a RAG status.

So if a value in a cell in Row 2 has a low value it is red - a medium value, amber and a high value green.

Then I want this check repeated for Row 3 etc

I have tried to set up Conditional formatting for all rows. but without creating 3 rules for each row, I cant see how to make my formula clever enough to know that it is on Row 5 so only look at a cell in Row 5 - At present they all look at a cell in Row 2, so when that is changed all RAGs for al Rows change at once.

Any help would be appreciated.

Rob

Hi folks.

I am struggling a little with a plan I am trying to produce. It is a 52 week planner, ultimately for a period of up 10 years... for now, I will keep it simple and look at providing the example for just 52 weeks.

In the attached example I have managed to successfully put the number of minutes in the correct Week based on the 'Next Due Date' indicated in column J.

I believe I need to use an OR statement, as well as the IF now as what I would also like to do is show the same number of minutes based on the repetition of the item, based on the Pitch Days shown in column K.

For example, Row 7 shows a repeat of every 30 days, so ideally I need '5' to appear on the next due date (03/09/2018) which it is , PLUS I need to show it every 30 days after that.

I really am lost and wondering if the 'IF OR' approach is the right one or not? I am trying to avoid VBA at this point if at all possible!

Note, blank columns are left in as this is dummy data which I can then plug the formula back in to my correct worksheet!

All guidance gratefully received. Please let me know if I need to provide any further information.

I am struggling a little with a plan I am trying to produce. It is a 52 week planner, ultimately for a period of up 10 years... for now, I will keep it simple and look at providing the example for just 52 weeks.

In the attached example I have managed to successfully put the number of minutes in the correct Week based on the 'Next Due Date' indicated in column J.

I believe I need to use an OR statement, as well as the IF now as what I would also like to do is show the same number of minutes based on the repetition of the item, based on the Pitch Days shown in column K.

For example, Row 7 shows a repeat of every 30 days, so ideally I need '5' to appear on the next due date (03/09/2018) which it is , PLUS I need to show it every 30 days after that.

I really am lost and wondering if the 'IF OR' approach is the right one or not? I am trying to avoid VBA at this point if at all possible!

Note, blank columns are left in as this is dummy data which I can then plug the formula back in to my correct worksheet!

All guidance gratefully received. Please let me know if I need to provide any further information.

Hello,

I created a scenario summary and in the Result Cells column, all I get is a cell reference.

Is there any way I can do a look up of that cell number located in the other tab called "PPM3..." so that the "Defined Name" of that cell in "PPM3" displays where I wrote the "?" in the "Scenario Summary" tab??

I've been searching the web for days, no luck, maybe I am not asking the right question, but I think it should be doable, right?

I also downloaded the Kutools toolbar but I don't think that would help.

PS: I don't know how to do macros, so if your solution involves a macro, please keep that in mind when explaining to me. I am willing to try anything as long as the steps are clear :-)

Thanks so much in advance!

Laure

scenario excel.JPG

]]>I created a scenario summary and in the Result Cells column, all I get is a cell reference.

Is there any way I can do a look up of that cell number located in the other tab called "PPM3..." so that the "Defined Name" of that cell in "PPM3" displays where I wrote the "?" in the "Scenario Summary" tab??

I've been searching the web for days, no luck, maybe I am not asking the right question, but I think it should be doable, right?

I also downloaded the Kutools toolbar but I don't think that would help.

PS: I don't know how to do macros, so if your solution involves a macro, please keep that in mind when explaining to me. I am willing to try anything as long as the steps are clear :-)

Thanks so much in advance!

Laure

scenario excel.JPG

Hi all,

I'm hoping for your great help once again. Ive added attachement with a txt box of what i am trying to achieve.

but it is looking through 4 columns for 1 key word in each column and sum of matching all 4 words.

I'm hoping for your great help once again. Ive added attachement with a txt box of what i am trying to achieve.

but it is looking through 4 columns for 1 key word in each column and sum of matching all 4 words.

I have column A with quantity 20 .EA,20PKS the data is split in column B and C as column B and column C EA OR pks .

I have a new qty as 1020 UVC OR 5 UVC OR 100 UVC

Can someone help how to adjust formula to accept the split the nunber and uvc for both column B and C

COLUMN B FORMULA

COLUMN C FORMULA

I have a new qty as 1020 UVC OR 5 UVC OR 100 UVC

Can someone help how to adjust formula to accept the split the nunber and uvc for both column B and C

COLUMN B FORMULA

Code:

`=--SUBSTITUTE(LEFT(A2,FIND(".",A2)-1)," ","")`

Code:

=TRIM(MID(A2,-LOOKUP(1,-MIN(SEARCH({"EA","PKS"},A2&"EAPKS"))),3))

I'm trying to work out a formula to take the dates (results from formuls in short date format) from 2 ranges and list them in a 3rd in date order.

I am trying to automate a process and dont want to use sort function or VBA, any suggestions as to how this can be achieved?

The dates are mid column, ony 10 rows (2 columns) and there is data in the same rows in other colums. The sheet is at work and they restrict this site, I hae done a lot of complicated stuff to get to this point and cant remember how to replicaye it so I can't really post a sample sheet before anyone asks.

]]>I am trying to automate a process and dont want to use sort function or VBA, any suggestions as to how this can be achieved?

The dates are mid column, ony 10 rows (2 columns) and there is data in the same rows in other colums. The sheet is at work and they restrict this site, I hae done a lot of complicated stuff to get to this point and cant remember how to replicaye it so I can't really post a sample sheet before anyone asks.

Good morning!

I have a list with some numbers (1.11 , 1.12, 1.13, 1.14, etc.) and a top 5 tabel

I want to pick some numbers from the list in the top 5 tabel by some conditions like... the numbers should be between 1.55 and 2.8 ... this is the first condition

and the second one, the product of the top 5 tabel numbers to be between (50 and 150 for example) ...

Is there any posibility to do this in excel ?

Have a nice day !

I have a list with some numbers (1.11 , 1.12, 1.13, 1.14, etc.) and a top 5 tabel

I want to pick some numbers from the list in the top 5 tabel by some conditions like... the numbers should be between 1.55 and 2.8 ... this is the first condition

and the second one, the product of the top 5 tabel numbers to be between (50 and 150 for example) ...

Is there any posibility to do this in excel ?

Have a nice day !

Hello

Someone at my work trying to create excel sheet that do search for employee assigned task and hours and then sum these product together

Example:

Employees Project1 Project 2 Project 3

Tony 8 Check Sheets (8) Check Sheets (4) Check Sheets (8)

Keith 4 Plans (4) Check Sheets (40) Check Sheets (40)

Chris 4 Plans (4)

Assume that tony has assigned 8 hours to check sheets in project #1 and 4 hour for project # 2 and 8 hr for project # 3

He did this formula :

=IF(ISBLANK(INDIRECT("S19")),0,(MID(INDIRECT("S19"),SEARCH("(",INDIRECT("S19"))+1,SEARCH(")",INDIRECT("S19"))-SEARCH("(",INDIRECT("S19"))-1)+0))

However it only give the result of 4 which is project #1. How can he add the hours in Project 1, 2 & 3 using this formula and give me the total of 20?

is there a macro to do that or better formula?

Thanks

]]>Someone at my work trying to create excel sheet that do search for employee assigned task and hours and then sum these product together

Example:

Employees Project1 Project 2 Project 3

Tony 8 Check Sheets (8) Check Sheets (4) Check Sheets (8)

Keith 4 Plans (4) Check Sheets (40) Check Sheets (40)

Chris 4 Plans (4)

Assume that tony has assigned 8 hours to check sheets in project #1 and 4 hour for project # 2 and 8 hr for project # 3

He did this formula :

=IF(ISBLANK(INDIRECT("S19")),0,(MID(INDIRECT("S19"),SEARCH("(",INDIRECT("S19"))+1,SEARCH(")",INDIRECT("S19"))-SEARCH("(",INDIRECT("S19"))-1)+0))

However it only give the result of 4 which is project #1. How can he add the hours in Project 1, 2 & 3 using this formula and give me the total of 20?

is there a macro to do that or better formula?

Thanks

Hi all,

_00100752018 1 10 00

_00101451014 2 10 20

_00101451014 2 20 00

_0968260502B 3 10 20

_0968260502B 3 20 30

_0968260502B 3 30 00

I want to have values shown in column E&F against the no of same items in column A..

ex

1) If cell value A(A2) is one row ; then I want value as 0 & 10 in one row as shown in cell c1,e1

2) If cell value column A(A3) is in two row ; then I want value as 0 & 10 in 1st row & 20 & 0 in second row as shown in cells E2:F3

2) If cell value column A(A4) is in three row ; then I want value as 0 & 10 in 1st row & 20 & 30 in second row & 30&0 in 3rd row as shown in cells C4:E6

Column B is countif to have no of rows item is having

Please guide me how can I get this using formulas in excel

Regards.

]]>_00100752018 1 10 00

_00101451014 2 10 20

_00101451014 2 20 00

_0968260502B 3 10 20

_0968260502B 3 20 30

_0968260502B 3 30 00

I want to have values shown in column E&F against the no of same items in column A..

ex

1) If cell value A(A2) is one row ; then I want value as 0 & 10 in one row as shown in cell c1,e1

2) If cell value column A(A3) is in two row ; then I want value as 0 & 10 in 1st row & 20 & 0 in second row as shown in cells E2:F3

2) If cell value column A(A4) is in three row ; then I want value as 0 & 10 in 1st row & 20 & 30 in second row & 30&0 in 3rd row as shown in cells C4:E6

Column B is countif to have no of rows item is having

Please guide me how can I get this using formulas in excel

Regards.

Hi,

I am doing a time sheet and i want to do some summing up. On the screenshot from the link below the bottom right corner where I have "Total Pay" i want it to sum up the "Total Hours" from left hand bottom corner and automatilcy times it by X.

Can anyone help me with the equation?

Edit 1: As I am new on the forum I am not allowed to post any links or images. Is any one of help anyways?

Kind regards,

Cycu

]]>I am doing a time sheet and i want to do some summing up. On the screenshot from the link below the bottom right corner where I have "Total Pay" i want it to sum up the "Total Hours" from left hand bottom corner and automatilcy times it by X.

Can anyone help me with the equation?

Edit 1: As I am new on the forum I am not allowed to post any links or images. Is any one of help anyways?

Kind regards,

Cycu