Free Microsoft Excel 2013 Quick Reference

Return to original cell Results

Hi,
I have a combo box to list the month and setup cell link in b2. Problem is the return value show in b2 is id number from the combo box list not original value i.e. Jan, Feb...... Anyone know how to fix this ?

THX

=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)

The formula you gave me above worked great until I added a few more rows of data to the Jobs worksheet.

Any entry that was added past the original entrys will not lookup.
Did I loose the formula referance?
should I rename the worksheet?

What do I need to do to get this to work again?
It works down to row 50 but after that nothing is returned I check to make sure all cells were formated the same and I also tried sorting the list ascending didn't help.
I will need it to search at least 100 rows

Thanks
Charles

Although I like a lot the way that Excel handles data analysis (and
especially the Pivot Tables) I think that there is still significant "room"
for improvement.

Here is what I suggest:

Regarding FILTERS we need the following improvements on functionality and
ergonomy:

1) to be able to formulate more than 2 conditions - probably 10 would be ok;
I know that this could be accomplished by using Advanced Filter but this is
very time-consuming
2) possibility to create conditions that relate current cell with the
coresponding cell from other column
3) to filter after more than one value and on multiple columns when we click
on "AutoFilter" icon - according to the cells we have selected while keeping
CTRL pressed
4) to obtain a "Show excluding" filter when we click on "AutoFilter" icon
while pressing Shift
5) a corresponding button to "Show All" which enables us to remove only the
filter of the current column (or selected columns) - a shortcut key like F6
would also be very useful - so if we have 5 active filters we could easily
cancel one and keep the other 4
6) to be able to see (and activate) each one of the last 10 different filter
conditions used when we click on the filter arrow attached to the header cell
of a filtered column - this could be shown either on the left or the right
side
7) to be able to save and load certain set of filters - this is very
important when you work with more than 3 custom filters and occasionally
press "Show All" or when you need to switch between different sets of filters
8) to view the current filter condition when we float the mouse pointer over
the column's header cell
9) enhanced visibility for the active filters - not only the small blue
arrow on the grey square

Regarding PIVOT TABLES:

1) to save the pivot table "settings" in order to be able to switch quickly
from one "look" to another - I often have to make at least 4 different copies
of the same pivot in order to quickly show the desired aspects of different
analysis (on the same data set) to my superiors
2) when we make a copy of one pivot table in the same workbook Excel should
base the new pivot, by default, on the "database" used by the original pivot;
3) when we double-click on one of the detail fields the new sheet that
appears should keep the same formatting as the "database" sheet; by
formatting I also refer to the settings like Group/Ungroup and Freeze Panes
4) it is extremely necessary to have a function that allows us to copy the
data from the TOTAL field in a pivot table to a special field in the pivot's
"database"; this would help us when we need to group the data we analyse in
different categories according to the sum in that speciffic field - one usage
is when we need to separate the products that were sold above a minimum
value (or quantity); at this time we can only run a very slow SUMIF on all
data or a VLOOKUP over the pivot
5) there are many situations when our pivot table is based on an enormous
database but we only need to study just a part of it at a time - it would
help us a lot to be able to automatically generate a pivot table with the
same design as the main pivot but based on our specific selection from it
6) each pivot table should be able to auto-freeze its header according to
the changes we brought to its page, column, row and data fields

Other issues:

1) the status bar should be able to display simultaneously at least 3
informations chosen by user: SUM, COUNT and NO DUPLICATES (something like
MODE function) would be a good default option
2) we need an enhanced VLOOKUP function that would be able to search in a
database by more that one single criteria (example: NAME + SURNAME) and to
return more than one field (example: AGE, COUNTRY, SALARY, JOB); I know that
multi-criterial search could be accomplished through DGET but this works only
for one record at a time
3) we need to have an option that would automatically re-order the columns
on a second table based on the columns in the master table. This is extremely
useful when you have to append periodically some data to an existing table or
to merge two tables in order to run an agregated pivot. Please note that this
can't be achieved through horizontal sort since the header of a table isn't
usually sorted alphabetically.

