Welcome To Excel Space!!

[janitor_8]

You Can Discuss Any Type Of Discussion. Here, Everyone Can Do Any Type Of Excel(or any microsoft app related) Discussions Here. Enjoy!!:smiling_face_with_smiling_eyes:

[#2]

I need a little advise on my excel spreadsheet, I run a family history centre and I have created a spreadsheet for the volunteers to pick their shifts. I have protected the spreadsheet so that only two volunteers can choose the same shift. It works perfectly when I access the file, but when anyone else accesses it, all cells are fully editable. Is what I am attemting possible?

[#3]

Hi! I have an excel question. I have a workbook that has several sheets. My main sheet(sheet 1) is what I’m trying to place a formula in. I want cells in column B to auto fill with data from sheet 2 IF column A in sheet 1 matches column A in sheet 2. However, it’s possible that there are more than one matching column A.
so column A has contract numbers in sheet 1.

Sheet 2 has

column A - contract numbers 

Column B - contract types

it is possible that one contract can hVe 2 different type so I want both types to auto fill on sheet 1 

[#4]

Hi @janitor_8

I am trying to do a calculation to say on how many days in a month a bar is open

see data, Bar1 was open 2 in Jan, 3 in Feb. Bar2 was 1 in Jan 2 in Feb

on excel 365

data example

Jan	03-Jan	Bar1
Jan	03-Jan	Bar1
Jan	03-Jan	Bar2
Jan	05-Jan	Bar1
Feb	02-Feb	Bar1
Feb	02-Feb	bar1
Feb	02-Feb	bar2
Feb	04-Feb	bar1
Feb	04-Feb	bar1
Feb	04-Feb	bar2

[#5]

@chrisjms 

You can create a pivot table based on the data. Make sure to add the data to the Data Model when you create it.

Add Month to the Rows area, Bar to the Columns area and Date to the Values area.

Then change the summary function for the Values field to Distinct Count in the Value Field Settings.

See the attached demo workbook.

[#6]

@Hans Vogelaar 

Hi,

Thanks, that works well.

It does mean I then had to do a further formula to look at the pivot table data and pull based on the month and the bar.

Is there a way to do it all in one formula?

@chrisjms 

[#7]

@chrisjms 

And alternative:

[#8]

Thanks so much, I sent days trying to work out how to do it, now I see it, it makes sense.
So thanks also for the education.