trying to combine data if statement maybe?

**Betadog** · 02-28-2013, 03:46 PM

ok I combined all names from 2 spreadsheets into one but now im looking to have new spreadsheet fill in a colum from that name so it looks at the name goes to both spread sheets and if name exisits takes cell data and drops it into new spreadsheet.

make sense ?

example main sheet is a taly of June and july then there is separate june and july spreadsheet with hours worked. Main spreadsheet has colums for hors worked in june and july . Bob worked in june not in july and dave worked in both june and july .

so cell for july on main sheet looks at name then go look in both june and july for bob no if bob is on june put hours worked on main sheet in june and in july since didn't work put 0.

as for dave he worked both so go to each sheet and get and populate both june and july.

im just hoping for an easy way rather then manually going through each and every person.

am I making any sense?

**BB1972** · 02-28-2013, 04:29 PM

Speaking of manually going through things

could you please upload a sample workbook for us to work on?

**Betadog** · 02-28-2013, 06:43 PM

trial.xlsx

Sure tab 1 is master has everyone - tab 2 and 3 only have some... now the actual sheet im working on has 100's but this should explain what im doing. I need the june/july to fill in appropriate spots on master. let me know if needs further explanation.

**Betadog** · 03-01-2013, 02:23 PM

any luck with this?

**BB1972** · 03-02-2013, 11:47 AM

Betadog,

There are a couple of approaches you could take with this - the one you use may boil down to personal preference in terms of displaying the data, effort involved, who else may be using it, etc

The most straightforward approach, in my opinion, would be to copy the data from each month onto the "ALL DATA " tab I have added, including a column to contain the month before doing so. It's then simply a matter of filtering by month to see who did what.

Alternatively, on your "Master" tab, you could use formulas to look at each month's tab, and return the data found there, as I have shown on the attached workbook. The formula in C13 on the "Master" tab:

Please Login or Register  to view this content.

says return from B1:B6 of the June tab INDEX(June!B$1:B$6 if data matching B13 of the Master tab MATCH(Master!$B13 is found in the range A1:A6 of the June tab June!$A$1:$A$6. The "0" near the end specifies that it must be an exact match (note: you had "dave dave " - note the space after the 2nd dave - on your Master tab - this is not the same as "dave dave" on your June tab); and the =IFERROR & "-" tell Excel to return a "-" if there is no match - ie, rob rob did not work in June.

I hope this helps.