Above is what I consider that would help me, my colleagues and a lot of
power users that I know to use Excel more efficiently. Please let me know
what do you think about my suggestions.

Thank you very much for your attention.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi all,

=IF(AND('C:PRODUCTIONCutting Priority Lists[cpleger.xls]Sheet1'!M14=3,'C:PRODUCTIONCutting Priority Lists[cpleger.xls]Sheet1'!C14<>""),'C:PRODUCTIONCutting Priority Lists[cpleger.xls]Sheet1'!C14,"")

The above equation ocassionally pulls through N/A in differing multiple cells but when I open cpleger file the N/A fixes itself. In cpleger file the rows are constantly moving, presume this accounts for why N/A is found in differing ammounts and different cells. However after saving changes to each file when open original file on its own the N/A cells return. Is this being caused by the equation or other mysterious happenings?

----------------------

I have what I call a job database that contains all kinds of job data about various projects. Each job has a number assigned to it that corresponds with the row it's in. I want to have various screens which allows the user to view a list that automatically changes based on what stage each job is in. For example if it is a new job the status would be"job lead" Then an icon, text box or whatever would be on the "job lead screen" When the user schedules a meeting with the client. That icon or text box would disappear from the job lead screen and would be on the "to meet screen" After the user meets with the client, the icon would move on to the "to review" screen and so on.

I was thinking of using another separate worksheet to have the phases or statuses across the columns and when a user completes a phase they press a button that runs a macro that advances the progression. Naturally the order of progression would be contingent on various circumstances and options chosen. The job icon needs to be a macro to return the original job number so that relevant job data for that phase can be rotated into cells on a screen set up to aid the user in carrying out the next step in the series.

So I guess my question is simply, how do I make a macro that creates a button(icon) that has a macro that grabs the currently loaded job # from a cell so that when that button is pressed it will recall that job #The rest of the macro I could probably figure out.

I doubt I'm making any sense to anyone.

I'm just in over my head. Any help would be appreciated.

Up until a few weeks ago I was able to copy a cell(s) and then paste them in
another group of cells, usually a column. Presently when I highlight a cell,
right click copy, highlight a very large group of cells, and then right
click, the paste or paste special are grayed out. When I return to the
original cell I can see that it is no longer highlighted. I have found that
if I try to paste only a small group of cells that it will work. I have also
found that I can highlight a cell and many times it will only stay
highlighted for 10-15 seconds. I am using Excel 2000. Ideas and thoughts
will be appreciated.
Thanks,
Barry

I created a macro to place a border on the left "wall" of a cell and then
move my cursor 3 cells to the right and stop. Instead, each time I use the
macro, the cursor returns to the original cell and I have to move over to
where it should have moved. Is there some way I can make the macro move the
cursor correctly?

Trying to copy and paste a simple formula:

H2+(H2*4.1%)

from original cell into the rest of the cells below it in the column. When I do this, the formula references change as they should, but the value returned is identical to the value returned in the original cell (H2), instead of returning the appropriate calculated values for the rest of the cells in the column. What am I doing wrong? I've done this a million times and this is the first time this has happened!

Hello

I would like to set up a hyperlink on an excel page which mimics clicking
the "back" button on the Web menu in Excel. So for example if the user
clicks on the hyperlink on sheet1 and goes to cell A10 in sheet2, I would
like the user to be able to click on a hyperlink in sheet2 which takes them
"back" to the originating cell in sheet1. There are many many different
hyperlinks in sheet1 which all take the user to the same cell A10 in sheet2,
what we want is the ability to return to the particular originating cell in
sheet1.

We can get this functionality at the moment simply by using the Web menu in
excel and clicking the back button, but we do not want to use this menu any
more.

Its possible we will accept a button on sheet2 which runs a macro which
executes the back command, but only as a totally last resort. If this is
the only possibility then could someone list the code to do this.

All help appreciated.

Andrew

Hi!

I have two cells. One cell (C15) returns RANDom words from an index, generated by this formula:

=IF(A14="generate sentence",INDEX(B5:E5,ROUNDUP(RAND()*1,0),ROUNDUP(RAND()*4,0)),FALSE)

