Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Populating rows Results

I need to pull values from multiple cells in a single row and place
them into another worksheet in specified cells. I have setup a pull
down in Worksheet2 to show all of the values in a column on Worksheet1.
When I select one of these values in the pull down I need to grab 6
other cells in Worksheet1 (from the same row) and place them into
Worksheet2 in different rows/columns.
Is this possible, and do I need to use a macro to accomplish this?

I appreciate any help.

Thanks,

Chad

I am trying to build a reporting tool where each department enters it's own
information. From that I want it to populate a summary sheet. The summary
sheet has the same column as the data sheet. Is there a way to do this?

I am having a problem finding a way to find specific values in a column
and give a value in the row where that specific value is.

Ok here is what I am trying to do. I have 2 worksheets, 1 with the
months as the headings, the other with data. I have dates on the data
spread sheet going down and there are figures associated with each of
those dates. I am trying to sum up the figures for specific months on
the 1st spread sheet. So far I created a function that gave a month in
text associated with the figures. Now I want to populate the sums of,
say all "January's" on the 1st worksheet. I have used excel a lot and
can not seem to figure this out. Please help!

I am not sure if that is explained right, please let me know if you
need more info!

Pat

Hi,

I have a spreadsheet which has columns with headings A-F. Under these
columns I have some number e.g. 5, 9, 12, 27, 36, 47.
i.e.
A B C D E F
5 9 12 27 36 47

I also have 6 blank columns headed RowA - Row F, plus a further 6 blank
columns called ColA - ColF.

I have a lookup table (7x7 grid) that maps rows and columns that I wish to
use. Basically the grid shows:
Column1 Column2 Column 3.... Column7
Row1 1 2 3... 7
Row2 8 9 10... 14
....
Row7 43... 49

I want to lookup the number displayed in A and find out which Row and which
Column it lives in according to the table. i.e. the number 8 would be in
Row2, Column1.

I want to put the row result under RowA and the column result under ColumnA.

I appreciate there probably a million ways of doing this and I really don't
know Excel well enough to know the best one.

Does anyone have any ideas?

Thanks.

I have a pricelist/configurator helper workbook that I have produced in Excel with 12 worksheets (8 visible/4 hidden). My sales people enter nummeric data in columns matching the item they want in their proposal in each of the 8 visible sheets. Each sheet contains a different part of the product mix being proposed. The inputs are automatically copied in cells in the hidden sheets where the pricing function occurs. The inputs are also copied to the last visible worksheet that is the "pricelist" for our proposal.

Here's the issue.

