Store row number after a find
Following a find command I want to store the row number to a variablke and
use that to move to a columb on that row
what column? same column? different column?
> Following a find command I want to store the row number to a variablke and
> use that to move to a columb on that row
Any column on that row. Real problem is storing the 'found' row number to a
> what column? same column? different column?
> "Alan" wrote:
> > ...row reference
i would like to sum a series of numbers in columnB and put in cell C1.
which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in
other words, i want to sum the numbers from B13 to B29 in this case. i know
i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be
changing frequently. next time A1 and A2 might be 9 and 36, respectively,
thus summing cells B9:B36. i do not want to manually change the formula in
C1 every time i change A1 and A2. i also do not want to create a macro. any
thoughts? thanks, mike allen
A couple of ways
=SUM(INDEX(B:B,A1):IND...Specifying Row / Column in Import File
Hello, first time poster here...
I've a question regarding the import of Excel data. I'm going to b
creating a flat file on a mainframe, copying it down to a PC and usin
Excel to import it. I have a template that has cells and calculation
associated with it. Now, what I would like to do is to have th
mainframe prefix a record with a row / column coordinate, for example:
A1,Data for Cell A1
B1,Data for Cell B1
A2,Data for Cell A2
This is a simple example, but the program on the mainframe will outpu
the cell address based upon some rules as it rolls through the data.
How ...Sorting rows without messing up cell references??
I have a several tables that refer to one another. I'd like to sor
some of them, but doing so while keeping the formulas as relativ
references messes up each cell references. If I change all the ro
references to absolute, things get mess up if I sort the original tabl
being referred to.
Is there any way to get around this without manually cutting an
pasting each row around into the right sort order?
Message posted from http://www.ExcelForum.com
If you can find a unique value in each table, maybe you could change your
Say one table is on sheet1, A1:E99 a...Transform row to a column
Can i make values that listed in a a row, to shown in a column
for a example cells A1:1 B1:2 C1:3 D1:4 to become cells A1: 1 A2: 2
A3: 3 A4: 4.
Thank you in advance!
dealwi8me's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18854
View this thread: http://www.excelforum.com/showthread.php?threadid=549315
One way: Select A2:A4. In the formula bar type "=transpose(b1:d1)" (without
the quotes), but don't press Enter....How increase max # of rows/sheet in excel?
I have a huge report 250,000 rows generated for me at work (John Hancock). I
use pivot tables to categorize the data. TAt this point I have to seperate
the data into 5 worksheets, create 5 pivot tables, and then aggregate them.
Is there a way to increase the maximum # of rows per sheet in excel above
the 65,000+ default? For instance to 250,000+. That would save me a
tremendous amount of time as I would only have to generate one pivot table.
no not possibe. 65536 is the maximum. Though with this much of data you
sgould consider using a database (e.g. Access) and create pivot tabl...EXCEL HELP (ROWS & DATA)
I have a table in Word that has many rows and cells with text in it.
I have a column of text in excel.
I would like to get in excel the information from the work document of just
the text that is found in my excel column.
I tried vlookup but the document in word may have a sentence in it and vlook
look at a whole cell not part of it.
Thanks for the help very much.
Example: word docuement has "table four is next to table three"
in excel I have a column
...Add rows to a formula when copying
I have the following formula in cell C3
I wish to copy this to cell C16 but wish to add more than the 13 row
(ie 16 - 3) to each reference in the worksheet Calculations. I need i
to add 27 rows.
The end result formula I need in cell C16 is
Is there any easy way to do this? Happy to do it using a macro i
necessary. To sum up, I would like to copy a formula and...Insert Column and Row Tools Greyed out on custom toolbar
In OS 10.2.8 when I put the Insert Column and Insert Row tools onto my
existing or onto a new toolbar using the Customize - Commands Dialog Box,
the tools don't work. They are greyed out.
However, it works on one computer, but not several others, and all versions
appear to be the same.
All other tools appear to be working normally, including Insert Cells,
Delete row and delete column.
Any thoughts as to what is going on?
There are two sets of Insert Row/Insert Column Controls in MacXLv.X.
The controls in the Customize/Commands/Edit category are for the
List ...How? compare two worksheets and identify rows of data that appear on both.
Suppose I have 2 worksheets.
Sheet 1 has 1000 lines of data spanning xNumber of columns.
Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.
How can I get to a point where I can build one worksheet that is a composite
of both sheets - such that no identical line of data appears twice on the
Thanks in advance, Simon.
You could do this several ways. It really depends on your data
integrity and what sort of data it is.
-- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row...add number of duplicate fields in a row or column and have total
I have a log of jobs done on a photocopier, and need to bill people for what
they have used.
the log is very long and have the user name and the number of colour and
number of Black and White copies they have done.
I need to know how much colour and how much black and white jobs have been
done by each user.
hope someone know a way thanks
or am I better to use Access ?
THe assumptions made
People in A1:A20
Type in B1:B20, C for colour, B for B&W
Quantity in...Counting the Same Rows
I have a spreadsheet with 2 columns column a is CustomerID and Column B
I need a way of counting how many transaction dates a customer as
column A can have the same customer ID in multiple times
Any help appreciated
Steve M's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32520
View this thread: http://www.excelforum.com/showthread.php?threadid=534495
Is it just
or do you need to test the dates as well
=SUMPR...How to restrict the number of rows & columns?
can some one help me in konwing how to restrict the number of rows and
columns in an excel sheet
You cannot change the number of rows or columns, but you could hide any
beyond the ones you want to see.
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"sridhar" <email@example.com> wrote in message
> can some one help me in konwing how to restrict the number of rows and
> columns in an exce...Row and Column Title Background Color Doesn't Copy
I am trying to use the "copy picture" function to save a portion of my Gantt
to a .gif file to paste into PowerPoint.
In Project, the row and column titles appear gray but when I paste the .gif
into PowerPoint, they have no color.
My client is getting picky about this...he liked it with the gray. AAAGHHH.
That's why a lot of folks end up using something like SnagIt to do
copy/paste. It's a free 30 day download.
> I am trying to use the "copy picture" function to save a portion of my Gantt
> to a .gif file to pa...Excel 2007: Huge Spreadsheets (>1 Million Rows, 16,000 Columns)
Well, I don't yet have my copy of MS-Excel 2007, but I am happy to see
that (in theory at least) the spreadsheets one will be able to generate
will be much larger than the 256-column, 65,536 (?) row spreadsheets of
the MS-Excel I now have. According to Microsoft:
[Excel 2007 provides] "the ability to create single sheets of over a
million rows and 16,000 columns."
Well, I certainly hope this is true! It would be very useful to me.
I wonder if anyone has tried to work with this feature?
http://www.FreewareFri...Macro insert data, need to insert rows
I have a macro that imports data from a CSV text file. Currently, it only inserts about 30 rows and 6 columns. Four of those columns have numbers that get totalled at the end of the import. My totals line is at line 45 (the import starts at row 6). The problem is that if more rows are added, and the data import goes past row 45, it moves my totals line over to column G (column 7) and nothing gets tallied correctly
How can I make it so that, when the macro runs, instead of overwriting or moving my totals line it just adds a row above the totals line? I want it so that if the data goes to ...Row Insertion Problem
I have a worksheet with a hidden column of formulas, each formula making
calculations using data on the respective row.
It is fine until a user inserts a row, as (unbeknownst to the user) the
respective cell of the row will be blank, causing calculation errors.
How best to tackle this issue?
Many thanks in advance.
One thought ..
Safest and easiest to protect the sheet, with a password
Prevent any damage caused by users inserting/deleting rows/cols
Downloads:23,000 Files:370 Subscribers:66
"robot"...how to fix starting point when counting rows?
is there a way to feed in the the starting cell to begin from when
counting the number of rows?
i can get the below to work in a macro
countRows = Range("C9").End(xlDown).Row
but when i try to turn it into a function i cant figure out how to
give the starting cell as an argument
tks for any help
Put "C9" (without the quotes) in cell A1, for example, then you can do
Is that what you mean?
Hope this helps.
On Aug 29, 4:08=A0pm, polarcap <mem_...@...Insert Row with a macro
When I use the following code in an Excel macro to insert a row, it does not
work correctly. Instead of inserting just one row above the TOTAL EXPENSES
row, rows will continue to be inserted until the worksheet runs out of rows.
Could someone please help me.
For Each c In [A1:A150]
If c Like "TOTAL EXPENSES" Then
I have to use the "TOTAL EXPENSES" instead of the row number because this is
a report I import from another program and the row number that TOTAL EXPENSES
appears in varies from report to report depending ...Not able to sort whole rows
I have a protected sheet with password say "1122"
In the protection window I have checked the box of "Sort"
I selected whole lines from 14 to 40 and tried the: Data|Sort and had the
"The cell or chart you are trying to change is protected and therefore
Any sugestion how to solve this using a macro or VBA code for a button
I need to sort according to column F (which is hidden).
What code do I need to hide 2 groups of columns:
DR:FQ and G:AB
Unprotect, do the sort then reprotect.
ActiveSheet.Unprot...Insert a number of rows based on a value in a cell on active row
Insert a number of rows based on a value in a cell on active row
Hey all. I am new to macros in excel. Hoping to find help. I have
worksheet, 3 columns and lets say 1000 rows. Column A is a beginnin
page number of a document, column B is its ending page number, an
column C is the number of pages calculated by subtracting Bx - Ay an
adding 1. Example:
1000 1001 1
1002 1005 4
1006 1007 2
1008 1008 1
I'd like to have a macro which would go line-by-line and insert a
appropriate number of rows in bet...insert a row on ALL sheets
I have a spreadsheet with 12 sheets in it, one for each
month of the year. Is there a way to insert a row on all
12 sheets at once or do I have to go into each sheet and
insert the row one at a time? In other words, do I have to
do it 12 times or is there a way to do it only once and
have it affect all 12 sheets?
"bw" <firstname.lastname@example.org> wrote in message
> I have a spreadsheet with 12 sheets in it, one for each
> month of the year. Is there a way to insert a row on all
> 12 sheets at onc...Attach Rows with Row Data
I am trying to sort by Data/Time in a Column, but if I sort by Date/Time,
Just that changes, the other columns don't change with it.
How can I either LOCK or ATTACH the Rpw data together so when I sort,
Everything changes, not just the Date/Time?
just select the entire data range (all columns) prior to sorting your
"Admiral_Kirk_1" <Admiral_Kirk_1@discussions.microsoft.com> schrieb im
> I am trying to sort by Data/Time in a ...Shuffling Rows
How do I shuffle ROWs on microsoft excel
Message posted from http://www.ExcelForum.com
Could you be a bit more specific about what exactly you want to
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"wefva00" <email@example.com> wrote in message
> How do I shuffle ROWs on microsoft excel?
> Message posted from http://www.ExcelForum.com/
...How do I count non blank rows only?
This seems so simple but I've spent hours without resolution. In column A,
I need to count the number of rows that actually have data in them. If there
is no data, I need to keep column A blank. Here's an oversimplied example of
my simple list.
1 John Doe
2 Susan Smith
3 Joe Dear
In column A now I have =COUNTA($B$2:B64) and that works great until you
hit a row with no data.
I have tried all these:
There's got to be an easy way that I'm missing!! HEEE...