In cell H1, I want to multiply the value in cell B[x] by $G$1 where x is given in cell A1. For example, if A1 is 4, I want to to multiply B4 by $G$1 and have the result in H1.
View 2 RepliesRight now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.
Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.
ww = Work Week.
In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.
I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:
Import Data
A B
Alex 1
Ben 2
April 2
Harry 3
Data Sets
Alex Data Range
1 2 3
Refine Help Token
Cover Safe Coin
Simple Aware Change
Ben Data Range
1 2 3
Sorry Away Candy
Happy Home Soda
Mad Neutral Water
My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.
So 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 a table where the first column contains the date and the second column contains number of calls answered on that day. I have all the dates from Jan 1, 2007 - Dec 31, 2007 listed in the first column. I would like to be able to show the last date where data was entered at the top on my page where I have my Year-to-date running total. I would like the date to be automatically updated to the last datewhere data is entered.
View 2 Replies View RelatedI'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies View RelatedHow come doing this doesnt work?
=GETPIVOTDATA("Payment",PivotTables!$B$25,"Year",Formulas!J4)
I want to have the item at the end link to a cell reference so it can change.
how to make that work?
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?
Warhammer 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.
I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 Replies View RelatedI have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies View RelatedI am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
I want to do (seemingly easy task) of getting a price from a code, from a code sheet.
EXAMPLE:
Sheet1 (Log sheet)
A1 = "Code456"
A2 = $? <- This needs to be found.
Sheet2 (Price Sheet)
A1=Code123 B1=$1
A2=Code456 B1=$2
A3=Code789 B1=$0.50
Etc, and so on...
What I want to do is basically (without using macros):
"If A1 = sheet2:A1, then
A2 = sheet2:B1, halt. , ELSE,
If A1 = sheet2:A2, then
A2 = sheet2:B2, halt. ELSE,
So on and so forth..."
I thought there might be an easy way to do it with some sort of 'double sided' data validation drop downs or OFFSET function but I cant figure it out.
i have a column of data part number part text e.g 7w1,8b1 in the next column i want to write a function that will look at the other and dependant on whats in it place a word e.g. for 8b1 i need it to look at the "b" and enter beauvale if theres no "b" but a "w" enter wollaton, i have tried an if function and used "*b*" but it doesnt seem to like wildcards ....
View 9 Replies View RelatedHow do I write this formulae ??
For cell range M6:AV6 fill with N/R If the data in cell D1 = Wont have
In the attached spreadsheet I need a formula to extract the value from column C that matches the date in Column E where the date in column E is equal to or falls between the dates in Columns A and B.file now attached.
View 4 Replies View RelatedCan I conditions format a cell based on data from another cell?
View 8 Replies View RelatedIf A1 is blank, able to edit and type in B1.
If A1 has text in it, B1 = C1
In excel, how do I set a reference cell and then have in the next cell, the reference cell + 12 rows below the reference cell? And, then in the following cell, 12 rows below the last cell, and so on. I have a guess that you would use the offset function, but I would like to be able to set the reference cell once and then have each cell following the reference cell to pull data from the sheet that the reference cell is on in increments of 12 rows below the reference cell. What is the excel code to do this?
View 9 Replies View RelatedI have a data set (A1:B4)
I have decided which cell I am interested in and have written it in cell (B8)... in my actual spreadsheet, this interest was calculated by a formula
Now I want cell B10 to display the information that I am interested in (ie: $100)... however, I want a formula to do this as my "cell of interests" will keep changing based on its predefine formula.
******** ******************** ************************************************************************>Microsoft Excel - Book8___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB10=
ABCD1DescriptionCost**2AAA100**3BBB200**4CCC300**5****6****7****8Cell*of*Interests:$B$2**9****10Formula*to*display*Cell*of*interests:***Database*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have values in column C1:C10 that are 8,9 or 10 (representing the months of August, Sept, and Oct). I need to copy a value that occurs in column I if the value in the same row in column C is 8.
E.g. if the value in C1 is 8, then copy the data in I1 to a separate column, and so on for each row in column C.
i am in need of the following.
A B C
9.30am 120 120
9.31am 119.9
9.32am 119.8
9.33am 121
9.34am 120.9
9.35am 120.8 120.8
Here A and B value is keep on changing from external source, when A1=9.30am, the B1 value 120, then C1 should capture the B1 value and stored in it. like wise it captured and stored for every 5 minit or as per requirement.
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 RelatedI am looking for a way to do what i would call a dual vlookup.
i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same.
what i am trying to do is something like
vlookup("a",sheet2!A:M,***need to put the column of the data here***,0)
i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
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.