After the input of each proposal is complete, the salesperson has to manually delete any rows that do not contain a zero quantity in the last worksheet (the "pricelist'). I have seen other Excel spreadsheets that automatically insert the rows that have numeric data in them in a logical fashion. I have never pursued how to do it but our proposals are now large enough to cause this to be a MAJOR inconvenience to the sales reps. I would like to have the last sheet in the workbook automatically populate with data from the other worksheets that have a numeric quantity associated with them.

Is there a plausible way to do this without being a Jedi Master of VBA?

--
Message posted via http://www.officekb.com

Hi there

I have a main sheet, this sheet is used to log calls for several
different managers.

these managers also have a seperate worksheet. Is it possible to
populate these sheets so it displays all the related rows from the main
logging sheet.

I have tried using a vlookup, but this returns the same row over and
over again.

Any help would be gratefully appreciated

--
brownsatan
------------------------------------------------------------------------
brownsatan's Profile: http://www.excelforum.com/member.php...o&userid=31218
View this thread: http://www.excelforum.com/showthread...hreadid=508918

Hi folks, my first post here. Hope you can help.

I've created a sheet that contains 2 user input variables, call them
NUM1 and NUM2, followed by a bunch of calculations based on those 2 as
well as other "static" values. Each calculation is done separately to
keep it easy to follow. All that crunches out a number, call it RESULT
The purpose of the entire sheet is to see what combinations of NUM1 and
NUM2 make RESULT either negative or positive.

NUM1 has about a dozen possibilites and NUM3 about 3 dozen which
results in over 430 possible values for RESULT.

Ultimately I want to display those results in a table with all possible
values of NUM1 as column headers and NUM2 as row headers.

Is there any way I can get Excel to populate each cell in the table
with RESULT using the NUM1 column and NUM2 row headings?

I know I could do this if the formula was simple but as I mentioned
before, the steps to get from NUM1 and NUM2 to RESULT take up 40 rows.
Most are simple single operator math but some are more complex. I'd
never be able to get all those steps into one formula.

Can it be done or am I stuck with going back and compressing all those
40 steps into one long fomula?

Any suggestions would be very much appreciated. Thanks in advance!

--
wildswing
------------------------------------------------------------------------
wildswing's Profile: http://www.excelforum.com/member.php...o&userid=30853
View this thread: http://www.excelforum.com/showthread...hreadid=505213

I have a large spreadsheet that I enter data into from one of our
laboratory instruments. Data Initially got dumped into cell i10.
Columns a-f contain various formulas (which are mostly used as lookups
for other workbooks) that are dependant on whether there is a value in
the cell i10. Each Column has a different formula.

At the moment, everytime i enter in new data, for eg in cell i11, i12,
i13 etc, select the range from a-f then I drag the fill handle
downwards to populate the new rows so that my formulas are repeated.
(I elected not to populate the formula in anticipation for the growth
in data to row 65000 as the workbook size became to big and
performance was slow).

If there a way to populate the relevant row in colum a-f by copying
the formulas down automaically?

Thanks

Cameron

Hi All, How can I compare values from Sheet1 ColumnA and Sheet2 ColumnA and
populate Sheet1 Columnb accordingly?

For eg: ColumnA in sheet1 and Sheet2 have the same values, But there are
instances where certain cells in Sheet2 columnA are left blank.

here is the eg:
Sheet1:
ColumnA
ann taylor 1
ben coy22
can loy 2
den zen
eno zor
fan tan4 c

Sheet2:
ColumnA ColumnB
ann taylor 1 11
ben coy22 12

can loy 2 13
den zen
eno zor 16
fan tan4 c

so there are blank rows between b&c and between d&e also there is no value
for d and f and hence it should not display any value for d and f in Sheet1
ColumnA

Here is how the Data in Sheet1 should look like:

Sheet1
ColumnA ColumnB
ann taylor 1 11
ben coy22 12
can loy 2 13
den zen
eno zor 16
fan tan4 c

Hope I made it clear

Thanks in advance

I have a sheet at work that is constantly adding rows to add more
items and their details to it.
Items are added and associated by a category in A4:A9999.

I want to have a combo box that will only show the rows for a given
category, then hide rows not equal to the value chosen.

So:
a) determine all distinct values in A4:A9999 and populate the combo
box with these values in real time if that won't be too hard on the
CPU, but somehow update it.
b) Then, when the user chooses a value from the combo box, all rows
are hidden except the A:A cells equal to the value and the A1:A3
(headers)

I am stuck on how to do a).

any ideas? thanks

Is there a way to do the following?

lets say there is a range spanning rows 1,2,3, with 3 columns
A1 = B1 + C1
A2 = B2 + C2
A3 = B3 + C3

Lets say I enter a blank row at 2
A1 = B1 + C1

A3 = B3 + C3
A4 = B4 + C4

Is there a way to automatically fill in the formuals on row 2 so when
I insert a blank row I get this:
A1 = B1 + C1
A2 = B2 + C2
A3 = B3 + C3
A4 = B4 + C4

The idea behind this is I'd prefer not to have to prefil lots of cells
with formulas, and instead expand them as needed

I have three userforms, each with a combobox that I need to populate with
items from the same worksheet. Also each list that populates each combobox
has the possibility to increase is number of items. The name of the
worksheet the holds all the data is "Items"

