Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

**n07cn3** · 10-30-2013, 12:55 AM

Hello,

is there an equation that will populate the "task" for the employee if column C has a numerical value.

for example if employee1 has 8 in cell C2 then D2 = "Task1", E2 = "Task2" and G4 = "Task4" (task are from "task" tab).

Any help would be greatly appreciated.

Please see attach excel book.

Thanks.

**:) Sixthsense :)** · 10-30-2013, 01:12 AM

In C2 Cell of Task Sheet

=IF(SUMPRODUCT((Hours!$A$2:$A$10=$A2)*(Hours!$B$2:$B$10=$B2)*(Hours!D$2:D$10<>"")),"x","")

Drag it down...

**n07cn3** · 10-30-2013, 08:45 AM

Sixthsense,

thanks for your help but the "x" populated in the Task Sheet instead of the Hours sheet.

is there anyway to make "task1, task2 and task4"in Task sheet to populate in row2 of hours sheet if c2 has a numerical value?

thanks again for you help.

**:) Sixthsense :)** · 10-31-2013, 05:46 AM

In D2 Cell of Hours Sheet

=IF(SUMPRODUCT((Task!$A$2:$A$10=$A2)*(Task!$B$2:$B$10=$B2)*(Task!C$2:C$10<>"")),INDEX(Task!$C$1:$K$1,,COLUMN(A$1)),"")

Drag it down and right....

**sktneer** · 10-31-2013, 07:35 AM

@Sixthsense

I think you can exclude (Task!$B$2:$B$10=$B2) from Sumproduct. What do you say?

I must say that It was a brilliant solution provided by you. Not sure about OP but you deserve a * from me.

**n07cn3** · 10-31-2013, 11:29 PM

@Sixthsense and sktneer,

we are so close to solving this.

both equations work great except when Hours (column c in Hours sheet) is blank the exclusion in column D through L still populate with "TaksX" in Hours sheet.

is there a way to make it not to populate if column c equal to blank?

thanks again for all of your help.

**sktneer** · 10-31-2013, 11:59 PM

You can use........

Please Login or Register  to view this content.

**:) Sixthsense :)** · 11-01-2013, 12:01 AM

Originally Posted by sktneer

@Sixthsense

I think you can exclude (Task!$B$2:$B$10=$B2) from Sumproduct. What do you say?

Yes exactly

As you rightly pointed that the Emp # is enough when comparing the data and thanks for the correction and rep

**:) Sixthsense :)** · 11-01-2013, 12:05 AM

Originally Posted by sktneer

You can use........

Please Login or Register  to view this content.

In the suggested formula just add a Trim($C2) function for C2 cell

**n07cn3** · 11-01-2013, 12:23 AM

@ Sixthsense

i have very limit knowledge about excel.

i tried both equation you've suggested and both work.

i was just wondering what does the "trim" function does that the first equation would not do?

**:) Sixthsense :)** · 11-01-2013, 12:57 AM

Trim() just clears the leading and trailing spaces and allows only one space in between each characters if there is multiple spaces.

**n07cn3** · 11-01-2013, 01:02 AM

@ Sixthsense.

again thank you so much for helping me!

**:) Sixthsense :)** · 11-01-2013, 01:05 AM

Glad it helps you and thanks for the feedback and rep

**n07cn3** · 11-03-2013, 01:41 AM

@ Sixthsense.

The equation worked great except when the employee task change in column C through K in "Task" sheet the information in column D through L in "Hours" sheet does not change.

Is there something we can do to make the "Hours" sheet update if the information in the "Task" sheet change?

thanks.

**sktneer** · 11-03-2013, 02:03 AM

If you mean by putting another "x" in task sheet for any Emp#, it does reflect on the hour sheet. Then where is the problem?

**n07cn3** · 11-03-2013, 01:11 PM

@ sktneer,

I've figure out what went wrong. It was the user and not the equation.

When I update the "task" sheet i use the "space bar" instead of the "delete" key. lol

all is good now.

but if there is a way to twitch the equation so that other users would not make the same mistake as i did that would be wonderful.

thanks again for your respond.

**sktneer** · 11-03-2013, 04:18 PM

One way to stop the user to input any character other than x is as follows......

Select the range C2:K10 in the task sheet. Go to the Data-->Data Validation-->List (in Allow field) -->x (in the source field) and click OK and you are done. Now user can only input x in the task sheet in the range specified. If he inputs any other character or space, he will get an error message.

I hope this helps.

**n07cn3** · 11-03-2013, 09:59 PM

@ sktneer,

it works great! i like it.

thanks again for your help!

Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

LinkBack

Thread Tools

Rate This Thread

Display

Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Re: Data from Sheet2 will populate if certain cell in Sheet1 have numerical value.

Thread Information

Users Browsing this Thread

Similar Threads

Auto populate data from Sheet1 to Sheet2

match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2

if cell in Sheet1 Column A = cell in Sheet2 Column A than fill data in Sheet1 Columns B-I

How do I populate cells on sheet1 with data on sheet3 using data in sheet2?

[SOLVED] How do I get data entered in sheet1 to auto populate sheet2?

Bookmarks

Bookmarks

Posting Permissions