Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


Free Excel Forum

How To Expand Arrays In The Formula?

Forum Register
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

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

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!


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!


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.

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       Dec-07   Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 Dec-07 3       Jun-08   Dec-07 Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 Aug-08 Sep-08 Oct-08 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           Dec-07 May-08           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 8-0=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 (11pm-7am) enters 11pm - 11:59pm in the third shift box, and then follows up with 12am-7am 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!




Good morning,

In the attached exampe sheet i have a vlookup working through a roster to extract just the shift headings (LC1, LC2 etc) and the aligning start, finish and shift lengths from the adjacent columns.

I now need to add in different role names. You will see in the end of column I there are now shifts called "Operational Advanced" with a number following.

What I need to do is amend my formula so that either of these "Roles" will come up in the table under column B.

There is potential for more different role names in the future, so ideally I would like to be able to expand this method as required.

Thanks in advance

Darren.



Okay, I've searched - the words "timestamp", "work" and "shift" return way to much to sift through, so I'll start a new thread. Apologies.

I have a data file that I import into Excel (2007). It gives me a time stamp on each transaction, along with user names, and the transaction details. I need to 'lump' these into the activity performed by each shift. (there are three, from 9pm to 5am, 5am to 1pm, and 1pm to 9pm).

So, simply stated, did each transaction occur on shift 1, 2, or 3?
This task is to help management even out workloads across the shifts, and determine peak activities - which I can get to with pivot tables, etc after I've identified the shift. (Since 3rd shift spans two days, the if "window" might be: 9p to midnight, and midnight to 5a ?)

sample file attached. Staff names deleted.

any and all help sincerely appreciated !!!

Refill Activity Shift Test.xlsx

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 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

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.


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 (0000-2330) listed in column A and the name of the shift (S1-S25) 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 (S1-S25) 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!


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 (terminology-wise).



My problem is; I have a workbook that has 5 tabs/worksheets which represent 5 weeks (-minus the weekend, and the 5th being a half-week). 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


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 (terminology-wise).

My problem is; I have a workbook that has 5 tabs/worksheets which represent 5 weeks (-minus the weekend, and the 5th being a half-week). 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've got a formula in my workbook at the moment that will look at a shift in the format "08:00-16:00" and convert it into "8.00am - 4.00pm" however - I would like it to show the minutes only if not 0.

e.g. "08:15-16:30" = "8.15am - 4.30pm"; "13:00-21:00" = "1pm - 9pm"

Is there anyway to do this? This is my current formula:



Please Login or Register  to view this content.


The reason for the a/p"m" formatting is purely aesthetic - I find it easier to read the shift if the am/pm is in lowecase.

I have previously acheived this with a lengthy process of lookup tables and such; but I would like to condense this calculation into one cell instead of multiple cells with the end result afterwards.

I've attached a sample workbook of the 2 different approaches

Hope somebody can help!

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


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


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 ,
all

i have some code that checks column B for values 10.1 10.2,10.3, 10.4, 10.5, 10.6,10.7,10.8,10.9,10.10,10g,10G then copies the rows to another work book called shift analyzer
how it works
I open a workbook called "Shift report" containing 31 sheets, each sheet is named from 1th to 31th days of the month
then i open another workbook called "shift analyzer"
then i have a macro button in the "shift report" workbook that call the code from shift analyzer work book
it then checks all 31 sheets and checks column b for values 10.1 10.2,10.3, 10.4, 10.5, 10.6,10.7,10.8,10.9,10.10,10G,10.g
then add them all to shift analyzer workbook on Sheet Name "10" by copying the rows
the code i have


Please Login or Register  to view this content.


this all works great but i wonder is there away to add the sheet name where the copy the row was copied from

e.g if 10.1 was found on sheet name "1th "then add to rcell offset ,24 = 1th
e.g if 10.1 was found on shhet name "15th then add to recell offset ,24 = 15th
e.g if 10.2 was found on sheet name "1th" then add to recell offset ,24 =1th


this just a guess on how i would do it above i not great with vba little knowleage

any more info need let me know

thanks in advance everyone

shane

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.


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 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'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,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),"",(--('A
Shift'!$A$4:$A$120="A"),--(TEXT('A Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"),('A
Shift'!$D$4:$D$120))/SUMPRODUCT(--('A Shift'!$A$4:$A$120="A"),--(TEXT('A
Shift'!$B$4:$B$120,"mmm-yy")="Mar-05"))

The call for an empty string, I believe is where it's falling out, not sure!



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 "HI-0012" and see the value of HI-0012!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!