# If Statement/indexing: Column To Adjust Based On The Date Of The Transaction/trip

Jun 17, 2009

I have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip.

Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.

## If Statement Showing Text Based On Date Value In A Column

May 14, 2009

I have a worksheet with a range of dates in column E. What I would like is a formula that states If any of those dates is less than or equal to 10 (days) from today and column H text is not "Yes" then show text "Email outstanding Actions" else show nothing?

## Date Of Last Transaction

Oct 1, 2009

I'm dealing with 2 spread sheets. The first, "Transactions", lists transactions by date, but they are grouped by series. So the 7 series dates are ascending but when you continue to the 8 series it starts at the first date of transaction and ascend to the latest. The second spread sheet, "Sheet1" is an inventory and lists date of last transaction, part #, Part name, sorted ascending by part #. I need a function that will reference the Part # in sheet1 and yeild the date of the last transaction made with that part #. I have not been able to figure it out.

## Finding Earliest Date And Transaction By Name

Apr 2, 2012

I'm working through a large database on sheet 1 that has multiple transactions for each person, what i am trying to do is find the earliest date and the first amount that each person deposited money. The findings will go into a table on sheet 2

This is a dummy data of what i have: Sheet 1 - database....

## Get Grouped Data Corresponding Last Transaction Date

Apr 26, 2008

I am trying to automatically show the date of the last transaction per part number. I have attached an example. The formula should also consider the data in WH, BRN, and TRNTYP columns

## Adjust Column Label Selection Multiple Pivot Tables Based On One Pivot Table

Aug 16, 2013

I have a pivot table in the first sheet which includes the field "Date" as a column label.

In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.

I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.

note that the field "Date" is used as a Column label, i.e., it is not a Report filter.

## Adding Author Column To Excel Indexing Sheet

Jul 2, 2014

I'm trying to get a document together that will spit out an index of all the files in a folder/subfolder with various attributes like title name/ hyperlink to the file/ type/ etc. How to add a function that will create a column/pull data for the author of the files I am indexing.

[Code] .....

Here's the website: [URL] .....

How to get a column that lists the author of the document.

## Indexing Multiple Columns To Return A Result From A Separate Column

Oct 22, 2009

Sheet2:
col A = contains the style#
col B = contains the color of the style
col C = contains the size of the style
col D = contains the qty of the style,color, size

Sheet1:

I would like to do the following:

A1 = input the style #
B1 = input the color of that style
C1 = input the size of that style

then D1 should automatically contain the qty of the mentioned style, color, and size.

## Setup IF Statement - If Date In Column A It Will Return Q3?

May 2, 2014

I wan to set up an IF statement so that if the date in Column A is between 4/28/14 and 7/17/14, it will return "Q3" and blank if no date.

## Vlookup: Tally Casino Trip Results

Aug 14, 2009

see the attached spreadsheet...I have a few notes of what I want to do. I think it is a vlookup, but i can't figure out how to do these.

## AVERAGE IF Statement Based On Matching Condition And Date Ranges

Feb 22, 2009

I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.

I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.

It looks something like this:

## Delete Row Based On Value In A Column Using IF Statement

Oct 8, 2012

I have a data of 10 rows and 6 columns, if i delete the data in column 1, I want the entire row 1 to get deleted and this I want using IF Statement.

## Nested If Statement - Calculate Value Based On All Of Active Cells In Column

Sep 11, 2013

I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)

I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?

Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select

[Code] .........

Aug 3, 2006

I currently have dates in a column in the format DD.MM.YYYY. I would like to convert this into MM/DD/YYYY, with the slashes instead of the periods.

## Adjust Multiple Column Widths In One Column On Button Click

Nov 19, 2006

I have a button for Column T that when clicked I would like to run through different column widths 25,35,45,55,65,75.

I've tried a case statement but it doesn't run through each case on the clicks.

## Adjust Date Range Plotted On Chart

Oct 14, 2008

I am trying to get some help by adjusting my datarange dynamically based on what days I select. I would hope the chart could adjust to display for example 10/3/2008 to 10/10/2008 values and only those values.

I have been trying to figure out how the datarange varies in this post Dynamic Chart dataranges, I do not understand how to apply them to the charts source data. Ultimately Krish's chart would be ideal to understand and I belive is the keystone to where I am trying to go.

