# Using Cell Reference For Data Table Input

Jan 27, 2014

I have a data table that looks like this:

RATE
Monthly
289.68
1.00%
262.81
2.00%
276.04

[Code]....

all the RATE numbers are manually entered.

now, if i reference the 3.00% number under the RATE column to the INPUT cell that I entered when I setup the data table, this happens:

RATE
Monthly
289.68
1.00%
262.81

[Code]....

this is obviously not correct, it is the value for the entry above.

and...

if i make ALL of the RATE cells then relative to the 3.00% one... (in other words 2.00% is actually =A5-0.01 and 4% is actually =A5+0.01)

i get...

RATE
Monthly
289.68
1.00%
262.81
2.00%
250.00

[Code]...

this is weird b/c the number for the first one and last one never change, but all the ones in-between change.

incidentally, the formula which is being solved is simply a 30k loan pmt for 10yrs ( =PMT(RATE/12,120,30000)*-1 )

## Data Table - Input Cell On Different Sheet

Mar 12, 2008

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,

## Cell Reference Moves When Data Table Updates

Jul 7, 2013

I have a data tabel in Sheet 1 & Sheet 2

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.

## Absolute Reference (cell Reference Behind The Table)

Mar 11, 2009

I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

## Parameter To Reference Certain Cell To Input Date Into URL?

Apr 3, 2014

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.

## Macro To Reference Cell But Still Allow Manual Input Override?

Aug 31, 2012

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.

## Clear Data Depending On User Input And Move Cells Forward Without Deleting Data Table

Oct 25, 2012

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.

1
2
3
4
5

£11
£133
£29
£193
£100

[Code] ........

## Generate Table On Another Sheet Depending On Input Data?

Apr 15, 2014

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.

## Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?

Mar 19, 2013

On a worksheet, I created:

- 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.

## Copy Input Data To Output Table Based On Variables

Mar 20, 2008

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)

## Table/reference/data Pointing

Apr 4, 2009

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.

## Absolute Cell Reference In A Table?

Jan 30, 2014

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.

## Vlookup, Using A Cell That Contains The Table Reference

Dec 12, 2008

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.

## Reference A Cell For Tab Name In VLOOKUP Table Array

May 27, 2009

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!\$A:\$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

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.

## Reference A Cell Inside Pivot Table?

Nov 23, 2012

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.

## Input Box With Reference To Sheet Name

Feb 19, 2009

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.

## Reference To A Range Of Cells As Input For A Macro

May 13, 2009

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?

## Reference Table (not Pivot Table) From Another Excel File

Aug 9, 2012

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.

## Run Macro After Cell Data Input

Jan 24, 2012

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.

## Input Data Then Clear Cell But Save To Another Cell?

Jan 29, 2014

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..

## How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

## Excel 2007 :: Not Being Allowed To Input Formula With Reference To Another Workbook

Feb 13, 2012

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

## Input Table -> Output Table

Sep 1, 2009

It would take forever to describe the problem so I just made a sample Excel file.

## Data Input Cell Progress Indicator?

Jul 24, 2014

I want to put progress indicator for data input in Excel.

The data input is in cell A1 until A10, and B1 until B10.

So, I need two progress indicator (for cell A & B).

## Using A Cell On Another Worksheet As Input For Data Validation

Oct 13, 2008

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.

## Highlighting Changes In Cell After Data Input Macro Run

Jun 27, 2013

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?

## Calculate Data Input And Assign This To Cell

Aug 6, 2008

Is is possible to create a calc for simple data input and assign this to cell U31 so that everytime user selects this cell then the calc pops up.

I didnt want to use an add-in in case other users don't have this add in on their computer.

## Input In Multiple Cells To Generate Input Into Single Cell?

Feb 22, 2014

I have a spreadsheet with 5 columns. A1 to A5

Entries are made into columns A2 to A5 but only ever one entry across all cells

If an entry is made into A2 to A5 the I need a "P" to be placed into A1

## Input Box To Create And Input Info Into A Formula Which Then Populates A Cell With A Value

Jul 24, 2014

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.

## Input In One Cell To Enter Data To Another Cell

Feb 21, 2014

If any text or number is added to a specific cell then a letter "P" is entered into another predefined cell.

Example:

Cell A1 is normally empty but if a number or text is added to cell B1 then a letter "P" is automatically placed into cell A1.