Let's see who's got the logic.
What I have:
Column C containing either 1 or 0.5 or ""
Column E starting at E9 which may contain "deal" or a few other things
Column I starting at I9 which may contain "here", "away", or "no"
Cell J4 which will contain a number........
I have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.
View 9 Replies View Relateda
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
In A1 I have a list. The list Manager, Assistant Manager, Casual In B1 I have a list of places America, UK, Mexico there are amounts in A3 A4 A5 and B3 B4 B5 If Manager is selected then America I would like A3 to display in C1 Depending on which variations are picked I would like the amounts to display in C1
I know how this formula which I've used before but I don't know how to include all the variations.
=IF(ISERROR(FIND("Manager",A1)),"",A3)
I have been trying to figure out for quite some time. I searched around the forums, but I believe I am not wording what I need correctly. Pretty much I have an example here to follow:
x.....A.........B...........C
1 Name....Color.....Number
2 Bob.......Blue.......Five
3 Jan.......Red.......Three
What I want, is a formula that pretty much says search through column A for something, then show me what is in Column C that corresponds, in this cell. So if i have a criteria for "Bob", I want it to return "Five". But I want it to be open, so that I can put bob or jan in another cell that it references.
I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.
Is there a simple method for doing this, as the data will routinely be
different lengths in that column.
Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?
I am working with two spread sheets; one is my take-off/summary sheet which is where I populate all the data for the project I am working on, the second sheet is also a summary sheet but it has also other functions; hence, I need to have two spread sheets.
I need to link/reference the data I have on my take-off sheet row 119, column K through ACL (lots of columns) to my other sheet but it now needs to be referenced in a column direction. At first, I enter = (sign) in T9 (the sheet I need to bring the data in) and then I go to the take-off sheet select N119 and hit enter, this first data is good, but then I try to drag it down to populate all the data from the other sheet but even though I lock the row (N$119) and let N free the formula doesn't populate this: =!N$119, =!O$119, ='!P$119 and so on so for...all what it gives me is =!N$119 all the way down...
I've only done a small amount of macros with VBA.
I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
I'm trying to create a macro that transposes data from columns to rows.
My source data is laid out so Column A and B contain item identifiers, and then the header for Column C to Column S contain dates (March, April, May, etc) and the row data below contains quantities for each month. This is on Sheet1.
I need my end data (on Sheet2) to have the item detail in Column A and B, the quantity in Column C and the Date in Column D. If there is a date that has no quantity then it should be skipped.
Sample Source Data (Pipes added for clarity, they aren't in the actual data)
Part |Description |June 4 |June 11 |June 18
A | PartA | 5 | | 12
(Please note June 11 has no quantity)
Sample Destination Data:
A | Part A | 5 | June 4
A | Part A | 12| June 18
Below is the start of the code, obviously I have a ways to go before it's fully functional but hopefully you get the idea how I'm trying to attempt this. Where I'm having issues right now is referencing columns by number. Is there a way to do this so I can do Column = Column + 1 to advance columns to the right? Or any better way to address this all together?
HTML Code:
Sub ConvertToRows()
Dim ReviewRow, ReviewRowEnd, PasteRow, ColumnNumber As Integer
ReviewRow = 2
[Code]....
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies View RelatedI am trying to create a spreadsheet that will take all my grades from the semester (I'm a college student) and compute my GPA. I managed to get a weighted average for each class, but the grade is on a scale of 1-100. I need to convert that to a letter grade, and then a 4.0 scale. How can I set it up so that it will return "A" if 90<Grade<100, "B" if 80<Grade<90, etc.? I'm apologize if this is something simple, but I've been trying to find the right function for a long time.
View 5 Replies View RelatedI work for a construction company, Someone else created a workbook a few years ago with 140 similar pages, each listing a separate piece of equipment as a service record. I need to print these out randomly for mechanics to update with new information, as they service the machines. However, some of the pages of the original workbook are goofy and the workbook as a whole could be improved. But, oh that many pages!
I have worked on a new template page for entering information to make the worksheets all cohesive and updated. Is there an EASY and/or FAST way for me to copy this page 150x to make a new workbook, and just enter my data onto each new page? I really don't want to reformat each page of the original workbook individually to see if I can fix all the glitches and weirdnesses that have occurred on the pages over the years. I thought it would be much easier to retype the data onto new pages.
I am having a little difficulty getting my vba to correspond to what i want it to do. I have columns that can vary with how many rows they have weekly. The all start at P2, R2, V2.
These columns always have the same number of rows as each other, but the number of rows varies.
I compare my R2>P2, and if it is return the number in R2. If not, i am looking to take P2 and round down to the nearest .95. (ex: R2 is at 3.45. The value that should be put in in V2 is 2.95. Since the values all change as well, it needs to compare: .96-1.95, 1.96-2.95, etc. all the way up to 15,000.
I know it can be done with if's, whiles, or from i to x, but my experience is in vb.net, not vba. And, since i am new at this, i can write the code, but don't know how to make it correspond to a new excel filename weekly.
My workbook is a variation of the below repeated on a number of sheets.
Sheet 1
A
B
C
1
Name
Carrots
Peas
2
John Smith
0
7
3
John Doe
2
1
4
Joe Bloggs
4
0
On a separate summary sheet Im struggling to create a formula that would look through all sheets and Sum where 'John Smith' appears and then sum the number of 'Carrots' (if any) and 'Peas' and then the same for any other names that has values attached to it. The names in each sheet may not be in the same order or appear at all otherwise I would just use a standard sum on each sheet.
I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
For example in this formula I typed in WWW as the max:
=SUMPRODUCT(($F$5:$WWW$5>=C2)*1,($F$5:$WWW$5<=C3)*1,ABS($F$6:$WWW$6))
If I try to type in a value higher than WWW it will give me an error. I was just trying to put in an amount that I would never hit within the row.
I would like a formula that checks for blank cells only between C1:C10 and E1:E10 and then insert the first and last names of the person associated with these blank cells (which are located in A1:10 and B1:B10) on another worksheet, which for the sake of argument can be called 'sheet2'.
View 2 Replies View RelatedI need assistance referencing cells in other sheets. I have a summary tab which I would have each refenced sheet listed (named for the activity being billed). In a separate cell in the summary tab, I want to bring back the value of the total amount billed for that activity. The total value is in the same cell of each sheet, as all invoice tabs needing to be referenced are identical in layout.
There is one hitch to this, the sheets referenced have not yet been created (that would be too too easy), and the activity list in the summary sheet has place holder names that will change when the activity being billed is defined (hence prompting the biller to create the sheet to be referenced).
Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3
What I am trying to do is have in the next column a formula which returns
the number adjacent to it multiplied by the number of blanks between it and
the next non-blank cell.
So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc
My current worksheet is being built for use in Eve-Online, a game I play. In specific, I am keeping track of profit made by manufacturing different items in the game. I am trying to build it like this:
Column 20 is a list of items, and the next 7 cells to the right of each item contain the mineral requirements for that item. So C20 says Bantam Frigate, C21-C28 will all contain numbers. Now, I am trying to create a function in the upper-right cells (like A1-A8) where if I type in "Bantam Frigate" into A1, A2-A8 will fetch the numbers that I put into C21-C28.
Now, I will be working with thousands of items, so I have to make sure I name both the 'database' and the query exactly right, but that's not a problem to me. Can I make something that will reference a value from (cell+1 column right) or (cell+2 columns right)
I have a row of cells on one "Sheet1." I am trying reference these cells into a column on "Sheet2." I know I can reference one cell at a time, but I have a large number of cells in the row on Sheet1 and I know that there has to be an easier way to do it, I am just not excel-savvy enough to know what it is.
I know you can copy and "paste special" (transpose), I am just wondering how to do that while referencing. I have heard that the "offset" function can be used, but after extensive trial and error, I haven't figured it out.
I have set up a spreadsheet in order to track and calculate the number of students we teach at our organization. It is running prohibitively slow when entering new data. On one sheet each individual session is entered (the school, program, date, number of hours etc.) This is the Session worksheet.
On the next sheet(the Program worksheet) the maximum number of students for each unique program (this is usually a combination of school and the program) is calculated to ensure we are not counting students we teach weekly as new students. This is calculated using a formula:
:{=MAX(IF(Session!G:G=Program!A2,Session!D:D))}.
"Session!G:G" = the unique program name on the Session worksheet.
"Program!A2" = the unique program name on the Program worksheet.
"Session!D:D" = the number of students in that individual session...........
Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?
If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.
I currently have 3 sheets 1, 2,and 3.
Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column
I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.
I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.
Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...
I have 153 rows of data to name, with a different offset formula per row.
View 8 Replies View RelatedAt the moment I have a macro that is assigned to a button. When clicked, it creates a newline, create and copy a column in another sheet. This code works fine :
[Code] ......
What I now want to do is make a cell called "Total Number of Sites" and allow user to input data into "D3" manualy. eg if 3 sites, user input 3 in D3 so D3 = 3.
Since total number of sites = 3, Instead of clicking that button I made 3 times, I want to call the macro 3 times using a nother button.
However it doesn't work. This is what I used:
[Code] ..........
I am currently trying to organize numerous numbers by fractions, and it just will not set up right.
1 1/2
1/2
3/4
2
1
2 X 3/4
1 X 1/2
1 1/2 X 3/4
What would be the best code used, or method, to ensure that these numbers organize correctly?
I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.
I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")
I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".
Can the function be used in this way or could someone suggest another way of handling this?
I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.
What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?