Forum Discussion
EXCEL FORMULA ERROR
Hi All, I'm looking to create a formula that searches for codes across various departments and collates them together on a top level sheet. This will be an easier way of showing the data to snr m...
Hi mtarler
The sheet range to check for BN is each day of the month (1,2,3,4 etc) and it's 2 rows deep to allow for a morning booking and afternoon booking.
The range to search for the name it for the the staff name and job role.
In terms of sharing an example sheet, let me explain below:
For example:
COLUMN A - STAFF NAMES (set out across 2 rows Name, Title)
A8 - Jane Doe
A9 - Manager
Month is set up to show from Column B along to AF, with dates and days shown.
The sheet range to check for BN is each day of the month (1,2,3,4 etc) and it's 2 rows deep to allow for a morning booking and afternoon booking.
The range to search for the name it for the the staff name and job role.
In terms of sharing an example sheet, let me explain below:
For example:
COLUMN A - STAFF NAMES (set out across 2 rows Name, Title)
A8 - Jane Doe
A9 - Manager
Month is set up to show from Column B along to AF, with dates and days shown.
in simplest terms would this work:
=(Sheet1_2024!A8="jane doe")*COUNTIF(Sheet1_2024!B8:AF9,"*bn*")
if so and you want more 'functionality' or feature or what not we can work on that. I just want to know if I'm 'seeing' your sheet structure right.
=(Sheet1_2024!A8="jane doe")*COUNTIF(Sheet1_2024!B8:AF9,"*bn*")
if so and you want more 'functionality' or feature or what not we can work on that. I just want to know if I'm 'seeing' your sheet structure right.
- The *COUNTIF(Sheet1_2024!B8:AF9,"*bn*") works, but the checking for the row that includes jane doe's name does not. As soon as I add this in, the results come back as 0.
I could complete the task manually, and hope noone moves the names, but surely there must be a way? Would XLookup help with this?- note that (Sheet1_2024!A8="jane doe") is an explicit operation and the cell must be exactly that value (i.e. capital letters, non-visible characters, extra spaces, etc... will all make it FALSE)
making the formula more 'general' for a range of inputs, adding it as a lookup or dynamic array may help but still need to know what you are trying to do besides the 1 sample calculation you gave. i.e. are you trying to create an output table with/for all the staff? do you have a list of staff or should we just use a UNIQUE(INDEX(Sheet1_2024!A8:A27,SEQUENCE(10,,,2))) to grab the unique list of employees. That said, we still need to fix / figure out why the 'basic' formula isn't working.- The cells match in text (ie, capital letters, spaces etc.)
There is a list of staff, it has staff name on row 1 and then their job title on row 2.
There is a top sheet that looks at the monthly calendar and tallies up the following (bn for bookings, holidays) this then is translated into a capacity %.
I have a sheet already that does this and is working, but when a staff member leaves/begins currently, I'm manually adding them in and changing the formulas, which takes time, so wanted to add a formula in that would look for the staff members name regardless of where they are on the sheet.