I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location):
D2 is 17 (the row)
B4 is 2 (the column)
therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies View RelatedI have got this working so far:
=LOOKUP(D9,'Financial Snapshot'!$H$5:$I$95)
D9 = 1.01
D10 = 1.02 etc
[Code]....
i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?
View 2 Replies View RelatedI have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
View 13 Replies View RelatedI have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
I have a data entry spreadsheet that contains 120 data entry panels each of 10 rows. Each panel is separated by 3 rows (1 of which is hidden). When entering data to the panel the user is required to enter a date in column F of the data entry panel. The number of used rows in each data entry panel can vary from 1 to 10.
The entry of a date in the first Column F cell in each entry panel stamps today's date in Col F in the first row below each data panel (the hidden row) and that date is then linked to a separate summary sheet to show the date of data entry. The code I'm using for the date stamping routine is below, and although it works it is painfully slow and I'm sure very inefficient. I've had to break the code into the three sections as shown below because there seems to be a limit to the number of individual cells I can reference in each of the range statements. (Is there a limit or is the length of the range statement causing some other problem?)
I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)
I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!
At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.
When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.
I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.
I am not sure how this is possible without doing copy pate transpose or individually refereing each cell to source. I would like the heading to be presented as in sheet1 which is sourced from Source sheet but if anychanges are made to source it updates the Sheet1. I have attached an example to illustrate. (Headers are not fixed number)
View 3 Replies View RelatedI am trying to get information from 1 sheet to another. What i need to do is take the information from sheet 2 column H2:H26 and put it in sheet 1 cell I4. i already have the vlookup information in the other cells that i need but for what i need this for is different than a vlookup. basically i want to be able to type in a name (from sheet 2) in cell I4 (sheet1) and all the information that i need auto populates for me like i already have. I was able to do this with a drop down menu but that wont work as the information will change weekly,
View 11 Replies View RelatedI have 2 Sheets in Excel: Sheet 1 & Sheet 2
Sheet 1 has some basic data (numbers) in a column, say AB1, AB2, AB4, AB15 (specific cells)
Sheet 2 basically references the data in a nicer template, with the basic forumala: ="Sheet 1"!AB1, etc..
Now...Sheet 1 has a new column inserted and new data added weekly, so AC1, AC2, AC4. AC15
How do I ensure that Sheet 2 recognises the new Column Insert (incremental letter) and references it automatically?
I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.
My problem is how to refer to the cell address and still use this conditional reference...
If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),
I have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:
Instead of =CONCATENATE(A4, " ", B4)
have
=CONCATENATE(FirstName4, " ", LastName4)
I'd like to reference Column A to a single cell in a different sheet.
=StoresServiced!A2
Returns the value of A2 in the sheet called StoresServiced to another sheet.
What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort
I am using this formula
=IF($A5="Select","",MAX(INDIRECT(SUBSTITUTE($A5,"/","")&"m1440"&"!d:d")))
It looks for the sheet based on A5
It finds the max number from the sheet in Column D
This is what i would like to do please....
On this found sheet - Column A has dates in this format yyyy.mm.dd
I would like to enter a date in a cell (say for example AA5 on main sheet)
Can the formula above be adapted to:
Look for sheet based on A5
then
Look at date entered in AA5
then
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
then
Return value from Column D
In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
View 7 Replies View Relatedhow to reference cell A1 in a macro?
View 9 Replies View RelatedHow can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example numbers 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
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?
I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum.
View 14 Replies View RelatedI have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.
So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.
Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.
I have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?
View 2 Replies View RelatedTrying to use INDIRECT to sum the contents of a column on another worksheet upto a certain cell reference which is in another cell on the worksheet.
=SUM(INDIRECT("Sheet1!A4:Sheet1!"&B1))
I have taken it back to simply having sheet1 with numbers in A4 to A23, then sheet2 with A23 in cell B1, and the formula above it C9, but I keep getting #REF!.
The formula works fine if on sheet1 without the worksheet names in it. Formula evaluation gets to =SUM(INDIRECT("Sheet1!A4:Sheet1!A23")) then gives =SUM(#REF!)
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
I managed to find this great bit of code some time ago and would now like to add to it. The code basically identifies any duplicates in column A and then lists the results in a message box,e.g.:
Duplicate Name: REF12345
Rows: 2,3
Works great for 1 or 2 duplicates but if there are many it becomes quite difficult to keep track. I'd like keep the code as is but add highlighting to the identified cells.
i'm looking for a loop macro to perform the following task:
Sheet 2 is my look up table, and sheet 1 as a number of references such as:
='Sheet 2'!B1
='Sheet 2'!E1
='Sheet 2'!G1 etc
I need a macro that increases the row references "1" to "2", saves the sheet then increases "2" to "3" and so on until the bottom of the reference table is reached
I am building an Excel sheet for my company which keeps track of who has done what with a new client in a step by step process. I have it set up so that once a department clicks complete in a box it automatically emails (VIA a macro) the next person who needs to do the next task. Some tasks are done by the same person no matter what, other tasks are done by a project leader which is different depending on the client.
The problem I'm having is that, within the macro, I need to reference an email address in a cell X cells to the left in the ".TO" line.
So, in Column A I have the CLIENT, Column B The PROJECT LEADER, and Column C The PROJECT LEADERS EMAIL. Then the next 22 Columns are labeled steps with drop down boxes, once the have selected "COMPLETED" it triggers the macro and send the email, just not on the ones when it needs to email the PROJECT LEADER, since it's a variable. Below is the macro, how I can have the ".TO" line in the email reference the email address in Column C to the left of it?
Sub Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object
[Code]....
if this is possible without becoming a circular reference.
Aim: to have a Macro that takes user selected cell and converts it the result and place it back in the same cell.
Example: could have a typed value in A10 say 500, and when the cell i selected and the Macro is run is converts the value by B10 (.5) and places the value back into A10. ie A10= A10*B10 (250)
I know this is a circular reference but could the macro say take the Value A10 and hold it in a temporary string of some sort before calculating and then returning the new value in there?