# Moving On A Hlookup By A Number Of Cells Each Time?

Nov 2, 2009

I have been given a spreadsheet to fix as the user says the charts/graphs aren't adding the new data.

But when I looked closer at the workbook the whole thing just seemed a little wrong to me

All the data is entered into the sheet 'Data Entry'.

Then on the 'Data' sheet it should pull thru some key values but half of it isn't done and it isn't very smart.

I have created a 'TEST Data' sheet so I could start messing with it.

I was going to pull the data thru from 'Data Entry' into the 'TEST Data' sheet using a Hlookup to make sure it worked before i messd with the actual 'Data' sheet.

The problem I am having is moving/copying the Hlookup across.

On the 'TEST data' sheet the Hlookup is as follows and is correct:

=HLOOKUP(\$A\$5,'Data Entry'!\$C\$4:\$M\$472,2,FALSE)

But the next set of (Week 2) is not in the 2nd or even 3rd row of the array, its in row 11......... thats 9 rows down from the original.

Moving on the data i want to bring back is always 9 more than the last so the Hlookups should look like:

=HLOOKUP(\$A\$5,'Data Entry'!\$C\$4:\$M\$472,11,FALSE)
=HLOOKUP(\$A\$5,'Data Entry'!\$C\$4:\$M\$472,20,FALSE)
=HLOOKUP(\$A\$5,'Data Entry'!\$C\$4:\$M\$472,29,FALSE)

etc etc

Can any of you guys out there help on how I can do this or it may not even be a Hlookup but something even smarter!!

## HLookup Find Minimum Number In Row Then Return Left Most Number?

Mar 1, 2012

Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)

I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.

Cell
A1 Number >2000300040005000600070008000900010000
70 2864295717148571000114312861429
84 24236148059971883795610751194

[Code].....

## Perform VLookup And Hlookup At The Same Time

Jun 30, 2006

I was wondering is it possible to perform a VLOOKUP AND HLOOKUP in the same formulae or is there another way to perform this task. the attached example

## Run Time Error '1004' Hlookup In UDF

Jan 8, 2007

im having problems calling the hlookup function in vba. Here is the code im using. I get run time error '1004'. Ive tried both setting the range, and manually inputting it. The User Defined Function takes no inputs, but is designed to get the value based on the relative position of the cell in the spreadsheet.

Option Explicit
Option Base 1

Function lastrow()
Sheets("template").Select
Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
End Function

Function summary1()...............

Oct 30, 2007

i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:

If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.

with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).

## HLOOKUP In HLOOKUP, Base Estimate Table In Excel

Jul 10, 2009

I am trying to import a BASE ESTIMATE table into EXCEL.

I have problems with most of the formulas, especially this one:

=VLOOKUP(\$E\$2,\$B\$24:\$P\$604,HLOOKUP(\$E\$3,\$D\$22:\$L\$604,1)+2)*HLOOKUP(HLOOKUP(\$E\$3,\$D\$22:\$L\$604,1),\$D\$2 2:\$L\$23,2)

and this one

=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10

I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?

## VLookup (or HLookup) Cell With Reference Rather Than Actual Number

Oct 13, 2011

I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).

So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.

A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #

However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.

When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.

Can I do a vlookup and keep my cell references?

## HLookup - Return Correct Number Of Rows Downwards In Table?

Feb 8, 2013

Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?

## HLOOKUP To Work With Cells Containing Formulas

Dec 8, 2013

I have a niggling problem with a worksheet when using Hlookup to return a value from a range of cells it is coming up with blanks when there is data in the range.

There is only ever one row cell containing text in the specified range and i need this to be returned in another column.

The problem with the hlookup formula i am using is that it will not work if cells contain formulas and in the range i am working on all cells contain formulas.

I know this formula works if there is text without a formula in the cell range, as soon as it hits a cell with a formula though it won't work.

I have attached the sheet : Book4.xlsx

From j138 there is one cell with text i wish to be returned in column q for each row. E.g., in q1 the first cell text returned would be khawatim. q2 should contain cantor etc etc.

## Use HLOOKUP AND VLOOKUP - Or Macro (drop Down The Cells In B4 And D4 )

Oct 13, 2009

I would like to drop down the cells in B4 and D4 so that if throws up a result in D6 which looks at Sheet 2 and throws back that score? So it will look for Neil in Column A and October in Row 1 and throw back that figure.

## Date / Time - Subtract Number Of Days Between Cells Only

May 2, 2014

I have a cells with a date and time in each cell. I want to subtract the number of days between the cells only. Is there a way to do that with the time in the cell? If not, how do I remove the time in each cell?

## Moving Filtered Rows From Two Sheets At Same Time

Feb 15, 2014

My code below is supposed to move filtered rows in 2 sheets ("BANK ENTRIES" and "GL ENTRIES")with "Y"/"y" character on column J which is manually encoded by user. The rule is, before they can move, the total amount in column I in both sheets should match. That is the reason why rows in both sheets should be moved at the same time. I was able to figure out this rule with the use of an If Statement and a message box.

My problem is, if I run the code where there is only 1 row remaining in both sheets, "BANK ENTRIES" would insert a blank row in the third line despite my if statement

[Code] .....

The second problem is, If I run the code on "BANK ENTRIES", my first header on the "GL ENTRIES" will be deleted. If I run also the code on "GL ENTRIES", my first header on "BANK ENTRIES" sheet will be deleted. There must be something wrong on my code. I am attaching my working file for you to have a clear visibility on my problem.

