I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
I need to create a reference such as TUT001 from Tutorial in a separate cell.
Also where there are 2 names that have the same 3 letters at the start i need the number to be 002.
Cell A is the first name, B is a period, C is the last name, D is an "@" and E is "yahoo.com".
I want to squeeze them all together, in order, without spaces, into cell F to make an email address.
What is the formula ?
I have a User Form in which users can enter R,G & B values in 3 seperate Text Boxes in order to change the colour scheme of a worksheet to suit their personal taste.
I have tried combining the 3 values into a string to give, for example, RGB(255, 182, 45)
But, of course, because it is a string variable it is enclosed in quotaion marks and so, when add to a .Interior.Color function, the qutation marks are added too and the macro fails.
I have tried altering with Left, Right, Mid and Trim but cannot get rid of the quotation marks. I also tried converting to Hex but got totally confused there!
code to convert the values in the 3 list boxes (called LBRed, LBGrn and LBBlu) into an RGB value that can be appended to .Interior.Color
I have 2 excel files A & B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!$10:$10,1,2)" is the formula used (referring row 10 in file A).
If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
I am trying to use following string to find result into column F and unable to find reason for Error.
=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))
A
B
C
D
E
F
1
part
code
price
find part
find code
Result
2
x
11
5
y
12
#VALUE!
[Code] ........
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
I'm trying to switch my vlookups to something more dynamic. I keep adding columns to the data sheet (sheet1 for these purposes) and so I have to go back and update all of the other sheets that use the vlookup. (I'll just give an example as sheet2 for these purposes)
So what I have currently:
=VLOOKUP(A5,Data_All,4,FALSE)
Basically the 'A' column in sheet2 has an integer value, and so the vlookup takes that integer and looks up on sheet1 which row corresponds to that integer and then returns that string value.
What I want:
='AO_Cat_' & 'A5'
Basically I have named every cell in sheet1 and the particular value that I want to pull is named "AO_Cat_%name" with the last part being the integer value. I don't know if that make sense or not. For example, on sheet2, A5 might be '16' and so the value that I want to pull is "AO_Cat_16"
If I were to do this from the VBA side of things, then it'd be a simple range("AO_Cat_" & id_number) where id_number was the lookup value. However, I'd prefer to have this directly as a worksheet formula for simplicity purposes.
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.
View 14 Replies View RelatedI have a problem using indirect in a counta function. Basically, I want to count the number of used cells in a range. However, there would be insertions to the rows (using the insert row) on top of the range.
my function is now =counta(indirect(" events database"!"A"&M22&":A"&M500)
However, this formula returns an error. What have I typed wrongly?
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedMy company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies View RelatedI am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies View RelatedHow can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.
The format is: Sheet 2
A/B/
334702/UEMR88QX
334703/UEMR85QX
334704/UEMR81QX
334707/UEMR8JQX
The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet?
Master
A/B/C/D/E/
35/ 465/881676311350/311350/UEMR8ZTU
36/469/881676310722/310722/UEMR8V5V
37/483/881676310924/310924/UEMR8XYU
38/805/881676331096/331096/UEMR8J13
My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.
I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies View RelatedI have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.
I have tried
=011-A1-11
="011-"A1"-11"
="011-A1-11"
I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).
CarPhoneHouseBoatAndrewXAndrewXAndrewXAndrewX
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
I think I need to combine functions to allow me to automate data entry from a table from another worksheet. If the Name entered in column A on Exp Breakdown matches a name in column A of Salary Breakdown I cannot seem to write a function to scan the table in Salary breakdown and lookup the percentage that person is paid from a specific program number. I have attached a sample worksheet to hopefully make it easier to understand.
View 3 Replies View RelatedI have created and named a table with salaries based on years of experience and education.
I am having trouble writing a function to search for a specific amount and result in the the cell below that specific amount.
So people are moving up by one year on the schedule I would like to use their current salary to lookup and display their salary for next year.
I am confused on how to incorporate the different functions I am assuming an index and a hlookup...
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1
123-45678-A-10
123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
Columns("B:B").Select
Selection.NumberFormat = "00"
To produce:
Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02
Now all I need to do is rejoin these 2 columns before sorting. My current code is:
Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value
[Code]...
As many have probably already guessed, this produces the following result:
Col A
123-45678-1
123-45678-10
123-45678-2
I'm looking for:
Col A
123-45678-01
123-45678-10
123-45678-02
**zeroes in BOLD for reference only, result does not need to be bold**
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
