After I run Hlookup on a product, I would like two variables, Row and Col, to assign the actual row and column of the spreadsheet to the location in the table. I have a small worksheet/program that I have attached, what I am trying to do. The program works, but my code is very long for the little bit of information.
Sales.xlsm‎
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
I have an issue with the below code:
VB:
LastRow1 = ActiveSheet.UsedRange.Rows.count
ActiveSheet.Range("R1:R" & LastRow1).FormulaR1C1 = _
"=IF(RC[-1]<>"""",HLOOKUP(RC[-1],Sheet2!R60:R61,2,FALSE),"""")"
The issue I have is that in sheet2 the HLOOKUP range i want to lookup will not always be in R60:R61 these rows vary depending on the amount of data above this range... is there a way of incorporating some sort of
VB : LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).row + 1
To locate the bottom of the data and the start of the HLOOKUP range?? if is there some way of re-coding it?
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)
To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)
The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)
=HLOOKUP(G8,'Headline Assumptions'!$C$32:$F$37,E8,FALSE)
Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?
This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion. I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".
I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:.....
I have a table where the columns are named: A, B, C, D and E and the lines are named: 1,2,3,4 and 5. All the cells of this table are filled with values.
I want the function to return the value on the correspondent cell everytime I type a combination of column and line. For instance if I type "B2" the function would return the value on this position...
Actually I am looking for something like a combination of the functions "hlookup" and "vlookup"...
Can a HLOOKUP function look for a column heading in more than one 'table_array'
View 4 Replies View RelatedI am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.
So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
I'm trying to use countifs on a filtered table to find the sum for a column based on 2 criteria.
I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!K7,ROW(Sheet3!K7:Sheet3!K7:K20000)-ROW(Sheet3!K7),0)),--(Sheet3!K7:Sheet3!K20000="Yes"))
for a single criteria which is great, but I also need it to look at column B.
In a non-filtered table it works as:
=COUNTIFS('Sheet3 '!$K:$K,"Yes",'Sheet3 '!$B:$B,B5)
Is there any way to do this?
I imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do...
Find MIN in Column F
After finding the MIN in F (let's pretend F222 is the current min)
Show the text in the field next to it (in this case display text from E222)
I have a spreadsheet containing 3 sheets. MW01 (first floor inventory), MW02 (second floor inventory), and SEARCH.
The sheets contain a part number in one column and a shelf location in the second column. I currently have over 1000 parts listed and growing.
I want to be able to type a part number into a cell on my "search" sheet and have it return the shelf location of that part.
Is there a good way to find out after the exists returns True in which place in the array the Z is located in ?
...
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
Z = a(i, ii) & ";" & a(i, ii + 1)
If .exists(Z) Then
msgbox "The array location for existing Z value is ..."...
...
I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?
BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.
I need to assign headers to a table according to the file name and location. I have attached the example spreadsheet. Sheet1 contains the table with the data and Sheet2 assigns the headers to each file and location. The code is skipping headers and I can not figure it out.
Macro Example.xlsm‎
I have a table with customers and their addresses (sheet2) and I need to find their GPS locations in sheet1.
I guess the logic would need to be something like this:
find CityName and StreetName in sheet1 where StreetNo is between minStreetNo and maxStreetNo return GPS X and GPS Y
with the formula or VBA code?
Example.xlsx
i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.
I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)
I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.
Cell
A1 Number >2000300040005000600070008000900010000
70 2864295717148571000114312861429
84 24236148059971883795610751194
[Code].....
I am looking for a VBA Code which will:
1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
[URL] to locate the first negative value. Several options are offered, of which these would work for me ....
VB:
=LEFT(ADDRESS(ROW(),CELL("col",INDEX(Range,MATCH(True,Range<0,0))),2),
FIND("$",ADDRESS(ROW(),CELL("col",INDEX(Range,MATCH(True,Range<0,0))),2),1)-1)
to return the Column Letter
And
VB:
=CELL("col",INDEX(Range,MATCH(True,Range<0,0)))
to return the Column Number.
However, I need to take it to the next step, in my application, B2:Z2 are year dates, 2014 et seq B3:Z3 contain $values , one of which might be negative So if D3 is the first negative, the solution will return the label "2016". In the same thread, the Editor offers a refinement needed for my solution, but is beyond my skill level to implement: Any solution to avoid #N/A errors for the ranges where there are no negative values? I would just use
IF(ISERROR(formula),"-",formula)....
I have a value stored in variable A. I need to search one column for the location of that value and return the row value. there is no chance for a duplicate entry. Is a loop my only option, or is there a find command in VB?
View 9 Replies View RelatedI currently have a spreadsheet that refers to a different spreadsheet, with different expenses each month, with the months going across from A,B,C,D etc.
Because the formulae on the expenses sheet refers to cells such as O13 O15 O27 etc, at the start of the next month the 'O' has to be changed to a 'P' to give P13 P15 P27
This doesnt sound much but there are over 10 different spreadsheets that are all currently changed manually. Is it possible to have a function referring to a cell containing 'O' which when changed to 'P' changes all the formulae to refer to the cell next to the original.
EG Instead of O13 it would be [variable]13
Essentially this will mean that a master sheet will be able to change all the excel spreadsheets in one go.
I have 2 columns:
VAL0085597220EXP0000207731
VAL0122473124EXP0000207864
VAL0169412496EXP0000207879
VAL0173317027EXP0000208330
VAL0172476750EXP0000208703
VAL0173317028EXP0000209148
VAL0047743340EXP0000209666
VAL0176241996EXP0000209668
VAL0169594337EXP0014327602
VAL0047743872EXP0014327702
VAL0158185837EXP0014329002
I am trying to match where these are located in another column
I'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.
Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.
I have a formula in Cell A1. It tells me the cell location of the last number in column E. So, for example, I input the number 44 in cell e85, the value for cell A1 becomes $E$85.
Let's say I now put the value 33 in cell e86, now the value for A1 becomes $E$86.
I want to calculate the average of the values in column E, all the way from E1 to whatever is in A1.
Here's what I need: =AVERAGE(E1:<whatever is in A1, like $E$85>)
How do I write the AVERAGE formula to make this work?
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.