I have cells that all reference from a 4x147 table, each row has one piece of data in, but only one piece from a choice of 4 cells (3 of which are blank).
the cells that are referencing this data, i would like to make them reference to all 4 cells, ignore the blanks and choose the one with the data.
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
I have the following code. I need the textbox values to pass to the cells when the option button (OB) is "moved away from"; that is, when I fill in the boxes while uner OB1, then select OB2, I want the values I put in to pass to the cells. Right now, the values passing to the cells listed under OB 1 are the values that are brought in under the enter event for OB2.
Private Sub OptionButton1_enter()
Me.TextBox1.Value = ActiveCell.Offset(0, 9).Value
Me.TextBox2.Value = ActiveCell.Offset(0, 10).Value
Me.TextBox3.Value = ActiveCell.Offset(0, 11).Value
Me.TextBox4.Value = ActiveCell.Offset(0, 12).Value
Me.TextBox5.Value = ActiveCell.Offset(0, 13).Value
Me.TextBox6.Value = ActiveCell.Offset(0, 14).Value
Me.TextBox7.Value = ActiveCell.Offset(0, 15).Value
Me.TextBox8.Value = ActiveCell.Offset(0, 16).Value
End Sub
But for the life of me, I can't get it to do what I need it to do. Excel 2010 user, and I've attached my file with the information, and descriptions.
View 5 Replies View RelatedIf I choose 1 thing from a list I want it to then fill in many cells with info.
For example: I have a color list to choose from, once I make my choice cells near it fill in with 1.5lbs yellow, 2lbs blue, 4lbs red,.....or what ever I have established as the "recipe" for my color choice. When I choose a different color from my list these cells each fill in with different values.
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
I am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
I am trying to use the SUMIFS function to sum the cells in one column if the cells in a reference column ARE blank, and if the cells in a different reference column fall within a date range.
-Column F contains currency, and is the sum range
-Column H contains dates as well as blanks, and I need the criteria to look for cells that ARE blank.
-Column A contains dates, and I need the criteria to be <= 01 Feb 2013 and > 31 Dec 2012
This is the formula I have tried, but it keeps adding the cells in F if the cells in H ARE NOT blank. Clearly, it's an issue with "<>" and probably a very simple solution, but I just can't figure it out.
=SUMIFS('2013'!F9:F59,'2013'!H9:H59,"<>",'2013'!A9:A59,">"&DATE(2012,12,31),'2013'!A9:A59,"<"&DATE(2013,2,1)
I have a spreadsheet list that has no merged cells. I need to copy that spreadsheet into another spreadsheet where each row is actually 2 merged rows. I thought that I could manually reference the first few rows, then drag them down to copy and hope that excel would be smart enough to pick up the pattern. But it doesn't, and keeps copying over every other row. Is there a way to quickly copy a spreadsheet with individual rows into a spreadsheet with merged (two rows) cells? Seems like it should be doable.
View 6 Replies View RelatedI have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
Is it possible to reference a cells value to define a range reference?
[Code] ......
I am trying to define the row value in the range reference with a value in a secondary cell?
I currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View 4 Replies View RelatedI want to make a lesson table which distribute the names to lesson choice priority.
You can see detail and explanation at attached file. LessonChoice.xlsx
I have a worksheet that has 8 activex listboxes. Each listbox is tied to the sames list of values (identified as a named range). The named range is a list of countries. Each country should only be selected once, therefore, I would like the selected country(ies) to not show up as a choice when the user makes a selection from another listbox. If this is too hard, maybe we can get a msgbx to appear anytime the users tries to select a country that has already been selected.
View 3 Replies View Relatedhow to use cells to create refrences in a formula so I don't have to hand write each refrence in a cell. I wanted to do this by creating a row with accending numbers (1,2,3,4,etc.) and a column with decending characters (a,b,c,d,etc...). Can this be done?
So for example I have a cell formula that reads the following
'Raw Data'!A2
Can I modify this formula so that the formula will automaticly refrence a different letter that corresponds with the cells location? So I would use this cell to change the (A) automatically depending on where the cell is in the spreadsheet. Likewise can I do that with the (2) the same way?
Does this make sence, or should I just continue to hard write each cell?
The reason why I need to do this is because the raw data is in a single column and the formulas that I need to use this data for needs me to convert the data into rows instead of columns.
I paste a weekly information sheet into a workbook. I pull information from cells within the sheet to form a weekly report. The sheet I paste is identical in columns but not always identical in rows; sometimes it may not have any information on one of the accounts. That account will be deleted and the sheet is 10 rows shorter. The sheet is over 1000 rows so it is too time consuming to add and delete the rows each week to match. My guess is I need to use VLOOKUP & OFFSET but I cannot get any of the formulas I tried to work. The account number appears on each 10 lines for that account. I have made up an example workbook to give an idea of what I need.
View 2 Replies View Relatedi have this formula:
=COUNTIF(INDIRECT(A2&"_RES"),"Yes")
A2 refers to a cell to complete the indirect query (DCA_RES) which refers to another range in the workbook.
Now I want to have count if A2&"_INIT" is "BB" AND if A2&"RES" is "YES"
So that I get the amount of records of resolved queries (Yes) that match my Initials (BB).
I cant think of a different formula that might work?
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
Essentially i'm trying to get the max of 5 cells at a time using the 'offset' reference. Here's my erroneous code:
Dim maxCell As Range
For Each maxCell In Range("y9:y150")
If maxCell.Value "" Then
maxCell.Offset(0, 1).Formula = "=max(maxCell.offset(0,-20): maxCell.offset(-5,-20))"
'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))"
End If
Next maxCell
I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.
I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.
I need the syntax that allows me to reference the cell directly beside my target cell for each row in a column
Like:
Col A Col B
S D
T F
S F
If i was targeting Col B
i have a code to save file referencing to a certain cell on a sheet, but how do i reference to 2 of them, since one is for a month and the other one is for year:
ChDir "X:SSS2014" ActiveWorkbook.SaveAs Filename:="X:SSS2014SSS Report* " & Format(Sheets("CP").Range("D3").Value, "mmmm yyyy") & ".xlsm", FileFormat:=".xlsm
so i have D3 and F3 to reference to together?
I've got an error whereby if i open a new workbook and enter the formula "=Sheet2!A1" in cell A1 on sheet1, Excel throws up a circular reference error. If i then try to select sheet2, excel highlights both sheets 1 and sheet 2 (i am not clicking incorrectly here).
Excel then thinks that i've entered the formula into the second worksheet - it dissapears from the 1st and the circular reference makes sense. In other words, it appears that i'm not actually entering a forumla on the hightlighted worksheet.
This was happening in Excel 2003 so i updated to 2007. I'm guessing this is something to do with an incompatibilty with another application but i can't work out what i might have installed. Does anyone have any idea of apps which might cause this type of error?
I have a rather extensive, for me that is, excel report of daily stats with rows for daily, weekly totals, monthly totals. All have various formulas along the way for various needs - some are simple sums, some are averages, some are percentages.
I need to make some new reports for various purposes that pull the existing information/calculations over into another sheet(s).
For instance - I want to do a report for *just* the week totals so I can do more analyzinig, but I don't know how to pull the already calculated info over so if it ever changes (as it sometimes does) - then my whole dataset reflects the change.
I need to reference a cell based on the results of two others - basically concatenate the two and then use the result.
A1 = G
A2 = 5
Then in B2, I need the value of the item in G5. So I need B2 to contain something like:
="[Value of A1]+[Value of A2]"
=G5
B2 now reflects the value in G5.
I know what I want to do, just can't get the syntax.
I am having an error when I try to run it and it appears not to like my destination (line bolded) - WHAT AM I DOING WRONG.?? Numrows = Worksheets("File1"). Range("A65536").End(xlUp).Row 'get the Row count
' LOOKUP Looklist! (Col B)
'----------------------------
For d = 1 To 228
' looks in Looklist B2(2,2), check against G1(1,7)
If Worksheets("Looklist").Cells(d + 1, 2).Value = Worksheets("File1").Cells(1, d + 6).Value Then
' If matches then copies G-x(lastrow,7) to looklist L2(2,12)
Worksheets("File1").Cell(Numrows, d + 6).Copy _
Destination:=Worksheets("Looklist").Cells(d + 1, 12)
End If
Next d
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies View RelatedI am building a spreadsheet that references 'LHA rent rates' listed in other cells.
Example table
Bed rate Code: LHA per week:
0 - £55.00
1 - £91.85
2 - £109.61
(Ie two columns with six cells. The first number before the dash are in a separate column. Each of these £ amounts will be in a separate cell.)
In another part of the sheet, i want to be able to type '0' in Cell 1, and this will trigger a formula in Cell 2 to retrieve the correct £ amount i.e., £55.00 in the 'example table' above. Likewise if i change Cell 1 to '2', then Cell 2 automatically updates to £109.61.
I think this would be a long IF formula in Cell 2, but how to do it.
I have been searching for this but have been unable to find an answer. Lets assume I create 2 tables. In the second table, I want to reference a specific cell from the first table in a formula (so that when I sort the column with that specific cell in the first table, the reference in the 2nd table will still work correctly and not change). How would I go about doing this?
View 9 Replies View Relatedi'm tracking hospital census with days of month in columns and patients 1 - XX in rows. this is basically what i'm working with.
A...........B........C...D...E...F...G...H...I...J...AG...AH
Patient...Payor...1...2...3...4...5...6...7...8...31...Total
1...........MCR.....x...x...x...x...x...x...d...............6
2...........COM.........x...d..................................1
3...........SP............s...s...s....s...d....................4
4...........MCR.............................x...d..............1
5...........MCR.............................x...x..d...........2
Total.................1...3...2..2...2...3....1..0.........14
The "total" cell formula is =COUNTIF(G7:AK7,"X")+COUNTIF(G7:AK7,"s")
S and X just identify where that patient is med/surg vs. skilled.
I'm looking for a solution that will reference column cells B2-BX and add the total from that row (AH) if the payor of that row is MCR, then another cell that would do the same for SP, COM and so on. so in this scenario it would be something like
COM...7.1% (SUM col AH where col B = "COM" ie. 1/14)
SP......28.6% (SUM col AH where col B = "SP" ie.4/14)
MCR...64.3% (SUM col AH where col B = "MCR" ie.1/14)
Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.
View 14 Replies View Related
