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.
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.
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 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.
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?
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'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.
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.
I 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.
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
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.
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 a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
I am trying to use the SUMIFS function to sum the cells in one column if the cells in a reference column ARE blank, and if the cells in a different reference column fall within a date range.
-Column F contains currency, and is the sum range -Column H contains dates as well as blanks, and I need the criteria to look for cells that ARE blank. -Column A contains dates, and I need the criteria to be <= 01 Feb 2013 and > 31 Dec 2012
This is the formula I have tried, but it keeps adding the cells in F if the cells in H ARE NOT blank. Clearly, it's an issue with "<>" and probably a very simple solution, but I just can't figure it out.
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 have a range that I named. Let's say that range is called "RangeX". Let's say RangeX is defined by B2:E10
I have a series of formulas that give me the row and column numbers of the starting and ending cell of a subset range I want to create within RangeX. Let's say the starting cell is defined by (1,1) and the ending cell is defined by (3,4) within RangeX. Translated, the starting cell would be B2 and the ending cell would be E4.
How do I create a subset range using the starting cell (1,1) and (3,4) in VBA? I would like to use the row/column reference as RangeX itself could move around.
I have a spreadsheet list that has no merged cells. I need to copy that spreadsheet into another spreadsheet where each row is actually 2 merged rows. I thought that I could manually reference the first few rows, then drag them down to copy and hope that excel would be smart enough to pick up the pattern. But it doesn't, and keeps copying over every other row. Is there a way to quickly copy a spreadsheet with individual rows into a spreadsheet with merged (two rows) cells? Seems like it should be doable.
I have cells that all reference from a 4x147 table, each row has one piece of data in, but only one piece from a choice of 4 cells (3 of which are blank).
the cells that are referencing this data, i would like to make them reference to all 4 cells, ignore the blanks and choose the one with the data.
I paste a weekly information sheet into a workbook. I pull information from cells within the sheet to form a weekly report. The sheet I paste is identical in columns but not always identical in rows; sometimes it may not have any information on one of the accounts. That account will be deleted and the sheet is 10 rows shorter. The sheet is over 1000 rows so it is too time consuming to add and delete the rows each week to match. My guess is I need to use VLOOKUP & OFFSET but I cannot get any of the formulas I tried to work. The account number appears on each 10 lines for that account. I have made up an example workbook to give an idea of what I need.
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))" End If Next maxCell
I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.
I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.