I have attached a sample of my workbook and the source data and chart I am trying to manipulate.

## Macro Adjust Row And Column

Nov 9, 2008

Is there a way to make a macro to make this true?

The WS is 300 rows,
The column width is 6.90

If LEN(B1)>147 then format ROW Height to 0.25
If LEN(B2)147 then format ROW Height to 0.25
If LEN(B300)

## Formula To Adjust Based On Target Cell

Mar 11, 2007

I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.

I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.

I have attached the spreadsheet which will be clearer.

## VBA Code - Adjust Column Range

Dec 19, 2013

I have this VBA (from a recorded macro) how can I amend it so that these two formulas change depending on the number of values....right now the range is to 129 only because we only have 129 cells of info but some sheets will have more some will have less. I want excel to base it based on the column I which used to be H.

VB:
Sub SLCREPORTDATA()
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Columns("H:H").Select

[Code]....

## Adjust Column Or Row Width With Keyboard?

May 31, 2013

I want to know that can we adjust column or row width with keyboard.

## Conditional Formatting That Will Adjust Based On Percentage Ranges

Sep 29, 2011

I need to have a conditional format that will adjust based on percentage ranges.

1% to 25% would be shaded red
26% to 50% would be shaded Orange
51% to 100% would be shaded Green

I was hoping to reference another cell and have the formatted cell reflect the conditional format. I dont think that's possible, so I can include a percentage in another cell and have that highlighted.

## Adjust Worksheet Placement Based On Dropdown Selected

Feb 8, 2012

I have created validated dropdown, which in turn I have used for conditional formatting to highlight specific consecutive rows based on the dropdown.

Is it possible for the worksheet to be positioned based on the dropdown chosen, so that the highlighted rows are visible without having to manually scroll down, looking for the highlighted rows?

If dropdown chosen highlights rows 50-60, I would like the page to automatically show rows 50-60 (not necessarily hide the others, but at least scroll to rows 50-60).

## Adjust Height For Individual Column In Chart?

Aug 15, 2014

Refer to enclosed excel file, I have drew a chart base on the data in side. However, for the last column which is "SG Productivity(KUSD)", The column very small even cannot be seen, is there any way can enlarge columns in this field without changing other column? T

## Run Macro And Adjust Column Width Range A:Y

May 3, 2014

i want a code to adjust the column A:Y from width

## Column Numbers To Adjust When Rows Are Hidden

Feb 12, 2014

I have multiple tables that populate with data based on a combination of metrics that are tied to various dropdown menus. Whenever a combination of metrics yields a row without a value in at least one cell, then that row is automatically hidden. My problem is that while the rows are hidden, the column number's don't adjust. For example, if row 3 is hidden in a table with 5 rows, then the column numbers appear as 1, 2, 4, 5 (the table will shift from 5 rows to 4).

## Combobox On Worksheet - Adjust Width Of Column

May 1, 2014

I have got a few combo boxes on my worksheet. But when I adjust the width of the columns the size of the combo boxes changes as well. How can I make it so that the combo boxes stay frozen even though the columns size is being changed?

## Formula To Enter A Date In One Column Based On Date In Another?

Jun 10, 2013

is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.

## Compare Dates In Table & Adjust Adjacent Value Based On Result

May 6, 2008

I have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES"

Sub CountTheCells()
Dim cell As Excel.Range
Dim i As Integer
For Each cell In Sheet2.Range("DATES")
If cell.Value < Sheet2.Range("POPS") Then
i = i + 1
End If
Next
MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins")
yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo)
If yesno = vbNo Then
MsgBox ("Not Done")
Else..........................

## Adjust Column Width Of Protected Worksheet Without Unprotected It?

Apr 9, 2014

Is it possible to adjust column width of a PW protected worksheet without unprotecting it, or unprotect it and adjust in background then PW protect it?

On sheet1 H1 = column width value

## How To Get Wrap Text To Auto Adjust Height Of A Column

Apr 16, 2014

I have a number of cells where I've put to wrap text, but the cell size is not adjusting when I type past the size of the cells, how do I get the cells the get bigger automaticly to fit all the text but bigger in height not length?