Create Array By Referencing Other Cells?
Nov 5, 2013
I have the following problem:
In cells A1 to A4 I have 4 numbers (8,7,12 and 15).
In cells B1 and B2 are the numbers 1 and 4
Now I want to use the max function to find the highest value in the cells A1 to A4. But instead of typing =max(a1:a4) I want to use other cells that tell excel where to look. What I exactly want is to say =max("a"&B1:"a"&B2) because in B1 and B2 the rows are indicated from which to which excel shall find the maximum.
View 4 Replies
ADVERTISEMENT
Aug 20, 2014
The macro I have has 2 arrays that at present has a representative sample of the data I will be looking for and hence allocating a relevant code. I thought the if MyArr was now Sheets("Sheet2").range("A1:A200) and similarly for MyAssettype using column B. however i just get a Runtime error!
View 5 Replies
View Related
Oct 10, 2013
I have this MAXIF array function that I am trying to use but I need it to reference the Last Row and not the absolute refererence. I am a novice at VBA. The first max if array is the one with my effort to try and get the last row. It doesnt work. The second one works but references the cell.
Code:
Range("U2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:RC1 & LR=RC[-20],'Monthly Production'!R2C19:RC19 & LR))"
Range("V2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:R8729C1=RC[-21],'Monthly Production'!R2C21:R8729C21))"
View 1 Replies
View Related
Mar 11, 2009
I'm having a problem with the below array. The column heading in cells E7:H7 Never change and i would like to sum up the appropriate data below those headings according to the what is in cell D4. The formula in cell A9 works but only if the string is exactly the same as what is listed in cell D4. Is there a way to reference cell D4 like i have in cell B9. I'm looking to get the formula in B9 to work, but i'm missing something.
Sheet1
ABCDEFGH2Assumptions 3Collateral Sample 1{60;90;FCL} MACRO Populates 4Collateral Sample 2{90;FCL} {60;90;FCL} 5Collateral Sample 3{FCL} 6Collateral Sample 4{60;90;FCL} 7 Current6090FCL8 990 123410200 658711360 4682212
Spreadsheet FormulasCellFormulaA9{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E9:$H9))}B9{=SUM(IF($E$7:$H$7=$D$4,$E9:$H9))}A10{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E10:$H10))}B10{=SUM(IF($E$7:$H$7=$D$4,$E10:$H10))}A11{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E11:$H11))}B11{=SUM(IF($E$7:$H$7=$D$4,$E11:$H11))}Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View 9 Replies
View Related
May 8, 2009
I have data that I received from a feed. Each cell displays a value like 100, 200 or 300 or a time like 10:41:52 AM, but the formula bar depicts the array information ie =Q|TS!'AVAVA,45000,DEIPSCT'.
I'm working with the data where basically the time is the independent variable and the number is the dependent, very basic stuff BUT I cannot figure out how to reference the time.
Just trying to write a very simple if function such as =IF(G17="8:50:17 AM","start") and I can't figure out how to do it. Cell G17 is showing 8:50:17 AM.
View 9 Replies
View Related
Jul 11, 2014
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.
View 8 Replies
View Related
Apr 15, 2014
At my work we are using Excel as a CMS to produce SQL scripts for insertion into a database. The data is then turned into mobile web pages. This involves multiple workbooks - 1 for each main page with the sub-pages as extra worksheets in the book.
Much of the data is duplicated so we have created master workbooks then copied them and laboriously referenced the relevant cells back to the original. That way if any data in the master is changed, all copies will change simultaneously.
This is obviously not the most efficient way of publishing web pages but we are stuck with it for now. So I am wondering if it is possible to write a VBA code to create a copy of the master and then reference all required tables in the worksheets automatically? The tables are all named ranges.
View 9 Replies
View Related
Sep 27, 2006
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 Related
Dec 20, 2006
I am trying to create an Array, and have searched through many threads on this website and realise I don't have a basic understanding of Arrays. Specifically the ReDim Array function.
View 2 Replies
View Related
Jan 25, 2013
I have a doubt, in VBScript for excel is possible create an associative array?, for example:
PHP Code:
Dim columnValue(26) As Variant columnValue("A") = 1 columnValue("B") = 2
So if I want to know the value that corresponds to the letter B the result must be: 2
MsgBox columnaValor("B")
View 2 Replies
View Related
May 24, 2014
I have attached a spreadsheet of data that I would like to reduce.
In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.
In the table below that (rows 28 to 37) I have set out how I would like to present the data. how to create an array formula that sums the above data months into the criteria of column A.
View 5 Replies
View Related
May 15, 2014
Array set for workbooks? I have more than 10 workbooks to update
refer to attachment. the VBA code is in Consolidation SOP.
View 13 Replies
View Related
Jan 6, 2009
I have an array which I would like to extract the information and put it in a table in a spreadhseet. Does anybody knows a faster or better way to do it different than using a for next?
View 2 Replies
View Related
Apr 15, 2014
As part of a larger formula, I need a way to evaluate a range row by row and generate an array with the results. More specifically, I need to evaluate whether at least one of the cells in a row has a specific value.
So given the following table:
1
1
1
2
2
1
2
2
3
3
When querying the table for the value "1", it should result in the array {TRUE, TRUE, TRUE, FALSE, FALSE}.
How can I achieve this?
View 4 Replies
View Related
Mar 26, 2014
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.
View 8 Replies
View Related
May 2, 2009
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.
View 5 Replies
View Related
Jun 8, 2009
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 Related
Oct 4, 2009
I 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).
View 5 Replies
View Related
May 22, 2006
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
View 12 Replies
View Related
Nov 20, 2008
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)
View 4 Replies
View Related
Nov 24, 2008
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.
View 2 Replies
View Related
Jul 13, 2009
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...........
View 4 Replies
View Related
Jan 16, 2014
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.
View 4 Replies
View Related
Oct 14, 2007
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.
View 9 Replies
View Related
Dec 8, 2008
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...
View 9 Replies
View Related
Nov 11, 2011
Sub Macro2()
Dim stgSheets As String, Cnt As Long
Dim arrSheets()
Cnt = Sheets.Count
ReDim arrSheets(Cnt - 1)
For i = 1 To Cnt
stgSheets = stgSheets & Sheets(i).Name
arrSheets(i - 1) = stgSheets
'sSheets = sSheets & "", ""
Next i
[Code]...
View 2 Replies
View Related
Nov 12, 2006
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.
Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)
I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
View 9 Replies
View Related
Feb 1, 2008
I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please.
Below is the code i am using to allow for my sub: ....
View 8 Replies
View Related
Mar 2, 2014
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?
View 3 Replies
View Related
Aug 6, 2014
Applying my indirect formula to the rest of the cells in my sheet properly.
I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ....
The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.
So I have quite a few rows to get through I want to be able to autofill so it looks like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
2
Sheet 2
=(INDIRECT("'"&A2&"'!$A$5"))
[Code] ......
As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ......
Excel isn't intuitive enough to know I want to move cells in the referenced sheet.
View 5 Replies
View Related
