
How To Expand Arrays In The Formula?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hi all! I am doing KPI for my boss and struggle with one of formulas. I have a sheet called "stafflist" and a sheet named "main" in the workbook. Stafflist contains a few tables, one for each shift. These tables contains some text as well. H  holiday, S  sick etc.
In the main sheet I have a couple of tables to return names in. And there are two criteria (you have to choose shift and date to retrieve names from that shift on that day only). It is too complicated for me to create the formula to make all this thing going. I`ve attached a workbook. See inside. Hopefully someone will find a solution... Thanks in advance!
Similar Excel Video Tutorials
Array Formulas Basics
 Topics for this video: 1)Learn about the basics of array formulas 2)Entering array formulas with Ctrl + Shift + Enter 3)Array syntax
SUMIF Only Accept Ranges, Not Arrays
 Learn about the limitations of the SUMIF function it cannot accept arrays, only ranges. This means you must use other means like the SUMPRODUCT funct ...
Hide Columns by Grouping
 See how to hide columns using the Grouping feature. The Grouping feature will had a collapse and Expand plus above the column for easy hiding and unhi ...
Similar Topics
Hi all! Errors drive me crazy! Especially when I can` get rid of them. So, I have a formula, and error catcher =IF(ISERR( does`nt work. Excel says you have a mistake in your formula. But I am sure enough I don`t. There is a formula :
=INDEX(OFFSET(Stafflist!$B$5,MATCH(VLOOKUP($C$5,$Q$17:$R$20,2,0),Stafflist!$B$5:$B$163,0),1,ROWS(IND IRECT("Shift"&VLOOKUP($C$5,$Q$17:$R$20,2,0)))),SMALL(IF("O/T"=OFFSET(Stafflist!$B$5,MATCH(VLOOKUP($C$5,$Q$17:$R$20,2,0),Stafflist!$B$5:$B$163,0),MATCH(MAIN!$G$ 5,Stafflist!$C$5:$AJ$5),ROWS(INDIRECT("Shift"&VLOOKUP($C$5,$Q$17:$R$20,2,0)))),ROW(INDIRECT("1:"&ROW S(INDIRECT("Shift"&VLOOKUP($C$5,$Q$17:$R$20,2,0))))),""),ROW(A5)))
The formula works fine, but when there is no value to return to that cell, it comes up as #NUM error. Conditional formating is not for me. These dark triangles in the corners of the cells drive me crazy as much as errors do. How do I get rid of the error in the cell if there is no value to return?
Hi all! I struggle again. On one worksheet I have a huge stafflist and on another I have small table. When I choose adate In that table I want to retrieve names of staff who is on holiday, who is off sick. Find an attached workbook. Maybe someone will find a solution. Thanks!
Many thanks in advance for all who contribute help in this thread.
In my workbook are tabs "start", "end", and "total". Each work shift a user creates a new "shift" tab bewteen the "Start" and "end". Each "Shift" tab has an identical layout. Within the "Shift" layout is a range where the user enters notes (range=A42:A55), a cell with the date (B3), and a cell with the shift (F3). I would like to implement a way to search the notes range of each "shift" tab for any of a set of key words (example: Missing, Incident, Found), then return a list of the date/shift of any "shift" tab which contains any of the key words into a "Total" tab cell.
Each "shift" tab is renamed the "(day of the month)(shift worked)" when it is completed. So if the formula returned a list of the tabs names, that would be acceptable too. (Month).xls
Again, thank you to all.
Hi all! I'm working on KPI for my company. Unfortunately I struggle on one formula. I want to retrieve names of staff who are on holidays or sick. I made a formula and am sure enough it should work, but it doesn't. All I get is #REF, #NUM, and #NA errors. See attached workbook. Maybe someone will find a solution... More info inside the workbook. Thanks in advance!
I was trying this since last couple of hours, I could not accomplish a solution. I guess it should be simple but I ain't able to find the solution !
Sheet 1 (Main):
Main
A
B
C
E
F
G
H
I
J
K
L
M
N
O
P
Q
1
Code
Branches
Product
Populated for Date
Date Ended
2
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
Dec07
3
Jun08
Dec07
Jan08
Feb08
Mar08
Apr08
May08
Jun08
Jul08
Aug08
Sep08
Oct08
4
5
APJ
6
INCREMBUS
B0
EBG001
500
500
55555
7
INCDIRBUS
B0
EBG001
119812077
8
INCBRKSEC
B0
EBG001
119812077
9
INCDP
B0
EBG001
0
10
INCMF
B0
EBG001
972129
11
INCIPO
B0
EBG001
1265865
12
INCFDFI
B0
EBG001
0
13
INCPMS
B0
EBG001
444179
14
INCSTRPDT
B0
EBG001
0
Excel tables to the web  Excel Jeanie Html 4
Sheet 2 (Workout):
Workout
A
B
C
D
E
F
G
H
I
J
K
L
1
v
EBG001
2
Dec07
May08
3
4
B0
APJ
10
12
16
INCREMBUS

20
22
24
26
31
35
Spreadsheet Formulas
Cell
Formula
C16
{=IF(AND(Main!G2:R2=Workout!F2,Main!G3:R3=Workout!G2),SUM(IF(Main!A5:A14200=Workout!A16,IF(Main!C5:C14200=Workout!C1,IF(Main!B5:B14200=Workout!A4,Main!G5:R14200)))),0)}
Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!
Excel tables to the web  Excel Jeanie Html 4
I just want to be able to display the value 55555 in Sheet 2 (Workout) which is in cell L6 on Sheet 1 (Main) based on a match with the dates in cells F2 & G2 *and* branch, Code and Product. If I do a SUM(IF()) or SUMPRODUCT() it would return the sum of values in K6 & L6 which I do not want. I think there is INDEX() involved here but cannot get my head through it
Help me out guys, please !!
Attached to this post is a copy of the timesheet I am trying to make in excel. If someone selects "Personal" or "Sick Time" as is seen in the example, I need it to populate in the box specified in the lower left hand corner. It should recognize the amount of time based on the worked time (0 hours in this case) subtracted from the scheduled time (8.0 Hours in this case). With 80=8, then the total sick time would be 8 hours for that day (clearly). But if multiple "sick days" occur, I need for the resulting sick time from each day to be added together and put into the respective box.
One more caveat: A third shift person (11pm7am) enters 11pm  11:59pm in the third shift box, and then follows up with 12am7am in the first shift box on the next day. The formula needs to be able to distinguish between the times in the 1st shift box to be able to recognize if the time entered is for 1st shift of 3rd shift (2nd shift and 3rd shift get paid a differential).
Any ideas on how to make this work? Thanks in advance for any help!
I am new to Excel and would appreciate any help or suggestions.
The problem I am having is counting the number of sick leave occurances of a firefighter who is assigned to a specific shift ("A", "B", OR "C" SHIFT). Excel seems to count each consecutive sick shift as an occurance, because it seems to see that each sick shift is seperated by two scheduled days off.
I need the Excel worksheet to count each consecutive sick leave shift as one occasion. IE: Captain James Smokey takes off 3 consecutive ("A" shifts) sick leave, returns to work on "A" shift, and then takes off sick again the following shift. This should count as four shift of sick leave, but should only count as two sick occurances.
Attached you will find a example of the spreadsheet.
Again, all help or suggestions would be greatly appreciated
Hi,
I've been trying to work out an excel problem for days and days, googled
till I could google no more, and still haven't found the answer, so I
was hoping you could help..
I have a spreadsheet with a list of peoples names, addresses, postcodes
etc. (stafflist)
I have another ten or twenty spreadsheets with postcode data on them.
The postcodes refer to government funded areas.
I have set up the sheets (postcodearea1, postcodearea2) with all the
applicable postcodes, and have managed to get a column to look through
the 'stafflist' sheet, to find if there are any of those postcodes in
teh 'stafflist' sheet, and count how many.
For example:
Area Postcode Count
Beckfield TS6 4BG 2
Beckfield TS6 4BH 0
etc etc
I've done this with a 'Count If('Stafflist!$B$2:$B$144, xxx')' formula.
What I want to do, is for excel, when it finds an instance of the
specified postcode in 'stafflist', to bring the corresponding data
across (name, date of birth, address) and 'deposit' it in either the
same (postcodearea) sheet, or on a new sheet.
Eg:
If
//If the postcode on postcodearea cell B2 exists in the stafflist sheet
//column B
(stafflist!$B$2:$B$114,B2)
//Add number of instances to 'count' column in 'postcodearea' sheet
count=count++
//Then, take all the data in the row(s) containing the corresponding
postcode(s) and put it all on a sheet called 'fundedstaff'
copy stafflist!$ref$ to fundedstaff!
I would want the information from the 'postcodearea' sheet to be in the
first two/three columns of the sheet, then the personal information to
be in teh next columns along teh row.
Is there any way this can be done at all?? I've never used VisualBasic
before, but I've done Java and C#, so I understand programming concepts,
but have no idea when it comes to VB. I'd be really grateful if someone
could help me wiht this.. It would save me days and days worth of work
each month..
I hope someone can help..
Thanks
lemonpillows
I have a workbook designed for department scheduling. On sheet "Shifts" I have a list of all available shifts. Time slots are every 1/2 hour (00002330) listed in column A and the name of the shift (S1S25) going across in row 1. A number 1 is placed in each 1/2 hour interval, going down, to indicate time in the shift. So say for a 4 hour shift I would have 8 1's so they can be totaled at the bottom. There is also a sheet for each day of the week. The sheets are set up like the "shifts". Instead of shift names the employee name is listed in the row. I want the cell below their name to have a drop down box that shows the available shifts (S1S25) and then when the shift is chosen by the manager I want it to autopopulate the corresponding 1's from the "Shift" sheet to the time intervals on the weekday sheet. I don't know if I'm overthinking this or if I need a more complicated macro. I would really appreciate some ideas!
I atempting to expand the fucntionality of the folllowing forumla by using the address() worksheet function.
Code:
=INDEX(Tables!$H$4:$Q$24,MIN(20,VALUE(MAIN!$AT$11))+1,MATCH(CP30,Tables!$H$4:$Q$4))
I have replaced the "Tables!$H$4:$Q$24" in the above formula with the following ...
Code:
ADDRESS(MATCH(MAIN!$B$12,Tables!H:H,0)+1,8,,,"Tables")&":"&ADDRESS(MATCH(MAIN!$B$12,Tables!H:H,0)+21,17)
Although the ADDRESS formula DOES return the correct address  "Tables!$H$4:$Q$24"  when placed in a cell by itself, it gives me a VALUE error when used as a replacement to Tables!$H$4:$Q$24 in the first formula.
Is the result from the ADDRESS function a reference or is it simply a string type?
Thansk for the help.
Edit:
Oh yeah, WHY am I doing this?
I need to reference one of several tables for the results. The table to be referenced is determinded by the value in MAIN!$B$12. Each table has the same number of rows and columns and are situated vertically with a few rows in between. This should have no affect of the results. The test forumla is referencing the first table anyway and should work as intended.
The original question is at the bottom, but I was still struggling with it so
this is more information in the hopes that someone else can help me. Thanks!
I'm having trouble with the formula....
If my worksheets a
Apr. 3
May 5
March 13
and I want cell B14 from every worksheet to all appear in a new worksheet.
What would my formula look like???
Thanks for your time and assistance!!
"Gord Dibben" wrote:
> Tia
>
> You refer to "tables" in a workbook.
>
> Do you mean "worksheets" in a workbook?
>
> If the latter, how are they named?
>
> If Sheet1, Sheet2 etc. enter this formula in A1 of a sheet.
>
> =INDIRECT("Sheet" & (ROW() & "!B5")
>
> Drag/copy down column B.
>
> If sheets have unique names, enter the sheet names in a column and use this
> formula.
>
> =INDIRECT(A1 & "!B5")
>
> Assume sheet names were in A1:A10 you would copy down 10 cells.
>
> Your ranges may differ, so adjust to suit.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 2 Jun 2005 15:25:38 0700, Tia <Tia@discussions.microsoft.com> wrote:
>
> >If I have a workbook that has multiple tables, is it possible to select one
> >cell and copy that same cell from all the tables and paste into either a new
> >table or a new workbook???
> >
> >
> >I want the data from a specific cell (i.e.B5) from each table copied into
> >one table. Is there a way to do this???
> >
> >Thanks for any help!
>
>
Expand AllCollapse All
Hi all,
I'm fairly new at using formulas so I apologise if this question has been asked before, I'm not really sure what I should be looking up (terminologywise).
My problem is; I have a workbook that has 5 tabs/worksheets which represent 5 weeks (minus the weekend, and the 5th being a halfweek). In each of these week worksheets is a table of the week days. Above the table is a drop down box that has shift types listed (from a "Data" worksheet). Below this drop down is a column of 21 cells. I want these cells to populate with the specific hours for the particular shift type selected from the drop down. The 4 shift type hours are found on the "Data" worksheet and have named the ranges as "Early", "Day", "Late" and "Night".
The Night shift has the longest hours, so I've accounted for this on the week worksheet tables by allowing 21 cells in the "hours" column. The Early, Day and Late shifts only need 18 cells from this column, so the last 3 cells in their "arrays" are blank (just so each shift type consistently has 21 cells in the column").
The help I need is I don't know how to code the formula to see the value of the drop down, and then copy the shift type array from the Data worksheet to the current Week worksheet in the time column.
I have attached a copy of the workbook for your perusal.
Thanks in advance!
A Grace
Hi all,
I'm fairly new at using formulas so I apologise if this question has been asked before, I'm not really sure what I should be looking up (terminologywise).
My problem is; I have a workbook that has 5 tabs/worksheets which represent 5 weeks (minus the weekend, and the 5th being a halfweek). In each of these week worksheets is a table of the week days. Above the table is a drop down box that has shift types listed (from a "Data" worksheet). Below this drop down is a column of 21 cells. I want these cells to populate with the specific hours for the particular shift type selected from the drop down. The 4 shift type hours are found on the "Data" worksheet, I have named the ranges as "Early", "Day", "Late" and "Night".
The Night shift has the longest hours, so I've accounted for this on the week worksheet tables by allowing 21 cells in the "hours" column. The Early, Day and Late shifts only need 18 cells from this column, so the last 3 cells in their "arrays" are blank (just so each shift type consistently has 21 cells in the column").
The help I need is I don't know how to code the formula to see the value of the drop down, and then copy the shift type array from the Data worksheet to the current Week worksheet in the time column.
I have attached a copy of the workbook for your perusal.
Thanks in advance!
A Grace
Afternoon all,
I have a workbook with several sheets. On the main sheet I have a list of full names and a breakdown of their data according to their individual sheets. Each individual has 3 sheets to update which begin with their last name. ie Smith Log
I successfully used the Indirect/Left/find functions to find their data in their perspective sheets and place it on the "MAIN" sheet.
What I am looking to do now is: I have a cell (A12) which I would like the individuals full names to appear according to sheet "MAIN"
I have cell temporarily (J3) which grabs the sheet name (last name). Is there a way to do a lookup of the sheet name and match it to sheet "main" and return the value from "MAIN"
I have tried Vlookup / Match / Search with no success.
I have attached a very slim down version of the workbook.
Multiple people update this tracker so I am trying to make little room for errors
Thanks
Hey, I am stuck trying to create a 4 way lookup formula.
I need a formula that will look up a staff members Name, Profession, Date and type of shift worked. Then if they worked on day shift , have their name appear on a sheet that will collect all the dayshift workers names, in their corresponding professions.
I attached a very simple version of what I am trying to do with more detailed instructions on the second sheet named "1st Day of month".
TIA.
Hi,
We are having many errors in shift rostering in our organization. I therefore have planned to make a shift roster which can avoid errors.
The concept what i have here is that the names will be listed in sheet "A" along with the shift timings.
Workbook two will have formulas For eg: If i have created
1) 7.30am 4.30pm
2) 4.30  1.30
When i enter an option in sheet two the shift should change for everyone and should rotate sequentially i.e if a person was working in 1 st shift last week. This week when preparing the roster when option 2 is pressed he should automatically change to 2nd shift and the person in 2nd shift to 3rd shift and so on.
What i mean is here all the people get to work in all shifts and the confusion is reduced.
Kindly help me in making this a success or give me idea as to how i can prepare this.
Any assistance would be greatly appreciated.
Warm Regards,
Chaitanya
Hello
I have a spreadsheet which is linked to another workbook. When a cashier closes their window, the Main Bank cashier verifies the funds and places their shift (d for day, s for swing and g for grave) next to the cashier window number which is located in the Main Bank workbook. This is linked back to the cashier worksheet and transfers the cashier totals to the Main Bank spreadsheet. The d,s,or g is linked to the cashier sheet in cell e5 and transfers the totals to the appropriate shift. I need a VBA formula which locks the cashier worksheet from further changes when e5 displays d, s, or g. Also, if changes need to be made to the original cashier worksheet, the Main Banker would delete the link so that the cashier worksheet can be updated for changes and then relocked when accepted again by the Main Bank cashier. In other words:
=if(e5="d","s" or "g", lock sheet,unlock sheet)
Note all sheets are protected with a password.
Hi everyone, I need help with the following problem:
I am certain that there is a command that does this but it is so not obvious that I am breaking my head over it for the past two hours... Please help me out. Your help is appreciated in advance.
Main!A1 = "HI"
Main!A2 = "0012"
Based on the information typed in these fields, I want to link to a sheet called "HI0012" and see the value of HI0012!A1 in cell Main!A3
The named sheets already exist. How do I reference sheets based on calculate sheet names? My attempts were something like, written in cell A3 on the Main! sheet:
='(Main!A1&""&Main!A2)'!A1
Nothing works, please help! Thank you guys!
I've got this formula, What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the dreaded
#Value! error message. Here is the formula: =SUMPRODUCT(('A
Shift'!$A$4:$A$120="A"),(TEXT('A Shift'!$B$4:$B$120,"mmmyy")="Mar05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(('A Shift'!$A$4:$A$120="A"),(TEXT('A
Shift'!$B$4:$B$120,"mmmyy")="Mar05"),"",(('A
Shift'!$A$4:$A$120="A"),(TEXT('A Shift'!$B$4:$B$120,"mmmyy")="Mar05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(('A Shift'!$A$4:$A$120="A"),(TEXT('A
Shift'!$B$4:$B$120,"mmmyy")="Mar05"))
The call for an empty string, I believe is where it's falling out, not sure!
I have attached a workbook for better reference...
Here is what I am trying to do...
I have 3 worksheets in the workbook. One for First shift employees, one for Second shift employees and a third for Terminated Employees.
Each day when I do attendence, I use a drop down menu to show if the employees are here, called off, ncns, late, termed etc...each work book is for a week of data. So it shows Monday  Sunday. If in one of those fields, I enter the "termed" option, I would like it to automatically populate the employees name, start date, end date (the date it shows "termed"), and the shift worked in the third workseet "Termed".
So, on the third Sheet it would show Jess Donaldson from first shift was termed on Nov 18 and Harold Bigler from second shift was termed on Nov. 19.
Is this even possible? Does someone have any ideas of how I would go about doing this?
If anyone has any thoughts, suggestions, advice, I would really appreciate it.
I had trouble with a descriptive title for this one, sorry. I have had good luck in this forum before and have learned that there is usually more than one way to do something in Excel.
My current problem. The attached sheet is part of a scheduling spread sheet that I use. I enter shifts into a calendar grid (upper left of sheet). Names on the left, days of the block on the top. Letters represent the shift that someone will work ie: D is 7 to 4, listed as 7 A Red in the calendar portion. The formula that I use in the calendar section is =INDEX($A$5:$AC$53,MATCH("D",L$5:L$53,0),1) for instance for the D shift on Sept 5th of this block. It looks in the area of my grid for "D" in the L column from rows 5 to 53 and inserts the name from the first column (the person working that shift).
My problem is that our schedule has become more complicated. Now I have a couple of different possibilities for each shift. For the 7 AM shift I have D for 7 to 4 and D3 for 7 to 7 a 12 hr shift. I have figured out how to make the calendar time label change depending on which letter is entered in the grid. See the times section for Aug 26 (the rest still need to be changed). What I can't figure out is how to get the employee to fill in wether I use D or D3. Any thoughts?
Thank you,
Stuart
I am trying to reference other sheets to pull daily data into one sheet. So my first tab is called "All" and the others are "1", "2"...."12", one for each month of the year.
In cell A2 of the "All" tab I have the date Jan 1st, down until Dec 31st. I was trying to use this formula in cell B2 of the "All" tab to reference cell E8 in the tab "1", which it does and works fine:
=INDIRECT(MONTH(A2)&"!E8")
however, when I drag the formula down, the E8 in the formula stays static and does not shift down with each row I drag. The goal is to have the formula look in a different tab as the month changes from 1, then 2, then 3....
How do I get the E8 in the formula to shift as I drag the formula down?
Ok. I have a workbook, in this workbook there is a worksheet called:
main
on the main worksheet I have a import button that I press,
which fetches a daily roster file from the network and it imports the
files into the workbook into a sheet called:
master.xls
So there are two sheets: main and master.xls (with the .xls extention)
Before I go on to explain what I mean by help with a formula, let me
explain what the master.xls sheet has:
4 columns:
A1:D1 headers, data starts at A2 and below.
Daparment Name, Date, Employee Name, HR status code
So for example, lets use these values, A2:D2
SORT, 8/1/2005, Joe Smith, Present All Day
 so I hope I made that master.xls sheet clear,
it's basic : )
Now, the main sheet is always there, master.xls is only there if I
press the import button, everytime I press the import button, the old
master.xls file is deleted and a new one imported in it's place.
Everyday I have to check to make sure Roster was updated for all the
departments, and right now I have to visually check it, scroll down,
find the date and manually look for the Department name to make a
match.
So on the main sheet, I wanted to make something simple that looked
into master.xls and quickly told me in the sheet of (main).
So on the main sheet, in cell:
B27 I have: SORT (formatted for text)
C27 I have the date using the =now() formula (formatted for date)
and in D27 I wanted to put in a formula that would, basically do this.
Look into B27 and C27 on (main), and then look into (master.xls) and
see if that Date and Department is there and if so, Print Yes or No
into D27, where the formula would reside.
Basically I wanted a formula to check to see if the updates were done
and I could just carry over the formulas for all the departments below
and quickly find out my answer.
>From the research that I have done, I was told that I would have to
concatenate the columns, but I was wondering if it would be possible to
have it done without doing that ?
I was told that I could use an array formula but it never ended up
working, so not sure this formula was ok: here it is:
=IF(ISNUMBER(MATCH(B27&CHAR(160)&C27,master.xls!A:A&CHAR(160)&master.xls!B:B,0)),"Yes","No")
and I entered this formula using Control+Shift+Enter at the end (CSE)
since it is an array formula. I see {} so I know I entered it
correctly.
So not sure, if I did something wrong, or that formula not work, please
help me out, thanks.
A Great learning experiance this Excel has been.
If you have a difference formula or a different way of doing it, I
would like to hear it all, thanks.
What I need to do is trap for when there is no data,
when there is no data show either "Empty String" or 0, instead of the dreaded
#Value! or #DIV/0! error message. Here is the formula:
=SUMPRODUCT(('A
Shift'!$A$4:$A$120="A"),(TEXT('A Shift'!$B$4:$B$120,"mmmyy")="Mar05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(('A Shift'!$A$4:$A$120="A"),(TEXT('A
Shift'!$B$4:$B$120,"mmmyy")="Mar05"))
Any assistance will be appreciated.
Thanks,
I have an Excel 2003 workbook which contains a master sheet that needs
to retrieve and use the names of the other sheets contained in the
workbook. For example, the master sheet ("Instructions") would show in
cells A1  A5 the names of the five other sheets in the workbook that
might be called "Test1", "Test2", "Test3", "Test4" and "Test5". Once
the master sheet has a usable reference to the other sheet names, any
time one of those names changed (or additional sheets added) the master
sheet would automatically get updated. Is this possible either using a
sheet reference formula or VBA (preferably formula)? Ideally, the
master sheet would reference the other sheets as a Hyperlink cell so
clicking on any of the individual names would take the user directly to
the appropriate sheet!
Any ideas how this can be done?