Another cell (C18) shows some words based on the data returned in C15. In fact, C18 shows the same words as in C15, but translated into a different language (from a separate index). I use this formula:

=INDEX(G5:J5,MIN(IF(B5:E5=C15,ROW(B5:E5)-ROW(B5)+1)),MIN(IF(B5:E5=C15,COLUMN(B5:E5)-COLUMN(B5)+1)))

Both of these formulas work fine. The intention of the spreadsheet is to help learn basic sentences in another language. The words in C15 are foreign, and the words in C18 are English. Using conditional formatting, there is an option to hide/display the words in C18, by using white/black text. The condition is based on cell A17, which contains a dropdown menu with two options: "Hide Translation" and "Show Translation."

The problem is, when I select "Show Translation", the content in C15 changes, so actually, I don't see a translation of the sentence I originally intended. The objective is to see foreign sentences and understand them with the translation hidden. When there is a sentence that one doesn't understand, they can select "Show Translation" and see the English translation.

Every time I enter data in the spreadsheet, the randomly generated words in C15 change. Is there any way to avoid that? Thanks in advance!

Clark

I am having difficulty - (maybe just missing an obvious fix)
I need to select a value from another spreadsheet (based on an integer) but
my column with the corresponding integer array is to the right of the array
of information that I want returned.

For example the value on the spread sheet with the formula cell references a
cell (say G3) whose value is 5

The spreadsheet that the information is on looks something like this: ( I
cannot
change the column with the integer values to be to the left)

I need There a way. do 1
am of has 4 this. 2
in help 2 me 12% 5
dire ! b 2 17 13
*** !! ++ ## -- 17

I need to write the formula cell to reference G3 (the 5 - this value is an
input) and have it return "in" or "help" or "me"

Thanks JMB I thougth you gave me the correct solution with
=INDEX(A1:A4,MATCH(G3,F1:F4,0))
but it gave me the index of the value so if the value in G3 was 5
it gave me "***" (I had to add to my original example to include a 5th row
of info to display the example)

Because I am accessing between 2 sheets my formula actually looked more like
=INDEX('info sheet'!A1:A10,MATCH(G3,'info sheet'!F1:F10))

(the G3 cell and the formula cell are on one sheet and the other sheet has
the information I want to pull from - and I CANNOT change that information or
the way it is formatted)

Sorry this is so long but ...
Hope this gets my point across.
Thanks in advance

I have a formula;

=HYPERLINK(INDIRECT($A225&"!"&"$B$4"),INDIRECT($A225&"!"&"$B$4"))

that is not working. It comes up with an error message "Cannot open
specified file".

Originally, the cell contained the following formula, but i'm trying to put
a hyperlink over the top of it.

=INDIRECT($A224&"!"&"$B$4")

This indirect statement basically returns the data in workbook / cell
'5'!B4. I want to be able to hyperlink to that.

I was having a hard time to get rid of the necessity of pressing Ctrl+Shift+Enter after entering a function that return a matrix/array. My customised function will return x rows and y columns. Everytime is different. Since x and y are unknow to me when that time I initialise the function, I have to pre-select the size of the array in the spreadsheet by trial & error.

The problem is particular difficult to me since any subroutine within my customised function that targets to write to the other cells that is not the cell (original) one enters when the function is initialised, would be ignored.

Is there any method (subroutine/function) to expand the results (matrix/array) fully by displaying the only rows up to x and columns up to y immediately after keying "=myfnc(,,,)" without pre-select cells and keying {Ctrl+Shift+Enter}.

thx a lot
dan

Trying to use vlookup or hlookup in a multiple worsheet workbook. I need to
look at a cell and match to a range of cells in a table on another worksheet
then return another cells data. The lookup command is exactly what i need
but doesn't appear to allow the lookup range to be outside of hte current
worksheet. I have been able to work arounf this out by recreating the
original lookup range within the worksheet containing the Xlookup command
with a simple = command within the current worksheet. The redundency of this
and it's complexity with multiple lookup commands within multiple worksheets
(most of which are looking for various data contained in a range in a single
worksheet) is getting considerably less than elegant. Any other functions I
should look at? Any other ideas?

