I have created a table which is an index of all the worksheets. Now i would like to create a formula using the index reference. I get the right result if i concatenate but the text doesn't turn in to an excel formula.
=CONCATENATE("=";A1;"!";"D4") A1 contains the worksheet name, if the worsheetname is "A" then i would like it to return a formula that is =Sheet1!D4
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
I would like to be able to write a formula which references text within a cell and the formula automatically changes when the text changes. Example is attached.
Current formula is
=IF(E2="A",F2-A2,IF(E2="B",F2-B2,IF(E2="C",F2-C2,F2-D2)))
Is it possible to write the formula so if I change "A" in A1 to "Z" the formula will change as well? Or the formula will at least always reference the text within the cell, not necessarily the text that exists at the formula's conception?
Example.xlsx
I need a formula that can provide all possible alphabet combination in another cell .
example
COLUMN A A1 = RED
COLUMN B B1:B50 = BELOW COMBINATION / RESULT
RED
ERD
DRE
EDR
DER
RDE
I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
View 2 Replies View RelatedI 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
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
I am wanting to convert a cell reference text
"=$A$1"
to an actual cell reference
=$A$1
Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.
I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.
I'm NOT wanting an external cell to convert it for me
i.e. INDIRECT(CELL)
because I am wanting to copy the answer to another independent spreadsheet
I'm NOT wanting to paste values
i.e. return the answer from cell $A$1
because I want the cell reference to remain within the cell.
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
how to use cells to create refrences in a formula so I don't have to hand write each refrence in a cell. I wanted to do this by creating a row with accending numbers (1,2,3,4,etc.) and a column with decending characters (a,b,c,d,etc...). Can this be done?
So for example I have a cell formula that reads the following
'Raw Data'!A2
Can I modify this formula so that the formula will automaticly refrence a different letter that corresponds with the cells location? So I would use this cell to change the (A) automatically depending on where the cell is in the spreadsheet. Likewise can I do that with the (2) the same way?
Does this make sence, or should I just continue to hard write each cell?
The reason why I need to do this is because the raw data is in a single column and the formulas that I need to use this data for needs me to convert the data into rows instead of columns.
The spread sheet I work with will not post info on the second sheet automatically. So I'm in the process of trying to figure that out. The second page is basically giving a percentage of parts the person did compared to what another company would pay that person per hour to do at 7.42 an hour. I need the second page to take all the info on the first page and figure out the %. What it does to get it is, adds the numbers and divides by the total to get average and then compares it to 7.42 and hour.
Also, if i could get the Name boxes to be auto filled with the file name? that possible? IE, John smith is the name of the file and it would auto fill that into all the name boxes on the sheets. And could i get the date to auto fill it self from the first date box? I included two files, one is a blank sheet and the other is a sample of the data entered and what i want it to look like.
I am trying to create a cross-reference database in excel. I am not sure if I am using the correct terminology, so here is what I need to do:
I have three columns that contain text cells, each with different numbers of rows. Now imagine that cell A1 is related to cell B3 and also to cell C5. There are numerous different combinations similar to this. Is there a way to show this relationship between cells in excel. If so, how?
Is there anyway to create a circular reference between three cells, and still enter text freely into any of the cells?
I've speculated that it might be possible by utilizing three or more other cells slaved to the first three, and simply kept off to the side.
To give a better understanding of what I'm trying to do, I've created a finance spreadsheet for our shop's snack bar, and below is a screenshot of a portion of said spreadsheet.
As you can see, all of the formulas are set up to where Columns F, G, and H are directly affected by what I put in Column E, but I don't want to be limited to just Column E; I want the other users and I to have more freedom than that.
Once again, my goal is to set up a 3-cell circular reference in which data can be typed into any of them. I've considered setting up a macro, but those are only active temporarily, and I would prefer the calculations to be made in real-time.
create parts cross reference table?
View 1 Replies View RelatedI'm a definite newbie when it comes to macros but can figure out formulas ok.
So I wrote a formula to break out a sort string and it got pretty complicated so I’m trying to create a macro so I don't have to write out the formula all the time. However the sort tag can be in different columns when I need this formula so I tried the "run macro" function to create a relative reference.
So it changed my formula from this:
PHP
=IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)),"",IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2)+1)&")",EM2)),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,100),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,FIND("("&(COLUMN()-COLUMN(EM2))+1&")",EM2)-FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)-3)))
PHP
"=IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])),"""",IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1])+1)&"")"",RC[-1])),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,100),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,FIND(""(""&(COLUMN()-COLUMN(RC[-1]))+1&"")"",RC[-1])-FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])-3)))"
Now my problem is in the first formula I need "$EM2" not "EM2".
I tried the obvious to me fix of R$C[-1] which did not work... And when trying to re-run the macro it puts RC143 which will not work because the sort string is not always in column EM.
I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.
View 11 Replies View RelatedI have a workbook containing a lot of worksheets, each sheet contains approx 60,000 rows of data (each sheet varies in the number of rows slightly but the data is always contained in column A). There are only 4 unique values and my task is to filter out the 4 seperate values and copy & append them to a master list in 4 columns side-by-side i.e. valueA in A1, valueB in B2 etc.
The problem I have run into is that when I have filtered out one of the values and try to copy it I get an error message - "Excel cannot create or use the data range reference because it is too complex".
This has left me thinking that the only way to process the data is to manually copy approx 20,000 rows at a time (I'm not sure what the maximum amount of filtered data Excel can handle is but this it can definitely handle) and paste to the master list.
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!
Every new file I create is named with a sequential reference based on some criteria:
PackageName_(Place+SequentialNumber).
So, in my worksheet I have several columns with package names: LABK, LAVO, LACR… and on the rows I have the file names.
I have this UserForm I created Search.JPG
Basically (despite I imagine it’s not that simple), when I type the package name and the place as follows, for example
Package = LABK
Place = SAO
After clicking on “Generate” button, it should look for all file names containing LABK and SAO, verify what the last sequential number is (highest/MAX) and generate a new one in the text box below the button.So, if the last file name is LABK_(SAO005), it’ll generate a new one as LABK_(SAO006).
I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.
I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (VBA - How to refer to a different worksheet cell) but I am not having any luck.
Sub Budget_Rollover()
Dim i As Long
' Loop through all the worksheets, starting with the second sheet
For i = 2 To Worksheets.Count
Dim wsReference As String
Dim wsName As String
' Define the name of the previous expense cycle worksheet
[code]......
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
how can I create a text file in a folder called test and then input texts in this created textfile
so say I had 2 input values
1 said hello other was james
i need to create textile in this folder and then have this written in the textile and saved (printed like the below)
hello
james
thank you
editing and inputting some text in an existing textfile and inputting the Inputbox values at the end of the text file like the above
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?
anyone know how to refer to text in a string? vba
View 9 Replies View RelatedI have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
I've seen a lot of other posts on here regarding referencing a COUNTIF statement to a cell instead of a value (like this: =COUNTIF(B7:E7,"<=" & B10), but need to do it for text instead of numbers. This is basically what I've got:
A
B
C
Missy 342
Missy
4
[Code].....
Column B is created using a search function, and returning only the Missy or Mike, (using a search function, which I know how to do), and then I want the counts to populate in column C based on how many times either Missy or Mike pops up in the range.
Problem is, when I try and write a formula like =COUNTIF($D$2:$D$9,VALUE(E2)) it doesnt work, but if I put in =COUNTIF($D$2:$D$9,"*Missy*") it works (of course).
Is there any way to get it to reference the text in the cell since the text in the search criteria will constantly change?
I'm trying to create a VB Script for an AS/400 app. It's all written but I'm trying to feed the data for part from an excel file. If B5 was 10 I need the final output in my cell to read:
autECLSession.autECLPS.SendKeys "10"
Here is my current formula ="autECLSession.autECLPS.SendKeys"""""&B5*100&""""""
It's reading as autECLSession.autECLPS.SendKeys""10"" which has two sets of quotes not just one and no space between sendkeys and "10"
I need to extract text from a cell between 2 predetermined characters. Below find all possible cases and what result (after extraction) should look like:
data cell Extracted
o9.5-105 9.5
o9-105 9
o10.5-105 10.5
o10-105 10
o9.5+105 9.5
o9+105 9
o10.5+105 10.5
o10+105 10
u9.5-105 9.5
u9-105 9
u10.5-105 10.5
u10-105 10
u9.5+105 9.5
u9+105 9
u10.5+105 10.5
u10+105 10
Basically I need to extract numbers between (to the left) o or u and to the right + or -. The string ends always with 3 numbers if it. I have also attached excel file of these two rows for your convenience.