I am trying to get my formula to reference directly to the right of the look up value but it keeps returning the diagonal value that is located to the right and up one cell.
excel 1.jpg
excel 2.jpg
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'm working on a spreadsheet for a gaming community. This sheet is used by more than 3000+ players and growing. It has to be updated roughly twice a week for new inventory items, which can range from 1 to 3 new items weekly.
To make it easier on new and existing members, I want to create a way to make updates with the spreadsheet easier and to allow players an easier way of updating the spreadsheet rather than re-entering all the data over and over again twice a week.
So, I started to use VLOOK in some formulas to look at the data and pull the corresponding information. However, this same information is used throughout the sheet and referencing this information has brought about #VALUE errors that I cannot seem to figure out how to get around.
I've included a copy of the spreadsheet to download and review in hopes of finding a solution.
Here's what this speadsheet is doing:
MyInventory is where the players will enter all their items, with some being drop-down selections and manual entry.
The LookupedInventory worksheet is where the VLOOKUP (columns C,G,J,Q,etc.) formula is used to look up entries in the MyInventory worksheet and pull their values over to the LookupedInventory worksheet.
Now these values from the LookupedInventory will be carried over into two other worksheets, InventoryTableAttack and InventoryTableDefense. These two worksheets are where my #VALUE errors are appearing. If the cell referenced is empty, the #VALUE error appears. However, if the cell references a number, the rest of the formulas tied to to this cell reference work. However, since there are errors, the rest of the worksheet cannot function until I fix all the #VALUE errors.
So, what I'm seeking is to see where my issue is and what I need to do to fix it.
If I need to take a different approach to this, please let me know what that is and how to go about doing it.
I've even thought about a macros that copies and paste all the current data from an older spreadsheet to the new one, but I'm not that good at macros. If I can stay away from macros, that would be good, but if not, then I'll have to dig in my heels and start leaning.
I am trying to use the vlookup function but the range of reference has that little green triangle in the corner and if I f2 each cell that triangle goes away and the vlookup function works great. Do you know what is going on here? And also, a quick way to convert all the cells with the green triangle to be without the triangle in a whole range? I don't want to have to go to each cell individually and f2. I've attached an example worksheet for reference to what I am describing. vlookup format.xlsx
View 2 Replies View RelatedSo my issue this time is that I have a bunch of columns that correspond to a month. If the user wants to view data from that month column then they have to link to that cell specifically (the worksheet being used is different than the one in which the month columns are)
My goal was to create a single cell in which the user typed in a month abbreviation such as jan, feb, mar.. so on and so forth. From there they correspond to a vlookup table which tells you which month is in what column (because that part never changes) so jan is in column c and feb is in column d... continuing until december is in column n. So the vlookup corresponds to the table and that works fine, type jan into the cell and the vlookup returns c. So i think this will work.
My problem is that in the cell I am typing this in I want it to be a part of a whole cell reference such as c5 or d7 where the 5 or 7 is a constant (I have that part down) however the reference is to another sheet. So in sheet 2 I want to reference sheet 1 i.e.
Formula: [Code].....
<-- is the desired return.
Right now (in sheet 2) I have
Formula: [Select Code] .....
Which returns the desired d5 but as text and not referencing sheet one. I tried
Formula: [Code]....
But this makes the vlookup malfunction (i think it starts looking in sheet 1 for the month cell but it is not and can not be there.
SO how do i force a vlookup to turn into a cell reference, and how do i make that cell reference come from a different worksheet without causing the vlookup to malfunction.
Attached File : examples.xlsx
I have made a toggle button to turn diagonal borders on and off in a single cell. Is there a way to make the button transparent, such that the button can be placed over the cell itself and the contents of the cell will be visible underneath, no matter the condition of the toggle?
View 8 Replies View RelatedI am simply trying to do a standard Vlookup but since there are multiple returns for any given match the best results for me are to return them horizontally on the same line in seperate columns.
My original formula holds all the same information, nothing is different except where I want the values to be returned. Here is the original...
=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,3,0)
and...
=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,4,0)
I have been putting formula 1 (Single Return) into Column X but need the multiple returns to return to X, Y, Z, and AA
I have been putting formula 2 (Single Return) into Column AB but need the multiple returns to return to AB, AC, AD, and AE
There should not be more than 4 returns per match so if anyone could help me with a formula to return each of these formulas horizontally like this.
I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).
The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".
I was trying something with INDEX / MATCH, but can't get it to work.
See the attached example for an illustration of my problem. Essentially, I am trying to reference the first two numbers of an account number when using VLOOKUP. The function then references a table with account classifications based upon the first two numbers of each 10 digit account. This seems so basic, but is driving me up the wall!
View 3 Replies View RelatedI have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula.
For instance, normally I would write:
VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False)
(I hope got the translation to English language Excel correct)
Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it).
Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.
I am looking for a simple non ARRAY formula (OFFSET)? that will use range A1:E5 to create a new range which will include cells A1, B2, C3, D4, E5 (the diagonal). The sum of this formula would give: 30 ...
View 9 Replies View RelatedI have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
View 2 Replies View Relatedis there any easy way to take the sum of a really lond diagonal? I know i could use a macro, but I was wondering if there was a way to do it with formulas, the sums will ultimately be used for a graph,
View 9 Replies View RelatedWithout VBA, how do I sum diagonals such as N2 and N3, but also like N7 where the values are limited to row 13? Hopefully, the post illustrates better than my words...
View 9 Replies View RelatedI am stumped on coming up with a dynamic formula that calculates a value based on an increasing number of "sums of products". Specifically, I need to calculate the total number of repeat users based on a "decaying' repeat rate. In other words, I need it to calculate = Sum[... +(d5*f5)+(d4*g4)+(d3*h3)]
or in equation form:
where
n = (1, "infinity")
and
i = {1, 2, ... (n-1)}
with
"Xn" is the calculated value of repeat users for month "n" (output/result cell)
"mi" is the number of new users (in month i)
"Nn-i" is the column 'number' of the repeat rate value (%) multiplier
Here is a screenshot of the first couple rows to illustrate:
I actually have about 70 months of data (rows) and roughly the same amount of columns representing a decaying repeat rate (70 or so) and these will be increasing as time goes on so I need this formula to be dynamic (i.e. - copy it down the column of (# of repeat users)
I am using Excel for Mac 2011.
I have a table with changing length of the rows and columns. I need to calculate the sums of the rows, the columns ad the diagonal. I managed to write a working code for the columns, but have no success with the rest. Here is what I have:
View 4 Replies View RelatedDoes anyone know of a macro that can take values in a daigonal and put them in a single column?
Like for example... if you refer to the attached image, I basically would like to select all the diagonal values in red then put them all in a single column.
How do I paste text from a column into a diagonal line of cells (i.e. A1, B2, C3, etc.)? Additionally (and this may be related), how do I get the cursor to move to a diagonal cell after an entry? (as opposed to down or to the side)
View 2 Replies View RelatedI have a table with data representing $income vs %Debt burden respectively. The data on the table per respective intersection provides number of accounts and total ($)value of accounts. I have tried to combine the two pieces of information into one column and have a diagonal line separating the two numbers. (sample table attached).
View 2 Replies View RelatedI would like to enter a long line of data (text & numbers) into a single cell and have the single cell data populate successive horizontal data fields with automatically. The single cell data would have properties that would correspond to specific properties in the successive fields. The single cell data would remain unchanged. The successive horizontal cells would be looking for a specific piece of the single cell data.
View 1 Replies View RelatedCurrently we are transposing data in multiple cells from horizontal to vertical & vice versa.
But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.
Is there any VBA function or public function to perform the this action?
Example:
From
A 1Dog; Lion; Parrot; Bee; Snail
To
A 7Dog8Lion9Parrot10Bee
11Snail
Like wise i will have to do the same action for the following
A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7
I have chart like below. In empty cells I want either 1 or 0 (1 if software is installed and 0 if not).
Excel
Outlook
Powerpoint
Word
Computer1
Computer2
Computer3
Computer4
Computer5
Data of computers and their software are like this:
Computer1
Word
Computer1
Excel
Computer1
Powerpoint
Computer1
Outlook
Computer2
Outlook
Computer2
Excel
Computer3
Outlook
Computer4
Outlook
Computer4
Excel
Computer4
Word
Computer5
Outlook
So called Matrix Lookup was very close, but it finds data FROM Matrix (aka that first table). Is it possible at all?
Excel and Windows version:
Excel 2010 SP1
Windows 7
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
Here is a formula I am trying to use to access another woorksheet to compare the values in J13 in each worksheet.
=IF(J13=INDIRECT("'"&$O$4&"'!"&"J13"),"▬",(IF(INDIRECT("'"&$O$4&"'!"&"J13")=" ","▬",(IF(J13=" ","▬",(IF(J13>INDIRECT("'"&$O$4&"'!"&"J13"),"▼","▲")))))))
Where O4 contains the name of the first worksheet.
I want the formula reference [J13] to chage to J14, J15 and so on.
I'm dealing with a DDE link and have a cell that works when I type in the value =WINROS|AVEVOL!XOM . But I don't want to manually enter each value. So I have tried to use =INDIRECT("WINROS|AVEVOL!" & A2) where A2 contains the value 'XOM', but I get a #REF! error.
View 9 Replies View RelatedWarhammer Quick Reference Desktop.xlsx
Its on book 2.
I'm trying to reference the Cryptek total that I got in the previous area, and put that result into the grand total army point formula. When I reference the solution cell, or even copy and paste the very same equation into it, and then proceed to filter the referenced cells change...
The obvious solution that i thought would work is write =D45 next to Cryptek in the point value column, then if I filter it SHOULD maintain the information from D45 except it doesn't.
On the spreadsheet Im creating I am using cell referencing. The user will input data into the blank cells, and cell referencing will put them into another table. Then I add these using an IF(OR(ISBLANK formula.
However, the empty cells come through as 0's rather than a blank cell. As they're coming through as 0's, they're being calculated into the formula, and this is causing problems in my main table.
Is there a formula I can use to ensure that the empty cells being copied over do not give off any other value?
I tried to use a few different IF formulas to only let specific data go through, but it didn't work. I tried to have the blank cells produce a letter so that they wouldn't be calculted in the formula, but letters are also counted as measured values.
I have a row with month titles. I manually fill this with data as the year progresses. I need to automatically use the last month updated in the row in a formula. i.e. theformula is in one cell and calculates on the last figure in the row e,g, data is in january (say C3) - when I input Feb data in C4, I want the formula to automatically reference the last complete cell (ignore january data and calculate on Feb data), and so on through the year.
I'm trying to grab data from two sheets by first doing a match. The match works, but how can I now reference ws2 so that I can pick up data if this match is true?
View 2 Replies View Related