Hi,

Having trouble getting this to work and hope someone can spot what I am doing wrong:

Set
Origin = Worksheets("Level I Screening")
Set Destination = Worksheets("Level II Screening")
    
    x = [{"Include", "Unsure"}]
        
    For n = 1 To UBound(x)
        With Origin.Range("G1:G" & Origin.Range("A" & Rows.Count).End(xlUp).Row)
          Set c = .Find(x(n), , xlValues, xlWhole)
            If Not c Is Nothing Then
                firstAddress = c.Offset(, -6).Address
                Do
'***********************************************************************************************************************
'                  If Destination.Range("A:A").Find(Origin.Range("A" & c.Row).Value, , xlValues,
xlWhole) Is Nothing Then
'***********************************************************************************************************************
                      Destination.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 3) = _
                      Origin.Cells(c.Row, 1).Resize(, 3).Value
'***********************************************************************************************************************
'                    End If
'***********************************************************************************************************************
                  Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Offset(, -6).Address <> firstAddress
            End If
        End With
    Next
Without the commented out code, it runs fine. What I can't get to work is for the macro to check the destination sheet to make sure the record has not be added before. It keeps giving me a Run-time error '91': Object variable or with block variable not set. The problem seems to be related to the c in the Find approach. It returns it as nothing and I don't know why this is happening.

Any thoughts.

abousetta

I have the following formula:

=IF(ISERROR(LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12)), 0, LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12))

This formula looks at a particular cell, in this case cell L22, and takes a value that is shown in FT & fractional inches and converts it to the decimal ft equivalent. Ex: 10' 8 3/8" will return a value of 10.6979. I can then use this value for all my calculations.

The problem is that some people have a habit of typing the exact same value above but in the following format: 10'-8 3/8". Notice the "-" shown to show seperation from ft and inches. Unfortunately, if this is typed in this format, the formula above will see this as a minus calculation and the value returned is 9.3021 which obviously creates disasterous calculation problems.

Is there a way to modify the original formula to ignore the "-" character OR if the "-" character is typed in the cell an "ERROR" value will be returned instead?

Thanks in advance.

CheddarThief

I'm in the macros dark ages because I can't get any of my VBE Macros to work. I guess I could use a serious tutorial is anyone has pointers.

MEantime, I open old macro sheets that were constructed by placing programs in a column of cells in a macro worksheet that I learned in Excel 2.x

The syntax is slight different and the VBE style doesn't use the RETURN() funtion to end the macro anymore, I believe, but otherwise this macro that I'm having trouble with will, I hope, be recognizable by the assembled wisdom.

I have a list of records that I will ultimately apply a pivot table command to thanks to help I received here. But first, the data that came in without delimiters had text of varying and unpredictable length and the fixed width import turned it into several fields based on no deciherable algorithm I can find. So I am manually reformatting these entries and filling down. There are between 200 and 600 consecutive entries that are identical in the text fields. At first I thought that each ran for the same number of entries so I wrote a macro that just jumped 607 lines, but for varying reasons, some of the groupings do not have the full 607 entries.

So I'm trying to restructure the macro so if I'm at the first occurence of a particular text value I can have the macro run through succeeding rows in that column until it finds a value that is not equal to the original and then jump there. By using a split window and running the macro on the bottom window this gives me the opportunity to easily select my fill down range instead of scrolling and trying to catch the change in text as it flies by my eye.

My proposed code looks like this:

=FOR("counter",1,607,1)
=IF(SELECTION()="R["counter"]C",NEXT(),SELECT("R["counter"]C")
=RETURN()

My problem is that I can't seem to get the quote marks right to call the variable and EXCEL doesn't like the formula I've written in line 2 of the code (unoriginally I'm using "counter" which is the sample variable excel uses in tutorials on FOR() NEXT() loops.) My notion is to start "counter" at 1 so the first time through the IF function compares the SELECTION to R[1]C. If they are equal, the true value is NEXT(). Counter is incremented to 2, so the IF function compares the SELECTION to R[2]C and so forth, until it encounters a different value. Then the False value is to SELECT that cell using the counter variable. Of course this approach would actually select not the last same value, but the 1st of a different value in that column. This could be accounted for by making the selection R["counter"-1]C, but the point is that it will put the lower screen view right in the vicinity of where I need to be and then I can take it quickly from there.