1) This is the largest and can probably use a feature I have seen where the
command scrolls up to the last empty row. I need to pull item number and
item description from "Items". Starting cell for each is A3 and B3
respectfully. Ending cell is unknown due to the possibility of adding new
items. However, I will not enter any data having to do with anything else
past the end of the list. I only need to populate with the item number but
need to be able to place the item number and corresponding item description
in the appropriate cells (already have code to place data in cell).

2) Currently, I have station numbers 1-7 in C3-C9. This may be added to if
a new EMS station is built, ie station 8 would be in C10. How do you
populate a combobox using same row. NOTE: I have data to the right of this
list that pertains to another combobox (see #3).

3) Currently I have a list of the ambulance numbers (M1, M2, M4, M7, etc) in
a list starting with M3 and listing down the sheet. I am not sure if you can
pinpoint that column and still do a command that locates the last empty cell
in that column or not. For this example, let's say M3-M20 contains each unit
number, with the possibility of adding more.

Thanks!

Hi all,
I recently created a diagram of our warehouse's aisle layout in an Excel
worksheet. On the spreadsheet, all locations in the warehouse are outlined
with a border and are laid out in columns. Each location is three rows high
to represent racks within each location: a top, middle and bottom rack per
each location. Consequently, the warehouse is laid out by aisle (letter),
section (number) and location/rack ( a number followed by a t, m or b to
indicate rack position). Thus, inventory in A1-42t would mean that it is
located in aisle A, section 1, location 42 top rack.

When we get a customer's inventory in, each pallet has a unique number. When
the pallet is put away in the warehouse, we record the location on a paper
form. Later, I tranfer it into a Powerpoint diagram I made so that our guys
in the warehouse can locate them more easily. I would like to automate this
process by switching to the Excel spreadsheet (described above). I would like
to have a userform where I enter the pallet number and the location (i.e.,
A1-42t) and the form will transfer the pallet number to the correct location
on the sheet containing the diagram.

Does anyone have a solution to what I am seeking?

I have two worksheets that contain information about the same items
(JobNumber) with one sheet being more current than the other. I want to
update the old data with new data.
What is the best walk sheet.newdata and compare it to sheet.olddata and
update sheet.oldata with info from sheet.newdata?

On sheet.olddata I have several columns including:

JobNumber Hours Dollars Descr1 Descr2 .......

On sheet.newdata I have only columns:

JobNumber NewHours NewDollars

I want to walk sheet.newdata and for each JobNumber I want to see if it
exists on sheet.olddata. If it exists, I want to replace Hours and Dollars
with Newhours and NewDollars. If the JobNumber from sheet.newdata is not on
sheet.olddata, I want to add it to the next available row.

Thanks

Hello-
I am new to macros and would like some help. I have a spreadsheet tha
does some calcuations based on numbers you input (cells B9 to E
require you to input numbers, F9,G9,H9 and I9 report numbers from shee
two cells that are generated by the B9 to E9 pn sheet 1).I have
command button that copies the B9:I9 Line down to B13, then clears B
to E9 for new data entry. I'd like to be able to hit my button agai
and have the macro go to paste at B13, see that data is there an
proceed to B14 and paste and then next time see B14 is populated go t
B15, etc.. Any help with this is appreciated - below is what I hav
assigned to my command button.

Also I only have 97 so keep that in mind
Thanks -Tom

Range("B9:I9").Select
Selection.Copy
Range("B13").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False
Application.CutCopyMode = False
Range("B9:F9").Select
Selection.ClearContents
Range("A9").Selec

--
Message posted from http://www.ExcelForum.com

Here is the problem:

