The CONCATENATE function casues the function to error and highlight the $B$11... array section
I have a table with each row representinginformation about a product a customer owns. I've added columns where I'm using the HLOOKUP function to go to another sheet in the workbook to find the products price. The issue is there are a dozen different pricebook worksheet's becasue of various parts of the work. I've added a column which displays the right pricebook sheet name now I'm just trying to get it that value into the HLOOKUP formula to be the shetname portion of the array value.
I am collecting a lot of prices from over 60 countries. Each country has a seperate sheet in a workbook, named "Country" Spec (eg Australia Spec, China Spec etc.). These sheets are all setup exactly the same.
I have the following sheet wherin I want to show the data coming from all the different sheets:
| a | b ----------------------------------- 1 | Australia Spec | "Formula" ----------------------------------- 2 | Bangladesh Spec | "Formula" ----------------------------------- 3 | China Spec | "Formula"
In B1, 2 3 etc I want to do a lookup of a fixed cell on the correct country sheet. Therefore I want to include the sheetname in the formula. Thus I would like in B1 the content of cell q13 of sheet Australia spec. In B2 the content of cell q13 of sheet Bangladesh Spec.
I am guessing that this is fairly simple but i tried finding an answer on the internet and had no luck.... can i have a formula for a cell that returns the sheet name? or a macro?
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'm currently using an HLookup formula to create an output tab for a huge set of input data, but whenever I add new rows to the input tab, I have to adjust the formulas for each individual output cell.
For example, my output formula is currently "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$384,116,FALSE)," but if I add a new row to the input data, I have to adjust the table array and the row number for the formula. So, if I added a row before the 116th row, but wanted the output to be the same for this cell, I would have to update the formula to "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$385,117,FALSE).
Is there any way to adjust this formula so that it will automatically update to produce the same output without manually updating the formula each time I edit the number of input rows? Would an index/match formula work, and if so, what would it look like?
Following is an example of an HLOOKUP formula I'm using:
=HLOOKUP(A1,$B$2:$E$5,4,FALSE)
The problem I'm having is that if a user inserts a row within the range B2:E5, then it throws my lookup formula out, because the formula should now be looking at row 5 and not row 4. How do I get the number 4 in the formula to be a variable that always selects the last row of the specified (variable) range?
This will take a mixture of at least HLOOKUP and Average
I have a table of 2 columns; 1 column is dates from Jan. 1st to Dec 31st; 2nd column is inches of rain on each day. Several are 0 inches but I am needing to take the the average of each month of only the days that have greater than 0 inches of rain.
Getting the average of each month is easy but canceling out the 0 inch days is throwing me off right now.
I have a set of 50 large spreadsheets each with the same size and structure.
I have a summary sheet which contains cells that each contain a single-cell 3D range across all the sheets.
But I'd like to modify this summary sheet to find a way of summing a subset of the 50 sheets according to a given criteria.
I imagine this could be a single criteria added to one cell in each of the 50 sheets.
Or perhaps it could be a letter in each sheet's name. eg, if the letters used as criteria are say G, P and S, I would name the sheets something like G1, P2, G3, S4, P5, ....S49, G50.
Perhaps this could be achieved with the indirect function or will it be necessary to resort to VBA?
I'll be creating a graph to which shows account balance by week, by account. The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet..
bare with me here as its hard to explain ................
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:
The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.
With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B 1/1/2011 # 2/1/2011 # ... 12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
I am facing a problem using Hlookup function. I am using Hlookup to do a control check of consolidation i am doing here, however it only returns the first found value instead of all correct values (or range).
I am attaching a file with an example. In the file formula is used in Supply(S) sheet on row 73.
I have various 6 x 6 blocks which contain just numbers, in another part of the spread sheet I have a 1 x 6 block of numbers. What I need to do is to check whether any of the numbers in the 1 x 6 block appear in any of the 6 x 6 blocks.If that does occur then I want to make a specific cell increase by the times that the match happens.using HLookup but just got errors and the If Statement, although did work, went on and on and on.
Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie ‘2’ does not increment with the drag. Is this correct – or is there a way of making the 2 increment?
I have some experience with programming, so I understand the theory behing things like loops, arrays and functions etc...Only problem is, I need to make a program at work to ease workloads and I'm afraid I have no idea where to begin.
Basically, I need something similar to a lookup, but thanks to the limit of IF statements available in the formula bar, I can't write what I need.
The program I need to write wouldbe similar to an autofill function. On Sheet1 of my spreadsheet, there are fields: Name, Address 1, Address 2, Address 3, Postcode, Area, Rep.... (The last three are the most important) On sheet two, Postcode, Area, Rep already have data entered in thier respective columns. (D3, E3, F3).
What I need done on Sheet1 is when the user enters the first two characters of a postcode, the 'program' will lookup the entire row respective to the postcode that is entered. when enter is pressed, the Postcode will copy itself from the other sheet as well as the Area number and the Rep into the empty fields in Sheet1. The other way to do this is look through a long list until the right postcode for an Area is found then assign the Rep the Contact details (Add. 1, Add. 2, Add. 3...).
I have a huge data and I need to find values through HLookup and than have to find the sum of that whole column.But when I tried to to so It is showing error as Formula broken.
The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
I have a formula that looks like this: =HLOOKUP('Output'!$B$3,'Input'!$B$1:$P$300,'Input'!$A3+1,FALSE)
It is working very fine, when the cell has a value, but when it doesn't - it will return 0. So my question is: is there any way to make it return certain value or word like "No value" instead of zero?
I have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.
I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below:
I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function?