It may be that there is an easier or more logical way to do this by loading the value in the current selection and using a FIND.COLUMN that looks for the not equal, <> value.

So, I'm certainly open to other ways up the mountain. For now I'm going to be pasting them into an old previously named macro in an old macro sheet because I just cannot get the VBE macro thing to work for me. I would be glad to read tutorial on that but also trying to forge forward on this project so I can finish it for the end of the day.

Brian

Best title I could think of.

Ok so I have 6 sheets Mon - Fri and a week sheet, currently I have the sheets being sorted and pulled to week which is what i want however when the sort is complete data from lets say Tuesday is no longer in the column section of tuesday. Yet under monday in fact if any of the values are not found in all days of the week they are currently being placed under the Monday column instead of sorting to their respective columns.

There are multiple ways to fix that but what I need is for id number last and first names to be under a2b2c2 respectively it auto sort the days of the week and peoples names to their respective rows.
However I need the people who are missing from the list on a given day of the week to only show a blank cell for that day and if they are missing from 2 days they would be blank on the other day as well, yet show up on the day column they were on the list from that day of the week sheet.

It is sort of like an attendence sheet however the list for each day of the week is generated at random so no day or week will be the same. just when i receive the data it will be sorted with said vba function.

I found parts of this source online that worked a bit better and faster in sorting than my original file and was able to modify some of the code to get partially what I needed. This is the main sort code for pulling from the data sheets. Do not mind the offsets they should be (0,1) respectively. I have tried to adjust them but it does not work. And unfortunately MSDN is not giving me a viable solution I can think of to fix the issue. Hense why I am asking for help after wrapping my head around this issue for 3 days. My luck I probably just need to add another variable and have it point to the column needed to paste the data.

Public Sub WeekSort()


Dim c As Range, cfind As Range, x, cfind1 As Range
On Error Resume Next
With Worksheets("Monday")
.UsedRange.Copy Worksheets("Week").Range("a2")