Attached File : xx_xxxx_xxx_Template2_2013-11-30v3.xlsmâ€Ž

## Moving Multiple Cell References At One Time

Sep 3, 2013

I am working with a formula that has multiple embedded IFs and therefore references the same cell multiple times. However I am also copying it between workbooks for different companies so some of the information changes which column it is in. For example, there is a formula in AA5 that has 3 references to R4 and 4 to R5 and I need all of the R4 references to be T4 and all of the R5 references to be T5.

I can't do a "find and replace" because the formula in Y5 still needs to reference R4 and R5.

I have been clicking on R4 and dragging it 3 times to T4 (and the same for R5 to T5). It just seems like there should be a way to move all of the R4 references at one time to T4.

## Macro To Paste In Certain Column, Moving Over Each Time

Mar 31, 2007

I have this macro listed below that finds all these files, copies cells from stat sheet and places them in sheet1 in another file. It keeps looping till all is found and done.

My problem is when it goes to paste in sheet 1 I want it to shift over a row each time.

Right now during the first pass it pastes in column B, I want it to start in column D

The issue seems to lie right under where it says "Put data into workbook"

Public Sub PullData()
Dim wkb As Workbook
Dim lngStore As Long
Dim strDate As String
Dim strName As String
Dim Book As String
Dim Sheet As String
Dim week As String
Dim Store As String

Dim IngRow As Long
Dim strCol(0 To 15) As String

strCol(0) = "b"
strCol(1) = "c"
strCol(2) = "d"
strCol(3) = "e"
strCol(4) = "f"
strCol(5) = "g"
strCol(6) = "h"
strCol(7) = "i"
strCol(8) = "j"
strCol(9) = "k"
strCol(10) = "l"
strCol(11) = "m"
strCol(12) = "n"
strCol(13) = "o"
strCol(14) = "p"

## Moving Average Of Real Time Data At Timed Intervals?

May 19, 2008

Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:

a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm

b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.

c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.

## Number Line/Row With Moving Indicator

Jun 1, 2008

I am trying to have a Number Line representing age of retirement.

I want to have a cursor on it Positioned on the number line which will move to desired age as I change the value of Cell representing Age.

Can this be done on Excel. I think so. Using User form or any thing like that will be ok as I need it for presenting a report.

## Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date

Jul 5, 2006

I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.

Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.

## Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time

Mar 3, 2009

I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!

## Subtract Start Time From Finish Time Return Hours Worked As Number

May 5, 2006

I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50

Start time: 12:30
Finish time: 23:00
Hours worked: 10.50

## Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

## Dynamic Sum Function (number Of Sheets Can Vary From Time To Time)

Sep 8, 2009

I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.

Any idea about a dynamic sum function that will do this, in combination with VBA?

## Count Number Of Full Time Periods Within A Time Period

Nov 18, 2008

I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)

I also have numerous set time periods that i'm interested in.. (about a dozen or so)

for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00

What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.

So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2

and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.

and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!

## Userform With Time Field Displays Time As Number

Dec 24, 2008

I have a userform that has a time field on it and then the userform displays the time as a number.

Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.

## Moving Contents Of Cells?

Feb 9, 2013

About half of the time when I move the contents of a cell I get the message "The Clipboard cannot be emptied. Another program might be using the Clipboard"

## Moving Cells With Formulas

Nov 23, 2009

I am trying to move a group of cell containing formulas from one sheet to another, I have tried copy & paste/ ctrl & alt / paste special but for some reason the initial cell references of the formulas are not updated.

## Moving Cells Corresponding To Date?

Dec 3, 2012

Starting on Monday and ending on Friday and then excluding the weekend dates beginning again on Monday. I would like to be able to store a value within a seperate cell.

EX:- Cell A1 contains the value of Cell B1,(B1 being Monday's value) but when the date changes so the value of Cell A1 should become the value of Cell B2 (Tuesday's value) and when the date changes again the value of Cell A1 should become the value of Cell B3,(Wednesday's value and so on). This should happen until Friday and then the calculation should evaluate the two days of the weekend and do nothing before moving on to calculating Monday's value and displaying in Cell A1 the appropiate value.

## Moving Around To Only The Designated Cells

Apr 1, 2008

I have a excel spread with a range of A1:H64. The spreadsheet is designed for the user to enter information into the appropriate cells, i.e. user ID in cell D4; name in cell D5; and date in cell F5. My question is instead of the user scrolling around and finding the cells that require input is there a way to set the spreadsheet where the user tabs and/or uses the enter key to move around and go only to the cells that require input and not to the ones that are locked and protected?

## Moving Cells From A Row To A Column

Aug 26, 2009

it is possible to move a row of cells with (names in each cell) to a column. Basically, I want the list of names to go from top to bottom rather than left to right.

From this (each name is in separate cells)
John Jones Bob Smith Jane Morgan

to this:

John Jones
Bob Smith
Jane Morgan

## Moving In Merged Cells

May 8, 2007

I have a merged cell area, several columns wide and 2 rows deep.

I know that pressing enter before reaching the end of the first row moves to the next cell below. Is there any method, either key combination or whatever, of moving to the next row within the merged cell?

## Moving Shape Based On Cells Value?

Jun 27, 2014

following code is supposed to check whether cell contains a value and based on that either display or hide a shape. The last couple of lines are moving it. What I would need is to check all cells in a row (not only the first one) and if lets say first 1,2,3...10... cells contain a value move the shape all the way to the end of that cell and so on.

VB:
Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill

[Code].....