I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.
What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?
I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View 3 Replies View RelatedI have a worksheet titled "Systems Estimate" that will dictate values on a second worksheet called "CSI Estimate". They're the same values in each cell and column, just in a different order row wise. I would like the values in each row in the A column in the "CSI Estimate" sheet to dictate columns B through W, with the values being referenced from the "Systems Estimate" sheet. I tried using an exact match with vlookup since the data isn't sorted, however it just returns the formula I enter, not a value.
View 3 Replies View RelatedI am trying to pull data from different Tables within a workbook by typing the name of the referenced Table in a cell rather than leaving it static withing a VLookup formula.
So far I have this:
=VLOOKUP($B6,OctTable,MATCH(D$4,$D$4:$V$4,0),0)
What would I do if I wanted to be able to dynamically change the "OctTable" portion with the name of another table which I type in call A6? I have researched as best possible and think that it might have something to do with INDEX, but I have failed to figure it out.
1. I have a worksheet with subtotal and total row above all columns
I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)
however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.
Something like SUBTOTAL(9, A6:LastCell in A)
2. I also use SUMPRODUCT to do calculations
e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))
again how can I make the sumproduct dynamically use all rows to the last active row.
3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?
e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))
I'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
I need to find average of the values , the count of the cells will be dynamic (may be 5 or even 200).
View 2 Replies View RelatedI am setting up a questionnaire which needs to be dynamic. The questions in the questionnaire are pulled from a 'baseline sheet' using check boxes and an if statement, i.e. each question in the baseline sheet has a tickbox next to it, and if it is checked then the question is populated into the questionnaire sheet.
Im wondering if there anyway for the worksheet to populate the input cell with the next cell value from the baseline sheet if the first is blank? So if a box is not ticked, it will intelligently seek out the next box that has been ticked and retrieve that value. Essentially, my goal is to not have any blank spaces in the questionnaire which is what is happening now.
Is anyone aware of some way to use the " dynamic named range approach" to only select the cells with numerical values in a column and name this range?
I've looked at the examples on this site but can't find any solution to this particular problem although I have a feeling that this should be possible.
how to reference a listbox value by column? For example, I have a listbox that is populated by two text boxes. The actual list looks like this:
ItemOne 1
ItemTwo 2
ItemThree 3
And so on. With Column 1 being a string and column 2 always a number.
I'd like to be able to take all of the values from column 1 and paste them into my spreadsheet next to particular adjacent cells.
Also, I'd like to take the values in column 2 and sum them.
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies View RelatedI am working with a worksheet that has some cells merged e.g. A1 and A2 are merged together as 1 cell. I am using cell offset to reference values from the worksheet assign them to variables then populate another worksheet with their respective values. I noticed that cell.offset(row,column).value is not retrieving a value when merged cells are mixed with non-merged cells within a given row. Is there another method/function I can take advantage of here?
View 1 Replies View RelatedHere's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
For example in this formula I typed in WWW as the max:
=SUMPRODUCT(($F$5:$WWW$5>=C2)*1,($F$5:$WWW$5<=C3)*1,ABS($F$6:$WWW$6))
If I try to type in a value higher than WWW it will give me an error. I was just trying to put in an amount that I would never hit within the row.
I would like a formula that checks for blank cells only between C1:C10 and E1:E10 and then insert the first and last names of the person associated with these blank cells (which are located in A1:10 and B1:B10) on another worksheet, which for the sake of argument can be called 'sheet2'.
View 2 Replies View RelatedI need assistance referencing cells in other sheets. I have a summary tab which I would have each refenced sheet listed (named for the activity being billed). In a separate cell in the summary tab, I want to bring back the value of the total amount billed for that activity. The total value is in the same cell of each sheet, as all invoice tabs needing to be referenced are identical in layout.
There is one hitch to this, the sheets referenced have not yet been created (that would be too too easy), and the activity list in the summary sheet has place holder names that will change when the activity being billed is defined (hence prompting the biller to create the sheet to be referenced).
Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3
What I am trying to do is have in the next column a formula which returns
the number adjacent to it multiplied by the number of blanks between it and
the next non-blank cell.
So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc
My current worksheet is being built for use in Eve-Online, a game I play. In specific, I am keeping track of profit made by manufacturing different items in the game. I am trying to build it like this:
Column 20 is a list of items, and the next 7 cells to the right of each item contain the mineral requirements for that item. So C20 says Bantam Frigate, C21-C28 will all contain numbers. Now, I am trying to create a function in the upper-right cells (like A1-A8) where if I type in "Bantam Frigate" into A1, A2-A8 will fetch the numbers that I put into C21-C28.
Now, I will be working with thousands of items, so I have to make sure I name both the 'database' and the query exactly right, but that's not a problem to me. Can I make something that will reference a value from (cell+1 column right) or (cell+2 columns right)
I have a row of cells on one "Sheet1." I am trying reference these cells into a column on "Sheet2." I know I can reference one cell at a time, but I have a large number of cells in the row on Sheet1 and I know that there has to be an easier way to do it, I am just not excel-savvy enough to know what it is.
I know you can copy and "paste special" (transpose), I am just wondering how to do that while referencing. I have heard that the "offset" function can be used, but after extensive trial and error, I haven't figured it out.
I have set up a spreadsheet in order to track and calculate the number of students we teach at our organization. It is running prohibitively slow when entering new data. On one sheet each individual session is entered (the school, program, date, number of hours etc.) This is the Session worksheet.
On the next sheet(the Program worksheet) the maximum number of students for each unique program (this is usually a combination of school and the program) is calculated to ensure we are not counting students we teach weekly as new students. This is calculated using a formula:
:{=MAX(IF(Session!G:G=Program!A2,Session!D:D))}.
"Session!G:G" = the unique program name on the Session worksheet.
"Program!A2" = the unique program name on the Program worksheet.
"Session!D:D" = the number of students in that individual session...........
Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?
If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.
I currently have 3 sheets 1, 2,and 3.
Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column
I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.
I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.
Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...
Applying my indirect formula to the rest of the cells in my sheet properly.
I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ....
The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.
So I have quite a few rows to get through I want to be able to autofill so it looks like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
2
Sheet 2
=(INDIRECT("'"&A2&"'!$A$5"))
[Code] ......
As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ......
Excel isn't intuitive enough to know I want to move cells in the referenced sheet.
I have a workbook that has a number of sheets with various project stages and dates on them. These details are arranged in small tables displayed vertically down the page. Each worksheet's name is the project code - e.g. 30033_99
I have now been asked to create an overview page that displays all these stages and dates on one page. What I'm trying to do is get Excel to look at the column with the project code and then pull information from the worksheet with that name.
I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.
I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.
I've tried to a create code from examples in previous posts but I can't seem to figure out how to get what it is that I'm looking for.
I'm trying to reference the value in cell O9 which would be a number.
Preceding this value, I would like to have the word "Rev" displayed. Just underneath those values, I would like to reference the value in O7 which is a date (3/27/2014 for example). I would like the footer to be updated anytime that cells O9 or O7 are updated.
These values would be placed in the right side of the footer of the active worksheet, and be displayed on each page that is printed out.
It's not much, but here's what I have so far:
[Code] ....
I'm using two comboboxes to simplify data query of a database: the first narrows the query by displaying employee names, the second narrowing the selection further by displaying the equipment that employee owns. I am able to populate both comboboxes fine.
The first combo box for employee names:
Code:
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("User&EquipOverview")
[Code]....
Where I am running into trouble is that I want the selection made with the equipment name combobox (ComboDevice) to populate several textboxes with values related to that specific selection in the database. That is, the combobox population needs to have the cell location included as information to pull the correct information.
Each employee has several pieces of similarly named equipment, so it's not so easy as to search the initial list for the value of the combobox selection.