For Each c In Range(.Range("a3"), .Range("a3").End(xlDown))
x = c.Value

    With Worksheets("Tuesday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 2).PasteSpecial
        End With 'W
     End With  'T
     With Worksheets("Wednesday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 5).PasteSpecial
        End With 'W
     End With  'Wed
     With Worksheets("Thursday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 8).PasteSpecial
        End With 'Week
     End With  'Th
     With Worksheets("Friday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 11).PasteSpecial
        End With 'Week
     End With  'F
line1:
 Next
 End With
    Application.CutCopyMode = False
End Sub
basically 
ID L F    M       T         W     Th     F
1   xx    x        x        x       x      x
2   xx             x                x      x
3   xx    x        x        x
x under M-F are the days they appear on list blanks are days they do not

any advice in the right direction or even some reference sources I can lookup to solve this issue would be greatly appreciated.

thanks in advance to anyone that can help me out.

*edit could have sworn I returned to programming section before not function section sorry mods*

Here is yet another workbook I've been working on where I wrote the macros and they worked and now they don't. More specifically, when I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.

If anyone could take a look, I've attached the workbook in all it's infuriating glory. Go to the count sheet, click the Add to Inventory button, and see what you get. I'm not great with VBA, but as you can see, I've been messing with it a good bit. Hopefully someone can see where I'm going wrong. Also, if anyone has any ideas to make to code function better, I'd love to hear it!

---Ok, so I've tried to attach the workbook about ten times now and I'm thinking it might just be cursed! Here is the macros that I have written. Hopefully someone can at least see something from this!
Sub AddInv()
'
'AddInvMacro
' Macro recorded 9/30/2009 by Jason Feilen
'
Dim inv As Worksheet
Dim mas As Worksheet
Dim rec As Integer
Dim item As String
Dim r As Long
Dim mr As Long

Set inv = Worksheets("Invoices")
Set mas = Worksheets("MasterInventory")
r = 2
mr = 2

'Find First Row Not Added to the Inventory
Do While inv.Cells(r, 8) = "Yes"
    r = r + 1
Loop

    'Add row to inventory
    Do While inv.Cells(r, 8) = "No"
        rec = inv.Cells(r, 6)
        item = inv.Cells(r, 4)
    
        'Find  item in master inventory
        Do While mas.Cells(mr, 2) <> inv.Cells(r, 4)
            mr = mr + 1
        Loop
    
        'Update On Hand Column
        mas.Cells(mr, 11).Value = mas.Cells(mr, 11).Value + inv.Cells(r, 6).Value
    
        'Update Added to Inventory Column
        inv.Cells(r, 8).Value = "Yes"
        
        mr = 2
        r = r + 1
    Loop

'

End Sub
Sub countInventory()

Dim r As Long
Dim mr As Long
Dim count As Worksheet
Dim master As Worksheet

r = 2
mr = 0
Set count = Worksheets("Count")
Set master = Worksheets("MasterInventory")

'Check Master Inventory and auto archive if neccessary
Do While r < 1151
    If master.Cells(r, 11) > 0 Then
        mr = mr + 1
        'MsgBox ("r = " & r & " mr = " & mr)
    End If
    r = r + 1
Loop

If mr > 0 Then
    ArchiveInventory
End If

'Copy and paste date
    count.Cells(1, 10).Copy
    master.Activate
    master.Cells(1, 13).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

'Extract data from count and input into Master Inventory
r = 2
mr = 2
Dim ItNo As Integer
Do While count.Cells(r, 1) <> ""
    ItNo = count.Cells(r, 1).Value
    Do While master.Cells(mr, 1) <> ItNo
        master.Cells(mr, 11) = count.Cells(r, 6)
        mr = mr + 1
    Loop
    r = r + 1
Loop
    
End Sub
Sub countSales()
'countSalesMacro

Dim r As Long
Dim mr As Long
Dim count As Worksheet
Dim sales As Worksheet

r = 2
mr = 3
Set count = Worksheets("Count")
Set sales = Worksheets("Sales")

'Check sales sheet and auto archive if neccessary
If sales.Range("D3") <> "" Then
    ArchiveSales
End If

'Find Last Row in Sales
Do While sales.Cells(mr, 4) > 0
    mr = mr + 1
Loop

'Copy and paste date
    count.Cells(1, 10).Copy
    sales.Activate
    sales.Cells(1, 6).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

'Check first row of count
If count.Cells(r, 1) = "" Then
    MsgBox ("There is no record in the first row of the count sheet.  If records exist, re-sort the sheet.")
    count.Activate
    Exit Sub
End If

'Find differences in inventory count and add information to Sales
Do While count.Cells(r, 1) <> ""
    If count.Cells(r, 8) <> "--" Then
        sales.Cells(mr, 4) = count.Cells(r, 1)
        sales.Cells(mr, 5) = count.Cells(r, 8)
        mr = mr + 1
        r = r + 1
    Else
        r = r + 1
    End If
Loop

'

End Sub
Sub ParAddedSort()

'After adding new pars, sorts the count sheet in order
Range("A1:H1150").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Sub ArchiveSales()

Dim r As Long
r = 3
Sheets("Sales").Activate
'Move historical date over to insert new data
    Columns("J:L").Copy
    Columns("J:L").Insert Shift:=xlToRight
    Application.CutCopyMode = False
'Copy and paste date
    Sheets("Sales").Cells(1, 6).Copy
    Sheets("Sales").Cells(1, 15).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'Enter headers into historical data
    Sheets("Sales").Cells(1, 13) = Sheets("Sales").Cells(1, 4)
    Sheets("Sales").Cells(2, 13) = Sheets("Sales").Cells(2, 4)
    Sheets("Sales").Cells(2, 14) = Sheets("Sales").Cells(2, 5)
    Sheets("Sales").Cells(2, 15) = Sheets("Sales").Cells(2, 6)
    
'Grab sales total
    tot = Sheets("Sales").Cells(5, 1)
    
'Enter data into history and clear data from report
    Do While Sheets("Sales").Cells(r, 4) > 0
        Sheets("Sales").Cells(r, 13) = Sheets("Sales").Cells(r, 4)
        Sheets("Sales").Cells(r, 14) = Sheets("Sales").Cells(r, 5)
        Sheets("Sales").Cells(r, 15) = Sheets("Sales").Cells(r, 6)
        Sheets("Sales").Cells(r, 4).ClearContents
        Sheets("Sales").Cells(r, 5).ClearContents
        r = r + 1
    Loop
    
'Enter total sales into history and clear data from report
    r = r + 1
    Sheets("Sales").Cells(r, 13) = Sheets("Sales").Cells(4, 1)
    Sheets("Sales").Cells(r, 15) = tot
    
'Clear Date
    Sheets("Sales").Cells(1, 6).ClearContents
End Sub
Sub ArchiveInventory()

Dim cnt As Long
Dim r As Long
r = 2
cnt = 3
Sheets("MasterInventory").Activate
'Move historical date over to insert new data
    Columns("Q:R").Copy
    Columns("Q:R").Insert Shift:=xlToRight
    Application.CutCopyMode = False
'Copy and paste date
    Sheets("MasterInventory").Cells(1, 13).Copy
    Sheets("MasterInventory").Cells(1, 19).Select
    'ActiveSheet.Paste
    Application.CutCopyMode = False
'Enter headers into historical data
    Sheets("MasterInventory").Cells(2, 19) = Sheets("MasterInventory").Cells(1, 1)
    Sheets("MasterInventory").Cells(2, 20) = Sheets("MasterInventory").Cells(1, 11)
    
'Enter data into history and don't clear data from report
    Do While r < 1151
        If Sheets("MasterInventory").Cells(r, 11) > 0 Then
            Sheets("MasterInventory").Cells(cnt, 19) = Sheets("MasterInventory").Cells(r, 1)
            Sheets("MasterInventory").Cells(cnt, 20) = Sheets("MasterInventory").Cells(r, 11)
            'Sheets("MasterInventory").Cells(r, 11).ClearContents
            cnt = cnt + 1
        End If
        r = r + 1
    Loop
    
'Clear Date
    Sheets("MasterInventory").Cells(1, 13).ClearContents
End Sub


Hi,

Thank you for taking the time to read this post. I am trying to build a VBA program to identify the appropriate project on my master list and update that information based on a smaller file I place on my share drive for fellow employees. This is my first post so if I can structure this better or post in a different area please tell me.

The specifics:

I am trying to pull information from my CM Update sheet to another sheet named the PSR. The PSR is the master sheet and much larger than the CM update sheet (many more columns, much more complicated--> Therefore a "merge" would not work). I have been able to run a simple Vlookup which, if the project numbers are the same for the CM Update sheet and the PSR, to update the PSR accordingly. However, there are two problems with this.

1) Sometimes the CM Update sheet is left blank due to accidental deletion or no updating. If it is left blank, and I have information stored in the PSR, I want the Macro to skip that cell in the PSR and leave the pre-existing information which was originally in the PSR. i.e., I want to retain outdated info rather than wipe out the information completely.
2) Sometimes I add new projects before the CM Update file gets back to me. I want that information to remain untouched by the Macro (i.e., if the Macro does not find a match between the project ID's in the PSR and the CM Update file, I want the PSR file data to remain rather than have the data be wiped out and have a 0 return due to no corresponding information in the CM Update file).

I have incorporated this into a simple point-and-click Macro, for which each column of information I pull has a vlookup function that looks like this:

=IF(ISNA(VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE)), 0, VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE))

I know the above function is not good enough. I have tried using IS(NOTBLANK as well, but this attempt I made misses the fundamental point. I need the cell in question in the PSR to remain untouched by the function and remain as it was before the code was executed should the cell be blank in the CM Update file.

Can anyone help me with this? I think this requires VBA code, and I have never done this before. Does anyone have any suggestions, ideas, ect? Should I post the entire macro I had been using in VBA code? I have been teaching myself the basics of VBA programming in Excel, but it is slow going. Any advice would be appreciated.

Thank you!