I have a spreadsheet that is using VLOOKUP. I type a number in a cell and then 6 numbers fill in the cells to the right of the place where you type in a number. A person will be able to enter numbers 0- 40.

My problem is that once I get past #11 it returns a #N/A. I am sure the fix is simple, but I have been looking at this all morning and I cannot find the error. Then all of a sudden I also had an issue with my first line for some reason. I typed #7 in there and it also returned the #N/A; however, when I typed #7 in another cell it worked jut fine.

I am really only concerned about being able to get all 40 numbers to work. I am also only going to use five cells straight down in a column so they will not be going down a column from 0 - 40. After the spreadsheet is up and running I want to put in an Access database so they will only see those five cells.

I have attached a copy of the spreadsheet that I am currently working with. Any help you can give me would be GREATLY appreciated.

Thanks in advanced for your help.

My problem is that once I get past #11 it returns a #N/A. I am sure the fix is simple, but I have been looking at this all morning and I cannot find the error. Then all of a sudden I also had an issue with my first line for some reason. I typed #7 in there and it also returned the #N/A; however, when I typed #7 in another cell it worked jut fine.

I am really only concerned about being able to get all 40 numbers to work. I am also only going to use five cells straight down in a column so they will not be going down a column from 0 - 40. After the spreadsheet is up and running I want to put in an Access database so they will only see those five cells.

I have attached a copy of the spreadsheet that I am currently working with. Any help you can give me would be GREATLY appreciated.

Thanks in advanced for your help.

I am trying to copy and paste cells from one sheet to antoher (withn the same workbook), the cells I am copying are being pasted in a different sheet but not in the same solumn set up

copy from - copy to

sheet1 a1- Sheet2 a1

Sheet1 b1- Sheet2 b1

Sheet1 c1- Sheet2 e1

Sheet1 e1- Sheet2 h1

Sheet1 f1-Sheet2 i1

