Use The Contents Of A Cell In A Formula In A Different Cell?
Apr 3, 2009
I've got a master workbook which reads from a number of other workbooks. The names and file address of the workbooks it reads from are listed in column A - e.g. A1 contains "C:DocsInfoFile1[sheet1.xls]". I've got a forumla that reads the required information from the workbooks and it looks like this:
=('C:DocsInfoFile1[sheet1.xls]Summary'!$B$3)+('C:DocsInfoFile1[sheet2.xls]Summary'!$B$3)
What I need to know is how I can replace the address in this formula with the value in column A. There will be many different sheets to read from and they'll change occasionally so I thought it would be easier to have one cell containing the name of the latest relievent sheets the formulas will link to rather than having to update each formula every time.
View 3 Replies
ADVERTISEMENT
Apr 5, 2009
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
View 2 Replies
View Related
Jan 29, 2006
Using Excell 2000 ...
I have data being retrieved from another sheet
Cell A1 of the Input Sheet, for example might contain the words "This is
a TEST"
Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1
.... thus displaying whatever the contents is of cell A1 of the Input Sheet.
I want to save the formula results in time ...
I want to convert the cell(s) of the Output Sheet to the results ...
getting rid of the equation(s).
I'm sure I have seen this before, I just cannot remember? ... getting
old!
View 9 Replies
View Related
May 18, 2006
I seem to be unable to locate a worksheet
function that allows me to determine, if a given cell contents is a formula
or a value.
View 11 Replies
View Related
Jul 13, 2009
I want to split the contents in a cell using a formula.
For Example, I have ***PavanKumar*** in A1
I want ***Pavan*** in A1 and ***Kumar*** in B1
View 9 Replies
View Related
Aug 27, 2009
Using XL07, some of my data looks like this
TY+/*-
PX/*-
TFM/*+
In an adjacent column, I am seeking to display the letters, numbers or symbols that appear before '/'. I would normally solve this little problem with something like =left(A5,2) or some combination of left and len. This method fails when the formula finds a len of 8 when the previous highest len was 7.
View 9 Replies
View Related
Feb 28, 2007
I need a formula that would scan a given column for data, get the first available data from the first available cell ( D1 ), perform computation, input result in a different cell ( A1 ) and then delete the contents of D1 and move on the D2 and so on till end of data. So far I have been able to have the result in A1 but could not achieve the deletion of D1 contents. The purpose is to prevent the formula from scanning the data column from D1 again. In this way, the datawould be made available in one column and when the data have been processed the column would be empty and the result would be in another column.
View 2 Replies
View Related
Jan 23, 2008
I need to create a formula which will change the contents of the cell to one of 3 options based on the value of another cell
For example if I was using B2 as the cell to reference I need to say IF B2 = 0 then say "Closed", IF B2 = 1 then say "Open" and if its any other value then say "Error".
I'm having some trouble having all 3 if statements nested correctly.
I don't want to do this as a macro just as a formula.
View 9 Replies
View Related
Apr 9, 2008
I want to set up a sheet macro that will replace the contents of a cell (when a specific number is typed in) with a specific formula.
For instance, when "43" is typed into any cell within specified columns (say columns C and H), it changes to the following formula: =IF(D47="",43,IF(D47<=3030,408,43))(WHERE D47 WOULD REPRESENT THE CELL ADJACENT TO THE CELL WHERE "43" WAS TYPED.)
IE:
If "43" was typed into cell C1, then it would change to the formula: =IF(D1="",43,IF(D1<=3030,408,43))
If "43" was typed into cell C50, then it would change to the formula: =IF(D50="",43,IF(D50<=3030,408,43))
View 12 Replies
View Related
May 20, 2008
Here is what I am trying to achieve:
I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'.
One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names)
What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list.
I have achieved this initially using the following formula:
=IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10)))
This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...)
But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following:
=INDEX([CONTENTS OF A1],RandInt(1,10))
So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!
View 9 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Aug 21, 2009
I've got the following sumproduct formula (which seems to work):
=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?
View 9 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx
View 10 Replies
View Related
Apr 29, 2014
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Apr 18, 2013
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
View 4 Replies
View Related
Feb 12, 2014
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?
View 4 Replies
View Related
Jun 1, 2014
i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
View 3 Replies
View Related
Apr 30, 2009
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View 5 Replies
View Related
Apr 28, 2014
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.
View 5 Replies
View Related
Sep 12, 2013
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies
View Related
Oct 20, 2013
I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
View 2 Replies
View Related
Mar 13, 2012
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
View 2 Replies
View Related
May 2, 2012
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
View 1 Replies
View Related
Mar 9, 2013
I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
View 9 Replies
View Related
Oct 8, 2013
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
View 7 Replies
View Related
Mar 7, 2007
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected
Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell
I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.
Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................
View 4 Replies
View Related
Sep 7, 2012
I am working on a database and trying to remove duplicates. When there are duplicates, I want to take the contents of one cell and copy them as a comment on the cell above (or below).
I tried to work on a small macro to do so, but the contents of the cell cannot be copied (it comes out as a blank).
The long way is to 'double click' in the cell, Ctrl+A, cut, click the cell above, Ctrl+F2, paste. This way is very long as I have thousands of duplicates.
View 4 Replies
View Related
Sep 23, 2013
I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.
View 4 Replies
View Related
