is it possible to set up a form (or any method) that would let me input a name (from my workbook) that would then "pull" up details about that name. I am thinking of making a staff holiday planner. This would have a column of names, holiday entitlement,days brought forward etc and columns that have a year (daily) calendar.
View 2 RepliesIs there a way that I can click on a link in a worksheet that is linked to a data list in in another sheet and display the results in a text box on the original sheet containing the link.
For example ...
Essentially I have a sheet of names. This is just my master database. Other sheets get names from this sheet. The names are alphabetized. When I add a new name, the names are resorted on other sheets, but the data assocaiated with those names does not move.
Example:
Sheet1:
Jane Doe
John Doe
Add
Jimmy Doe
Therefore:..............
I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.
Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.
Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...
I am trying to do a multiple compare.
First I have a table that has these headings with data..
Name, Level, Req1, Req2, Req3, Req4
Bob, 2, 423, 506, 649, 798
Fred, 4, 601, 799, 1023, 1547
In another section I have..
name Drop down, level drop down. with the 4 Req.
Basically, when you select Fred and 4, I want to to display 601, 799, 1023, and 1547 in the columns to the left of where you do the selection. (which is not in the same place as that table at the top of this question.
I have an excel document that contains two columns and ithe columns there are more than 50000 rows of data. The first column contains numbers and the second column contains text filled from only three values for example work, work 1 and work 2.
I want to see only the three identical same values from column A where the values from column B are diferent.Example of how to look the result criteria:
Column A Column B
123456 work
123456 work 1
123456 work 2
Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).
I have used the IF-statements to determine the "Week's SUM" in H5.
What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:
1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).
2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).
Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.
I've attached a file : Dynamic DV and Dynamic SUMS.xlsx
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............
So the problem is that I have two workbooks: one has a set of identifying values which are a subset of one of the sets of values in the second and I need to match them up. In addition I need to take the values two columns to the right of the matched values in the second workbook and put them in the first.
Here is what my formula looks like now.
=IF(A5=VLOOKUP(A5,'[WBOOK2]Sheet1'!$A:$A,1,TRUE),INDEX('[WBOOK2]Sheet1'!,MATCH(A5,'[WBOOK2]Sheet1'!$A:$A,0),3),"NF")
Whenever I try to run it an error message comes up and highlights the match function name.
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
Im going to try to make this as clear as possible. I cant use my actual data because it wouldnt make any sense to anyone so Ive made up an example problem. Here goes...
Lets say in Sheet 1 I have two descending columns of data. Column A is MODEL of Vehicle (Civic for example). Column B is vehicle identification number (xxx for example). Sheet two has 4 columns of data, but only one is really required for this example. Cell A1 is the MAKE of vehicle (Honda for example). Directly below that in Cell A2 is the MODEL of the vehicle (Civic). There are then a few rows of empty space until it gets to the next vehicle MAKE and MODEL.
So in Sheet1 there is a long list of MAKE's in ColumnA and VIN's in ColumnB. Sheet2 Has a long list of MAKE's and MODEL's in ColumnA and random data in other columns.
What I want to do is assemble a Macro to start in Sheet1-A2, read the MODEL then copy the corresponding Vehicle Identification Number in B2. I then want it to go to Sheet2-A2 and start searching downward until it comes across a matching MODEL. Once it finds the match I want it to step downward 2 cells and paste the Vehicle Identification Number. Then return to Sheet1-A3, and repeat the process until EOF.
I have an Excel file with two different visible sheets (dashboards), each with different types of charts, which are pulling from the same data tables on a hidden sheet. I have a data validation drop down list on one of the dashboard sheets, which lists 7 items. Once one of the 7 item is selected, both dashboards update, which is what I want. However, I'd like to be able to have duplicated, related validation drop down lists. So if someone is looking at the first dashboard sheet and they select a new item, when they go to the second dashboard and see that same item, they could on that second dashboard select a new item without having to go back to the first dashboard.
I'd provide an example if I could, but cannot. I've tried searching on the forum for something similar, but most often the topic of dependent data validation is on two different types of drop downs with the second being dependent on the first, whereas I'm looking for two drop downs that are interconnected and can update in sync.
Please see attached the Workbook. I need to check the policy Numbers in Column A to be present in Access Table. If yes then write the corresponding ScanDate and BatchNo in columns I and J.
Sun Project.xls
I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.
Given this data:
27-6-14 3:00 12
27-6-14 7:00 18
27-6-14 11:00 19
If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
Can you have a look at the example attached here?
I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00
1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.
2. In cell A21 there is text "Gena's Sales".
I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."
I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.
I have a vehicle intake-form in which users have to enter vehicle data.
I also have a huge list containing make/model information.
I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...
I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !
I need the listbox to have the related dates to the salesman I am selecting in Combox Not all dates
You can check my Attachment
I have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,
i want to show 10 which is sum of first four values.
Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1
got a formula that checked C4, then D4 for dates (either in or our of date) and returns a count of 0 or 1, with some cells having tet in them also.
The formula I am currently using is below
=IF( OR(F4 ="enrolled",F4 = "O/S"),0, IF(AND(G4="",TODAY()-F4>730),0,IF(AND(TODAY()-F4>730,TODAY()<G4),0,1)) )
I have a range of four courses (all child protection) that should be attended, some are eleanring, some local authority courses and staff should attend at least one but up to all four of them. Although they all fall under the question are they compliant or not (i.e.0 or 1)i do not want to count them all seperately. Is there any way I can look at all eight cells related to these courses and assess the latest date to assess if they are complinat (in date) or non compliant (out of date)?
I have attached the spreadsheet which I hope will clarify things
=IF(AND(E4="",TODAY()-D4>730),0,IF(AND(TODAY()-D4>730,TODAY()<E4),0,1))
correct formula related to dates;
Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".
I have two worksheets. One contains my master data and the other my look up table.
Master Data: ....
What i try to achieve is: from 00:00hrs to 24:00hrs = 24hrs which is 1.0 day
Have made the attached spredsheet to calculate it correctly to 1 day. But if you look on the attachment the "total hrs" is saying 00:00 (it transfers to 00:00 when i am putting in 24:00). Basically how am I able to make Excel to display 24:00 insted of 00:00. Maybe there is an add ins availible like the pop up calendar but for times instead.
I need to create a formula that will calculate the status of a customer based upon an input code and the relevent date.
The attached workbook is very simplified but should help.
Sheet1 contains the date of input (col A) for the client (col B) plus a code in column C to help ascertain the initial status of the a/c.
In Sheet2 (col B) I need a formula or possibly a VBA solution that will create a commission status for clients with 'N' in column C of Sheet1 which is current for 8 weeks after the input date in Sheet1. Thereafter this status then need to show say an 'S' for standard commission.
This result will be linked to the date shown in B2 on Sheet2. Just to make it clear, I only need a letter as the result to trigger off the actual commission calculations.
(Thread: Max array with multiple columns) I got the following array formula:
{=MAX((A45:A47=A28)*(B45:D47))}
Here, where the value in A45:A47 is equal to A28, the maximum value is pulled from the same row, within cells B45:D47.
e.g.
A28 = Apples
A45 = Pears
A46 = Apples
A47 = Bananas
B46 = 10
C46 = 20
D46 = 23
The formula returns 23.
This is great. However, in order to control for a potential situation that will arise, I need to adjust this to find the first matching value within the data set. I do not know what the MAX should change to in order to find the first matching value.
Using the above example, if the data was now:
A45 = Apples
A46 = Pears
A47 = Apples
B46 = 10
C46 = 20
D46 = 23
How do you get the formula to return 10?
The position of "Apples" will change, hence the need for an array formula.
in this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.
how can i make the cells highlight that relate to the cell sum in group 3.
i.e when i click cell b38 cell b21 and b4 should highlight.
we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.
My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................
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.