When I click on the first cell where I am pasting in sheet2 I type =sheet1 a1. I can then copy the adjacent cell (b and c - down as many rows I need), but how can I get the non-consecutive cells to populate without having to go back and forth to sheet one, copy the other data (which I would have to do twice as there is a column in between that I do not need the data from.

I have tried to attach a file to show what I am trying to do. I hope it uploads

The file will be used with excel 2003.

copy from - copy to

sheet1 a1- Sheet2 a1

Sheet1 b1- Sheet2 b1

Sheet1 c1- Sheet2 e1

Sheet1 e1- Sheet2 h1

Sheet1 f1-Sheet2 i1

When I click on the first cell where I am pasting in sheet2 I type =sheet1 a1. I can then copy the adjacent cell (b and c - down as many rows I need), but how can I get the non-consecutive cells to populate without having to go back and forth to sheet one, copy the other data (which I would have to do twice as there is a column in between that I do not need the data from.

I have tried to attach a file to show what I am trying to do. I hope it uploads

The file will be used with excel 2003.

If i have two columns, cost and schedule risk, with values "low", "moderate", "high" is there a way to use a formula to extract the highest risk from those two columns?

class 1 25% Limit Min class 1 Min class 2 Min class 3

class 2 50% 250,000 1,350 1,575 1,800

class 3 25% 500,000 1,800 2,125 2,500

1,000,000 2,250 2,600 3,375

2,000,000 2,600 3,000 4,050

Limit 250,000 3,000,000 2,880 3,375 7,500

min ????? Amount 6,000 4,000,000 4,900 6,500 6,250

5,000,000 4,320 5,400 9,900

6,000,000 6,500 8,100 8,400

7,000,000 5,760 7,200 11,000

8,000,000 7,200 9,000 13,350

9,000,000 8,500 10,600 15,500

10,000,000 9,650 12,000 17,650

-----------------------------------------------------------------------------------------------------------------

If you cannot view please look at the attached

Essentially what I am trying to do is create a logic which would return the highest min class value(from the table). However this depends on whether class 1/2/3 are filled(top left), for example as the limit entered is 250,000, then we must look towards the first row, and since class 1,2,3 all have values in them, then i wish to return min class 3 (1800)

BUT

if it were to be

CLASS 1 50%

CLASS 2 50%

CLASS 3 0%

then i wish to return min class 2 (1575)

Please do look at the attached class 2 50% 250,000 1,350 1,575 1,800

class 3 25% 500,000 1,800 2,125 2,500

1,000,000 2,250 2,600 3,375

2,000,000 2,600 3,000 4,050

Limit 250,000 3,000,000 2,880 3,375 7,500

min ????? Amount 6,000 4,000,000 4,900 6,500 6,250

5,000,000 4,320 5,400 9,900

6,000,000 6,500 8,100 8,400

7,000,000 5,760 7,200 11,000

8,000,000 7,200 9,000 13,350

9,000,000 8,500 10,600 15,500

10,000,000 9,650 12,000 17,650

-----------------------------------------------------------------------------------------------------------------

If you cannot view please look at the attached

Essentially what I am trying to do is create a logic which would return the highest min class value(from the table). However this depends on whether class 1/2/3 are filled(top left), for example as the limit entered is 250,000, then we must look towards the first row, and since class 1,2,3 all have values in them, then i wish to return min class 3 (1800)

BUT

if it were to be

CLASS 1 50%

CLASS 2 50%

CLASS 3 0%

then i wish to return min class 2 (1575)

Thanks

I want to find the sum of several columns based on the criteria in two other columns. I have this formula that returns the sum of two columns, based on two criteria but want to include two more columns in the sum.

=SUM(IF('Entry Sheet'!$E$18:$E$517='Entry Sheet'!W52,IF('Entry Sheet'!$F$18:$F$517=$N$9,IF('Entry Sheet'!$M$18:$M$517>0,'Entry Sheet'!$M$18:$M$517),0),0),0

Basically, it is saying If column E on an entry sheet = a certain code in column F (F holds a formula pointing to W code held on another page) and if the value of column N and Column M is >0, then put the sum of columns N and M together the second page in column D. This long formula sits in column D on the second page.

I want to include two more columns K and L into the sum. I've tried all kinds of things and not getting anywhere.

=SUM(IF('Entry Sheet'!$E$18:$E$517='Entry Sheet'!W52,IF('Entry Sheet'!$F$18:$F$517=$N$9,IF('Entry Sheet'!$M$18:$M$517>0,'Entry Sheet'!$M$18:$M$517),0),0),0

Basically, it is saying If column E on an entry sheet = a certain code in column F (F holds a formula pointing to W code held on another page) and if the value of column N and Column M is >0, then put the sum of columns N and M together the second page in column D. This long formula sits in column D on the second page.

I want to include two more columns K and L into the sum. I've tried all kinds of things and not getting anywhere.

Hi All - Really need your help!

In the attached spreadsheet, I am trying to write a formula for each cell in the highlighted area so that each row does not exceed the value in column "C" and that each column does not exceed the value in row 18. The trick is, as you move to the right, the cell value needs to be contingent upon the previous cells' values in both the row and column AND must "deplete" itself in a first-in-first-out inventory concept. I have hardcoded the correct values in the spreadsheet so you can see what I mean.

I have worked to figure something out for hours. Does anyone think they can help?

Thanks!

In the attached spreadsheet, I am trying to write a formula for each cell in the highlighted area so that each row does not exceed the value in column "C" and that each column does not exceed the value in row 18. The trick is, as you move to the right, the cell value needs to be contingent upon the previous cells' values in both the row and column AND must "deplete" itself in a first-in-first-out inventory concept. I have hardcoded the correct values in the spreadsheet so you can see what I mean.

I have worked to figure something out for hours. Does anyone think they can help?

Thanks!

Hi,

First timer here.

I'm trying to filter a spreadsheet by comparing text in 2 cells and have excel return text in another cell.

For example:

If "any" of the text in C5 is found in D5 then I'd like C6 to return text "Complete"

ie: C5="Account Number" - D5="Account Number Validated" - so C6 should now show "Complete"

However, if D5 shows "Not Valid" - C6 should return "Incomplete"

The text in either of the cells may contain some or all of the same text - but usually if C5 (for example) contains ONE or more of the same words in D5, the answer would be positive ("Complete").

I can use =ISNUMBER(SEARCH(C5,D6)) to return "TRUE" or FALSE" but would like something more relevant to my work.

Hope all this makes sense?

Thanks.

]]>First timer here.

I'm trying to filter a spreadsheet by comparing text in 2 cells and have excel return text in another cell.

For example:

If "any" of the text in C5 is found in D5 then I'd like C6 to return text "Complete"

ie: C5="Account Number" - D5="Account Number Validated" - so C6 should now show "Complete"

However, if D5 shows "Not Valid" - C6 should return "Incomplete"

The text in either of the cells may contain some or all of the same text - but usually if C5 (for example) contains ONE or more of the same words in D5, the answer would be positive ("Complete").

I can use =ISNUMBER(SEARCH(C5,D6)) to return "TRUE" or FALSE" but would like something more relevant to my work.

Hope all this makes sense?

Thanks.

We have employees at multiple sites.

Currently we have a schedule that spans a week for each site that auto totals hours worked.

I would like to make a Single Doc with a sheet for each site that checks each sheet for the employees name then adds their total hours from each site to their true total.

This will help my on duty supervisors quickly identify which employees are low on hours when seeking coverage for callouts and prevent overtime.

I also will make each shift auto total the time worked based on start and end, ,total the weekly hours worked for each employee and the site etc. Pretty sure I can manage that though.

If I can get a few pointers on how to create the formula criteria for totaling the hours then I can figure it out/apply it accross the board from there. I just got lost making my first attempt and now I have no idea where to start.

I attached the spreadsheet in question.

Currently we have a schedule that spans a week for each site that auto totals hours worked.

I would like to make a Single Doc with a sheet for each site that checks each sheet for the employees name then adds their total hours from each site to their true total.

This will help my on duty supervisors quickly identify which employees are low on hours when seeking coverage for callouts and prevent overtime.

I also will make each shift auto total the time worked based on start and end, ,total the weekly hours worked for each employee and the site etc. Pretty sure I can manage that though.

If I can get a few pointers on how to create the formula criteria for totaling the hours then I can figure it out/apply it accross the board from there. I just got lost making my first attempt and now I have no idea where to start.

I attached the spreadsheet in question.

Excel users,

I am surprised that Excel 2010 does not have an isformula function. Is there an alternative way to test for a formula without using code or user defined functions?

]]>I am surprised that Excel 2010 does not have an isformula function. Is there an alternative way to test for a formula without using code or user defined functions?

