I want to enter a number in cell, select a value from a drop-down list, and have another sheet updated with that info. For example, on sheet1 cell A1 = $900, A2 = Western Region (drop-down list). Sheet2 would have a list of values that would update based on values entered in sheet1:A1 and sheet1:A2. Is this possible using formulas or do I need to use VBA?
View 1 RepliesNow the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies View Relatedi am trying to merge cells based on the input from another sheet. these input will change from month to month and i need the sheet to cancel the merge and merge again based on the new input.
Example.xlsx the file showing what i need it to do.
In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..
View 7 Replies View RelatedI have a spreadsheet that has a couple of columns that ask for "Move In Date" and the other "Move Out Date". These dates are used in other calculations so there can be only one or the other in each row. I have users that mistakenly either leave both blank or both popluated. Is there a way to stop them with a message telling them that a date needs to be entered or deleted, maybe with data validation?
View 1 Replies View RelatedI have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies View RelatedI am entering data (exam results) in one sheet for a form class (each form has a sperate sheet) but want this data to be updated in another sheet called "combined" which is a full list of all the data for the year group of students. How would I do this? I have copied the information into the combined sheet using paste special but it wont update?
View 4 Replies View RelatedI use Excel 2003. I need help building a macro, please, that will copy data to a specific page in another workbook based on two criteria. Here's the deal:
The data in Workbook A, Sheet 1, Cell A1 may contain the word ALPHA, BAKER, or CHARLIE. Cell A2 may contain the number 1, 2, or 3. Cell B1 contains the data I want to collect from various Workbook As and keep in a list to analyze.
Over in Workbook B, Sheets 1 through 3 are named ALPHA, BAKER, and CHARLIE. Rows A, B, and C are titled 1, 2, and 3.
How can I copy the data from Workbook A, identified as ALPHA 3, to it's place in Workbook B, Sheet ALPHA, Row C?
Furthermore, Workbook A is a one time form will be used many times. Thus, when I copy B1 to Workbook B, Sheet ALPHA, Row C, I need to paste the data in the first empty cell in the row.
i want to update values fon an excel sheet on a month by month basis. So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders. I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??
View 1 Replies View RelatedI am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.)
the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.
Example:
PARSED SHEET.
W
X
1
Equipment
[Code] .......
I have a worksheet designed to look like a form. That worksheet pulls data from an "Application Data" worksheet using INDEX and MATCH formulas. That part works great. The user enters an Application number on row 8 and it populates all of the other cells. In fact, in cell O7 (not pictured), I have it generating the row number the data came from (based on a MATCH function).
Here's my question--if they go in and change a value in one of the other cells, they should be able to press an "Update" button. That will then kick off a macro that goes to the "Application Data" sheet, finds the row that has the Application Number (from row 8) and updates the values based on what the user entered on this form.
How do I write the statement that selects the row based on the Application Number?
Macro for getting colored cells in specific range in a separate sheets in to one consolidated sheet.
Have attached an excel as a reference.. marked yellow and red.
I'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
I am tracking my sales interactions and am trying to link cells so when I update on one sheet it updates my last "customer touch" on a master sheet. Current formula-
On "sheet1" I selected a cell and used this formula... =sheet2!$b$7
The problem is that I want to keep a history of what was in that sheet 2 B7 cell so i insert a row (making B7 move down and become B8) and enter new info into the new b7. if I go back to sheet1 the formula in the selected cell becomes =sheet2!$b$8 I want it to stay to =sheet2!$b$7 no matter what I do... insert rows, delete rows etc...
in short what i am trying to do is have a cell in sheet1 show my last interaction with the customer and keep a history of in sheet2-infiniti.... with the caveat that my last interaction on sheet 2 etc... is listed first (thus the insert row)
Attached simplified sheet gives background - but need to update the cells in the aggregate monthly table C44:C55 based on data in single cell above, and according to the correct month. Need do this without the previous months data changing.
Month is determined by cell E8 and data to be updated to the agg month range is to come from cell C15. The month date will change automatically as will the data in C15.
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View 4 Replies View RelatedI am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable and change the background colour of some cells as follows (but only disable the cells on the same row):
columnA = Number then disable cells D, F, G and change to red
columnA = Link then disable cells E, F, G and change to red
columnA = Image then disable cells D, E and change to red
Trying to run a VBA to colour a range of cells based on the input of a name. I can write the basic level VBA to colour one cell by numerical input but struggling to see how this can be adapted to colour a range of cells and if instead of 1,2,3,4 etc the case could be replaced as text: eg Joe, John, Jason etc.
View 9 Replies View RelatedI have a macro code for conditional formatting. The first 2 lines of the macro are
Private Sub Worksheet_Change(ByVal Target As Range) ....
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.
View 5 Replies View Relatedi have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...
View 9 Replies View RelatedI want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)
View 8 Replies View RelatedSo I'm creating a UserForm that takes user input to create a worksheet based on the values input. My question is: How to select a range of cells based on a variable input value? For example, it asks how many competitors there are, and the user can input 5, 8, 10, etc. So I want that many cells in Row 1 selected, whatever the value input is.
View 9 Replies View RelatedI am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?
Here is what I am looking for:
If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16
If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.
Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.
If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.