I have one worksheet (we'll call it "Segment Data") containing more rows
than are normally necessary, to accomodate projects that happen to have more
segments that are average. For most projects, many of the rows will not be
needed and will, consequently, be left blank.

A second worksheet (we'll call it "Sales Summary") will be designed with as
many rows as "Segment Data" and will be auto-populated with data from the
corresponding rows in "Segment Data." Therefore, if any rows in "Segment
Data" are unused and are left blank, the corresponding rows in "Sales
Summary" will be displaying all zeros.

Is it possible to use Conditional Formatting or other techniques to hide the
rows in "Sales Summary" if their corresponding rows in "Segment Data" are
blank?

Please Help. I can't seem to get the data entered on one workbook to enter on
another workbook without over writing. I've set up a master seed file where
client data is entered and then saved as the client name. Its data entered
into rows is then set to auto populate the second workbook's first row. Is
there a method by which a second set of data will automatically know to skip
down to the next unused row on the second workbook? Anybody?
Thanks!

OS: Win2k Pro SP4
App: xls 2003 SP2

I have a problem that appears to extend beyond my skills. I have a
Production List, it has a list of recurring reports produced out of the
department. In here I have 2 columns that show me whether the due date (col
F) is a calendar day or work day or weekday, etc, and the second column (col
G) shows me the actual "date" (be it 1, 2, 3, Monday, etc.; it may have
negative numbers if the due date is +/- x number of days from Last Work Day
which is also a parameter in col F).

I created a grid Col X has a list of unique records from Col G (actual date)
and Row 2 has a list of unique records from Col F (date parameter). I now
want to count how many reports I have each due date for the specific
parameter. My grid looks like this:

CD WD LWD DAY As Required
-1
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
25
1-30
15-30
Dynamic
Monday
Tuesday
Wednesday
Thursday
Friday
Week1

I tried to use Index/Match combo but couldn't figure out how to plug in the
actual countif (well I tried countif aone first and it just didn't work for
some reason, it gave me all zeros); plus I will like to make grid row/col
dynamic as the file changes so I don't have to keep renewing and repopulating
the formula on the grid. I will like the formula(s) or VBA code to use a
match type function to find the location of the data currently in columns F
and G in case additional columns are ever added to this spreadsheet.
Unfortunately since our job is to provide the field with what they need I
can't standardizee the data in Col F & G, that's why I need to keep filtering
for unique records depending on what we've entered there. I try to keep it as
standard as I can. Col F title is "Req Date Parameter" and Col G is "Actual
Due Date"

I hope someone can help me because I really need to use this data for
workflow, resource allocation, business planning, risk assessment, business
continuity, and identifying capacity issues so I'm constantly recalculating
stuff. This will take half the work off it. I WOULD REALLY appreciate any
help with any piece of this puzzle. I will be happy to provide a sample file
to anyone. My VBA is very limited, so if you are going to suggest VBA please
provide any help/direction/code you can.

THANKS!
--
Hile

I have a order form with products listed on Worksheet 1 (10,000 rows of
items). Once users have selected and placed a quantity amount of the
requested items, I would like to have just rows where a quantity is present
on Worksheet 1, to be reflected on a new worksheet (Worksheet 2); inclusive
of all of the information in the relative row from Worksheet 1. Once
Worksheet 2 has been populated, I would like a cell to total the order
amount. I am not sure how this is done and would appreciate any help
available.
--
Thanks...Nina

Help Automatically Populating A Summary sheet

I have 2 worksheets,:-

On sheet1 I have a series of columns going across the page (left to
right) each relating to one month. The rows going down the page relate
to the items sold that month. Each month a new column will be added
(relating to the month that has just ended) so on the 1st of March I
will add a new column relating to Februarys sales.

Sheet 2 is a brief summary of sheet1 and comparison of the last
6months sales. I would like sheet2 to update automatically as soon as
new data is added to sheet1. I would like it to detect the right hand
column of sheet 1 ie the last data added and display it on sheet 2.
Further I want it to total the last 6months data (the last 6 columns
on the right of sheet1)

Could somebody advise me how I can achieve this please?


No luck finding an answer? You could always try Google.