Hi

I've just received a .csv file with cells containing data in the following format:

Is it possible to extract just the date from this format? ie 12/12/2017

Regards

Mike

PS This is in Excel 365 using Windows 10

]]>I've just received a .csv file with cells containing data in the following format:

2017-12-12T00:51:05Z |

Regards

Mike

PS This is in Excel 365 using Windows 10

Hi,

If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example

John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30

excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.

Please help how to highlight if there is a clash

]]>If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example

John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30

excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.

Please help how to highlight if there is a clash

I have a formula question for an aged care spreadsheet: based on 3 criteria (1. Marital status? 2. are both (if a couple) in aged care?, 3. do they own their own home?)

Row 9 has Single in col b Single in col c Couple in D Couple in E Illness Separated Couple in F Illness Separated Couple in G

Row 10 has Yes in B, No in C, Yes in D, No in E, Yes in F, and No in G.

Row 11 has Yes in each column from B thru F. My formula caters for non-homeowners as you will see.

I have the following formula in cell b13

=IF(B11="no",0,(IF(AND(B9 ="single",AND(B10="Yes")),21,IF(AND(B9="Single",AND(B10="No")),22,IF(AND(B9 ="Couple",AND(B10="Yes")),31,IF(AND(B9="Couple",AND(B10="No")),32,IF(AND(B9 ="Illness Separated Couple",AND(B10="Yes")),41,IF(AND(B9="Illness Separated Couple",AND(B10="No")),42))))))))

