I have done a spreadsheet that shows leave for staff. At anyone time there can be only 3 managers and 4 staff off as a maximum and the cell value for the day is entered as "AL". Therefore on a given day if someone tried to enter a fifth "AL" for example in the staff section a pop up displays stating they have reached a maximum.
View 4 RepliesI need to test whether one or more rows are about to be deleted in a worksheet.
Users delete rows to edit a worksheet, which affects other columns. I need an opportunity to warn them before the delete occurs.
Im trying to come up with a Macro that will perform a =SUM() function across multiple workbooks. Some of these workbooks may exist, some may not.
For example, Summary.xls has a =sum() formula that picks up data from wbk1.xls
wbk2.xls
wbk3.xls
etc, all the way to 100
Today i only have wbk 1, 2 and 3 but i know everyday more wbk's are added so i want to create a sum type function to accomodate the sbk's that may be created at a future time.
Right now when i try it, I not only get REF/# but on opening Summary.xls I get a popup that asks me to define the missing wbk's
I need to find the unique column values and sum the next column values. i,e
ID XXX
1 100
2 200
1 100
3 500
4 600
2 200
--------------------
Total 1400
I have a 'master' spreadsheet containing over a thousand filenames. I have been given another spreadsheet with just a couple of hundred filenames on it and have been asked to find out if the filenames on this 'small' spreadsheet appear on the larger/master one.
Is there any way (by formula or by macro code) that I can do this easily? What would be great is some way of Excel colouring in the filenames on the 'small' spreadsheet that also exist on the master one.
I am trying to use the function Match into the Index function but as a Result I have nothing than N/A#, I would like to be sure that I'am using this function in a proper way
Workbooks(filename_appli).Activate
Range("B" & dercell_appli, "C" & première_ligne).Select
Set Rango3 = Range("B" & dercell_appli, "C" & première_ligne)
Range("C" & dercell_appli, "C" & première_ligne).Select
Set Rango4 = Range("C" & dercell_appli, "C" & première_ligne)
ws.Activate
dercell_unit2 = Range("C65500").End(xlUp).Row
For y = 4 To dercell_unit2
Coincidir = Application.Match(Rango4, Cells(y, 3), 0)
Cells(y, 2) = Application.Index(Rango3, Coincidir, 1)
Next y
Each column represents a user and each row represents an application the user has. I would like to extract all the applications that are common to all the users and place it in an arbitrary column.
For example
User1_____User2_____User3
Office_______Office_____Office
Adobe_______Adobe____Adobe
Flash________Flash_____Snake
Firefox_______Firefox
Solitaire
The above list should generate Office and Adobe since every user has Office and Adobe.
I have a challenge which I am trying to solve without VBA and I am uncertain how to approach
If I have the following spreadsheet consisting of three columns:
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
D.........................
E.........................
F........6........4......
The result I would like to achieve is
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
F........6........4......
So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?
I have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.
View 2 Replies View RelatedI am trying to determine if the value in cell Y2 also exists anywhere in cells AE2:QE2.
My initial stab at it is
[Code] .....
This results in a #VALUE! error (I am pretty sure that's because excel doesn't like the AE2:QE2 part).
I would do a formula-driven conditional format for this, but I have 2000+ rows that need to be acted on, so that's not feasible.
Is there any way to have excel check if the Y2 value exists in AE2...QE2, cell by cell?
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.
Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
I want to show values entered with percentage sign.(%) Right now, if i enter any value say,10 in a cell having percentage format, shows 1000%. For that, I don't want to enter 0.1. I just want to enter any value and see it with percentage symbol.
View 4 Replies View RelatedI have the following situation: I have some values ​​in column A. other values ​​in column B. I want to be able to display a list using the values ​​in column A and values ​​in column B. A1-> B1, etc.. Attach file: Book2.xlsx
View 5 Replies View RelatedI've tried creating a formual to only show the min values for each row,If
any values in my table is equal to 0, than, it should not display a 0, but
the lowest, positive number available. in my table. However, I'm not able to get it fully functionable.
Here's my formula:
i try to find any function, which make this.
I have a table: ....
I would like a formula to return the top 10 products sorted by value, but to return the colour name in order starting with the larges and the value in a different column. Ideally I would like to do as little formating as possible. I have attached a spreadsheet as an example
View 2 Replies View RelatedI am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are:
- Date
- Hourly Rate
Values inputted on a daily basis are:
- Hours Worked
- Volume Picked
Calculated values are:
- Total Cost
- Cost Per Unit
- Mean [=average(CPU)]
- UWL [=mean+(2*stdev(CPU))]
- UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
I am modifying a template from MS's site. Its a recipe tracker.
They have one sheet with a table on it. Some of the data in this table is pulled, using a formula, from the different worksheets (recipes). The particular formula they use is this:
However, when I try to do my own version of it, simply changing the '!Category' portion, it doesnt work. The cell just shows the formula and I cant figure out why. Sometimes, Ill type out the same formula that they have working and it doesnt work, which makes no sense to me.
Here is the template for reference: [URL] .........
I am creating a spreadsheet that allocates hours to different categories of work - for example, "Fire Protection". I need the allocated hours for all of the different Fire Protection duties to only show up when a value greater than zero is entered in the column at the top of this category entitled "Fire Protection". If "Fire Protection" has a value of zero, then all fire protection-related duties will automatically be zero as well - in effect, this category of hours is nullified.
View 1 Replies View RelatedFor input I have 2 columns:
A---B
1---5
3---4
-----
2----
I need for output column C:
C
1
2
3
4
5
It's possible to do that using one or more function ?
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
I have spread sheet that adds meter readings. As you enter the reading it subtracts from previous month's and gives you net value. But this always shows a negative value in the following month until you enter the current reading.
Also I would like to be able to total these net values as a running total. Is there a simple way to not show the negative values and or use those when getting the running total?
I have selected 6 rows and 2 columns in a sheet and I have a button in the same sheet.Now I click the button it triggers the button_click where I have put msgbox to popup.But I need this selected value in message box instead of the my hard coded string.
sample value in the selected cell:
1,1.005
2, 3.006
.........
..........
I have an inventory sheet that uses multiple userform comboboxes to input data to a sheet. Everything works great except on one userform "pullmat" where the user removes inventory from a unique list of all material available in "Master Log" sheet. The combobox that im trying to fix is "combobox3" in the "pullmat" userform. I'm trying to get that combobox to only show P.O. numbers that are related to the material selected in the "Material Code / Name- combobox2. In other words, the user should only be able to select a P.O.# that matches that specific material on the Master Log sheet.
The file size is too large to attach but I can email a copy if needed.
I have a list of part numbers in a column and a list of suppliers in another column. Parts may have anywhere from 1 to 15 suppliers.
Is there an easy way to show each part number once down one column, with the suppliers going across the one row for each part number?
Example:
PN#Supplier
123ABC
123DEF
123GHI
123JKL
123MNO
123PQR
456ABC
456DEF
456GHI
456JKL
456MNO
456PQR
456STU
Desired Output:
PN#Sup1Sup2Sup3Sup4Sup5Sup6Sup7
123ABCDEFGHIJKLMNOPQR
456ABCDEFGHIJKLMNOPQRSTU
I tried a pivot table,
I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?
View 2 Replies View Related