Is there a way to create a data table where the input cell is not on the same sheet as the table? I've tried using a named range, and can't make that work.
I usually work around the problem by putting my data tables on my assumptions sheet, or creating a new input cell on the data table sheet, and linking the original input cell to it,
In sheet 3 they are combined via reference in order for me to do calculation in the input - They are combined in sheet3 as the columns are not identical.
Because the data-tables will be updated via sharepoint, i have to link to cells beneath the data-tables as i expect these will be fill out when the tables update.
My problem is that when the tables update, i dont see the updates in my "combination" sheet. it appears the the data tables add a new line, which moves my cell reference 1 line down.
Ex: Had a reference in Sheet1'E3 - after the data table updates the reference is now Sheet1'E4.
I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/) (http://url/[""2014/04/01""]/starthour1/endhour2/) (http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
I have a situation where I need a macro to reference another cell in the sheet if the cell is left blank. If the user wants to edit the value, they have the option to manually input a value but if they decide to leave it blank, it autopopulates the referenced cell.
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
I am tracking business hours of various business locations. I would like to be able to enter the businesses operating hours on the "overview sheet" and, based on those hours, have a table generated on another sheet that "autofills" based on the date entered on the "overview sheet". For example:
I would enter the operating hours of the business on the "overview sheet" and it would look something like this:
Day Open Close
[Code]....
I want a table for each day created. I would then manually enter the data for the "Ranking" column. The issue is that I have numerous location I want to do this for and all with varying hours of operation. It is very tedious to manually create the tables.
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
I would like to make an excel worksheet that can do the following:
At the beginning of the worksheet I have a reference block w/ 2 rows, 4 columns of number of 1-8 (randomly, input manually). Then, I would have the same block (i will call these member blocks)(2r, 4c) repeatedly vertically (maybe 10~12 times etc...) and these block will have 2 rows in between to seperate each other.
Then, right on top of each of the member block (the row above the member block) there will be input space where i will manually input numbers (4 at most ~ also 1-8). Each of the number i manually put in will place into member block with its location reference to the reference block at the beginning. (and each number will be highlighted in one color, so 4 colors are needed.) Finally there will be several of these on a page. attached is a sample of what i kinda want but they are all input manually.
I know how to turn a column reference in a structured data table into an absolute reference:
=Table1[A] becomes =Table1[[A]:[A]]
However, how to make a cell reference, like this one, absolute to that it still locks on this row, column A when I drag it across. I don't want to use copy & paste, as I have other cell references that i need to leave dynamic.
=Table1[[#This Row],[A]]
I've seen that @ can lock rows, but I don't seem to be able to lock an individual cell.
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example: I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE) I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE) This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
I have attached my workbook. What I would like to do is to have an input box to where you input the Material, Gauge, and Size and then have it return the rest of the values on Sheet 1. The material is listed as the worksheet name and the other information is inside that sheet. For ex... I would enter in Stainless, 7, 48x120 and it would give me the rest on sheet 1. I am totally lost, I have tried several different things but am coming up with nothing.
I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
I want to run a macro that unhides 15 lines (line 20 to 35) when data is inputed on cell 'F6' and to run another macro called "search" (i have already finished this macro) which will fill in information on the unhidden lines from above.
I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
I am having trouble making any formula references between 2 different workbooks.
For example, I being typing out my VLOOKUP function on 1 sheet, as soon as I get the table argument, I switch over to the other workbook in which the table is, and I no longer have the VLOOPKUP function active.
I am having this problem for any formula in which I am trying to reference this workbook.
I have ensured they are both saved as Excel workbook documents. I am using Excel 2007
Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
I work for a marketing agency where technical savvy-ness is really limited. We analyze a lot of web page elements. I have a program that exports this data into a csv file and I've created a template for the user to add this sheet. This data is primarily text. I've created a button on another sheet that runs a macro that extracts data from the first sheet onto the second sheet in a particular format for ease of reading and editing it. That part is all good, this macro works and the button works. Now I'm trying to have it such when a person edits one of these cells, it highlights the changes on this newly sheet using VBA (the highlighting changes function just turned out to be too messy and buggy for what I want). I have this basic change macro that I have on the sheet currently:
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:H500"
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Interior.ColorIndex = 30 End With End If
ws_exit: Application.EnableEvents = True End Sub
This works as well. However, my problem is that when I run the data button macro, of course excel reads this as a change to the cell and highlights it as a change before any actual changes are made. How can I have it so I run my data macro, and the cell changes are highlighted only *after* the user goes in to edit the text data plugged in from the data macro?
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1) Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