When I drag the formula across row 13, from row b thru to G, I get the following values.

21 FALSE 31 32 41 42

Why do I get "FALSE" in c13 when the other answers are numerical and seem correct?

]]>Row 9 has Single in col b Single in col c Couple in D Couple in E Illness Separated Couple in F Illness Separated Couple in G

Row 10 has Yes in B, No in C, Yes in D, No in E, Yes in F, and No in G.

Row 11 has Yes in each column from B thru F. My formula caters for non-homeowners as you will see.

I have the following formula in cell b13

=IF(B11="no",0,(IF(AND(B9 ="single",AND(B10="Yes")),21,IF(AND(B9="Single",AND(B10="No")),22,IF(AND(B9 ="Couple",AND(B10="Yes")),31,IF(AND(B9="Couple",AND(B10="No")),32,IF(AND(B9 ="Illness Separated Couple",AND(B10="Yes")),41,IF(AND(B9="Illness Separated Couple",AND(B10="No")),42))))))))

When I drag the formula across row 13, from row b thru to G, I get the following values.

21 FALSE 31 32 41 42

Why do I get "FALSE" in c13 when the other answers are numerical and seem correct?

Hello Forum

I have the following Table, it is to organize private classes.

Classify the students by Name (by colors I did not know how to achieve it) and € / Hours

I have the other table that is Week and where depending on the time I indicate the name and knowing the price (€ / h), it tells me some values per day, hours and even totals in that week.

My doubt is that I would like to be able to change the number of the weeks automatically but that I respect when returning to previous weeks the schedules with their letters.

For example

If I have a total of 30 weeks

If I write 4 then I would see the classes of Week 4 (for example: 5h Pablo, 4h Fernando, 13h Cristina)

If I write 6 then I would see the classes of Week 6 (for example: 3h Fernando, 3h Cristina)

Can anybody help me?

Thank you.TABLA HORARIOS.xlsx

I have the following Table, it is to organize private classes.

Classify the students by Name (by colors I did not know how to achieve it) and € / Hours

I have the other table that is Week and where depending on the time I indicate the name and knowing the price (€ / h), it tells me some values per day, hours and even totals in that week.

My doubt is that I would like to be able to change the number of the weeks automatically but that I respect when returning to previous weeks the schedules with their letters.

For example

If I have a total of 30 weeks

If I write 4 then I would see the classes of Week 4 (for example: 5h Pablo, 4h Fernando, 13h Cristina)

If I write 6 then I would see the classes of Week 6 (for example: 3h Fernando, 3h Cristina)

Can anybody help me?

Thank you.TABLA HORARIOS.xlsx

Hi Guys, long story short it’s been a long time since I’ve had to use a string of IF statements (probably college). I’ve had an unsuccessful crack at the below but think I just need to brush up on my excel skills in general. In the mean time I would be really grateful if someone might be able to help.

Essentially, what I want is for the below model to:

$F$5/4 & IF(H2>$E$4, H4=0, "") - If current date (feeding from H2) is greater than the lease expiry date (E4) then the value of the subject cell(H4-AA4) must equal zero.

$F$5/4 & IF(H2>$D$4, H4=0, "") - If current date (feeding from H2) is greater than the lease break date (D4) then the value of the subject cell(H4-AA4) must equal zero.

$F$5/4 & IF(H2=0, "VOID", "") - If the subject cell equals 0 then display "VOID" if not then no effect.

& IF(D4:G4="VOID", $F$5/4, "") If there are (# per G4's input) x "VOID" periods preceding the subject cell, then the subject cell should revert to $F$5/4 if not then no effect.

I’m not sure if this is even possible, thanks in advance.