Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›3 »

HootTheOwl: May 13, 2012; Hootin and shootin

Can index and match be conditional?

Like lets say I have some arbitray number of rows and two columns A and B. Can I then add a third column and using index and match have column C equal to some concatination of every row where B is equal to the A on the specific row I'm working on?

code:

[a] [b]
[x] [a]
[y] [z]

becomes

code:

[a] [b] [x]
[x] [a] []
[y] [z] []

# ¿ Mar 3, 2022 04:12

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 19:50

HootTheOwl: May 13, 2012; Hootin and shootin

Lib and let die posted:

And then when I re-open the file to work with it, I have to convert it all to text again else it saves dates in whatever hosed up format it's decided is best. I suppose I could macrofy it, but then I have to have a workbook with my macros in it open when I'm working with the files too...It's absolutely infuriating. I'm close to giving up on Excel entirely and seeing if Google Sheets handles them any better - I've tried about everything I can think of with Excel (hence turning to my fellow goons for advice)

Turn off automformating?
Past values only?

# ¿ Mar 3, 2022 17:31

HootTheOwl: May 13, 2012; Hootin and shootin

Sounds like you want CountIfs

# ¿ Mar 24, 2022 16:42

HootTheOwl: May 13, 2012; Hootin and shootin

Falconier111 posted:

First of all: extremely inexperienced and self-taught, so keep that in mind.

I�m doing a screenshot let�s play right now, and I get the text for my updates by copying it over from the original script in the game files, then running it through a couple VBA scripts that are just big find-replace functions to bring it more in line with SA BBcode. After the first script, I get text looking like this:

Each line either starts with double quotes for narration or a letter or two to indicate the speaker. I need some code that will look at each section enclosed by quotes, check if it has the same speaker as the previous line, and if it does, append the two, ideally removing intermediary speaker markings and quotes in the process. I�m hoping it ends up looking like this:

At this point I usually make necessary manual edits before running it through the last script and publishing it. I�ve been removing the unnecessary bits by hand, but I have mobility issues that are getting in the way; text to speech works miracles for prose and I have little issue with standard editing (I got through my last LP just fine), but the software doesn�t accommodate coding and having to edit these marks out over and over for thousands of words/write code by hand to experiment with it is killing me. The rest of the process is under control, it�s just this one area is a sticking point. Does anyone know how to go about researching or implementing a solution?

Use a code tag
E I can't tell on my phone what you're looking for

HootTheOwl fucked around with this message at 01:39 on Mar 25, 2022

# ¿ Mar 25, 2022 01:27

HootTheOwl: May 13, 2012; Hootin and shootin

Ok I'm at my computer and I think you overthought it:

First, format your input into an array of strings so it's on per line. It looks like you have this already but in case you don't, use the Split function to achieve this.
Then define an output array of strings
Then, run a For Each loop over the split input array, with each line being an element in the group.
Inside this loop:
If The output array is empty, add the current line to the output, except the last character (which should be a ")
Next

If the output array wasn't empty, then check the first character in the Line. If the line begins with a " then concatinate it onto the last element in the output array (remember to remove the leading and trailing " characters)

Else, concatenate a " character onto the last element in the output array. And then Add the current line as a new element into the output array, removing it's trailing "

Next, End For

My VBA is pretty rusty but it should look something like:

code:

dim Input() as string = Split(whateveryourinputis, "\n")
dim Output() as string = {} (I forget how to initialize arrays in VBA, but just make sure it's not null)

For Each Line in Input
    if Output.Count = 0 Then
        Output.Add(Substr(Line, 0, Len(Line)-1)
    ElseIf Len(Line) > 0 And Left(Line, 1) = "" " Then
        Output(Output.Count-1) = Output(Output.Count-1) + Substr(Line, 1, Len(Line)-2)
    Else
        Output.Add(Substr(Line, 0, Len(LIne)-1))
    End If
Next

Output(Output.Count-1) = Output(Output.Count-1)+"""

I don't know how to put quotes in BBC code blocks, But it should be "\"" (quote, escaped quote, quote) every time you need to check for or add one.

HootTheOwl fucked around with this message at 02:43 on Mar 25, 2022

# ¿ Mar 25, 2022 02:31

HootTheOwl: May 13, 2012; Hootin and shootin

My wife says you want a pivot table.

# ¿ Jul 21, 2022 00:50

HootTheOwl: May 13, 2012; Hootin and shootin

Lib and let die posted:

I don't understand lambdas in python, and I don't understand them in excel. why create a function that's just getting thrown away???????

Lambdas let you define your own function ad hoc and they're amazing and cool and they're my friend.

# ¿ Jul 21, 2022 13:44

HootTheOwl: May 13, 2012; Hootin and shootin

Looten Plunder posted:

I'm creating an agent performance dashboard at work. I run a report and paste the data in excel then have a dashboard that summarises it by employee. I have a bunch of rules that filters based on the employee name that's input into cell B3. I currently have the following macro for filtering a pivot table based on the employee name in B3 to show the assessments that were completed for them
code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B3:B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("SummarySheet").PivotTables("EvalSummary")
    Set xPFile = xPTable.PivotFields("Agent Name")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub
Currently, if I don't have an employee name in cell B3 it displays every assessment. How do I alter that macro to filter to none unless an employee name has been input?

Doesn't exit sub exit the current block and proceed?
So you're only ending your if block. I think you want return there instead.

# ¿ Jul 21, 2022 13:50

HootTheOwl: May 13, 2012; Hootin and shootin

Nested ifs and concatenation

Sorry, I thinki misunderstood what you asked for:
I would try for a macro

# ¿ Jul 23, 2022 21:24

HootTheOwl: May 13, 2012; Hootin and shootin

Looten Plunder posted:

I'm trying to create a macro to copy a worksheet, create a new document with a filename based on the value in A3, paste the worksheet with just the values, columnwidths and cell formatting.
I can get get the macro to work up to the pasting part and then I get an Error 400, which in debug mode pops as a "Run time error 1004: Application defined or object defined error"

This is what I've cobbled together:
code:
Sub MySheetCopy()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim Path As String
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    Path = "C:\Users\Redacted\Desktop\DesktopDocs\Test Folder"
    myNewFileName = Path & Range("A2") & ".xlsx"
   

'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteColumnWidths
    ActiveSheet.PasteSpecial Paste:=xlPasteFormatsxlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub
In debug mode I can run it successfully all the way through to the "Range("A1).Select line.
Can anyone help me out?

First, your filepath is wrong: you're missing the slash between test folder and cell A3 (A2?)
Second I don't believe you can call .activate a workbook that isn't open
E: third, save as is going to make a complete copy of your work book, no? There's got to be a better way to do that.

HootTheOwl fucked around with this message at 04:31 on Jul 25, 2022

# ¿ Jul 25, 2022 04:27

HootTheOwl: May 13, 2012; Hootin and shootin

Try nesting ISBLANK into your filter condition
Or, more easily: <> ""
(Not equal to emptyquote)

HootTheOwl fucked around with this message at 23:50 on Jul 26, 2022

# ¿ Jul 26, 2022 23:45

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

Wouldn't that result in the empty cells being excluded from the FILTER, and thus not getting pasted over to the result area? Or am I reading it wrong?

Right. Sorry I meant: In your filter you basically have two outputs: The desired one and then a special branch if the value is blank.

code:

if isBlank
  return ""
else
  return what you have now

Are you copying numbers? Is your destination cells formatted to put blank data as numbers which are forcing one signifignt digit?

HootTheOwl fucked around with this message at 01:40 on Jul 27, 2022

# ¿ Jul 27, 2022 01:36

HootTheOwl: May 13, 2012; Hootin and shootin

A lambda :swoon:

But also 0 is just what index/match returns when the cell is blank, so you might need to just manually assign all 0's as blanks. Of course this won't work if zero is an acceptable value.

# ¿ Jul 27, 2022 02:20

HootTheOwl: May 13, 2012; Hootin and shootin

And Autofit is enabled?

# ¿ Jul 28, 2022 13:41

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

I'm not sure how to enable it, and Google isn't helping much. I know that when I click the autofit button, it seems to work, but I don't see an option to turn it off or on.

Try this?

Microsoft support says posted:

On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Row Height. Tip: To quickly autofit all rows on the worksheet, click the Select All button, and then double-click the boundary below one of the row headings.

# ¿ Jul 28, 2022 17:10

HootTheOwl: May 13, 2012; Hootin and shootin

If you already have a macro....
solve the whole workbook with a macro :unsmigghh:

# ¿ Jul 28, 2022 19:52

HootTheOwl: May 13, 2012; Hootin and shootin

Sounds like you want to perform a VLOOKUP on column B using the values on column A
something like

code:

=VLOOKUP(Ax,B:B,0,TRUE)

Where X is the row of the value in column A you're looking up and 0 is the column in sheet B you want to display (I can't remember if they're 0 or 1 indexed). True for exact match, false for approx match.

# ¿ Jul 28, 2022 21:06

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

No.

Coward.

# ¿ Jul 28, 2022 21:07

HootTheOwl: May 13, 2012; Hootin and shootin

Looten Plunder posted:

I work at a call centre where Managers have to assess their agents and write comments to justify how they scored the agent. I'm trying to create a pivot table that calculates the percentage of times a manager made a selection but didn't write a comment.

In other words, I'm trying to create a pivot table that calculates the percentage of blanks in the comment fields. I can get it to display the count of blank cells by dragging the "comments" field under the "ManagerName" field in the Rows section of the pivot table and then filtering to only "blank" and collapsing the field, but I can't get this number to display as a % of all comments (or the % difference from a field where there are no blanks). Can anyone help?

Can you add an extra column with a formula that uses COUNTBLANK?

# ¿ Sep 1, 2022 14:47

HootTheOwl: May 13, 2012; Hootin and shootin

My wife is working on a big sheet that should be a database.
Does excel have a way to index 11,000 rows so it doesn't take a hundred years when you try nd sort it?

# ¿ Sep 13, 2022 23:17

HootTheOwl: May 13, 2012; Hootin and shootin

fosborb posted:

11,000 rows shouldn't take any time at all to sort. if it does, try turning off calculations and manually refresh after sorting

What if the cell is create via a forumula?

# ¿ Sep 14, 2022 00:14

HootTheOwl: May 13, 2012; Hootin and shootin

AG3 posted:

I'm using a VBA code I googled to alter the enter key behavior for my sheet, to make it move right by default instead of down. The problem is, this code affects Excel permanently even in other files and persists through Excel restarts, until I manually run the code again to toggle it off. It looks like this:
code:
Sub EnterKeyBehavior()

If Application.MoveAfterReturnDirection = xlToRight Then
    Application.MoveAfterReturnDirection = xlDown
Else: Application.MoveAfterReturnDirection = xlToRight

End If
End Sub
Is there an easy way to make it turn itself on when the file is opened and turn itself off when it's closed?

I'm looking it up, but you're applying your change to the application object (excel) when it sounds like you only want to apply it to she worksheet, which I don't think you can do. Basically you're changing a setting in excel which is why it applies to your other sheets. It's not actually a change to the workbook, but to excel itself.
I also don't think you can use macros to tap into .net events (Ie, When the application closes, do something)
But I also think you can use windows built in language hotkeys?

e: Yup, this can only be done to applications: https://learn.microsoft.com/en-us/office/vba/api/excel.application.moveafterreturndirection
e: Oh! You can have macros run at prescribed events as documented here oh and there's event handlers too! Ok, give me some time and I think I can do this to you. If you want to do it yourself read this: https://www.exceltip.com/events-in-vba/workbook-events-using-vba-in-microsoft-excel.html Looks like you can just slap these into your workbook.
I wonder if this means you can have a macro which then just updates these handlers for you too?

EEE: Use the activate and deactivate events, imo. This will change the setting every time you enter the sheet and again when you leave it

HootTheOwl fucked around with this message at 13:41 on Oct 13, 2022

# ¿ Oct 13, 2022 13:25

HootTheOwl: May 13, 2012; Hootin and shootin

Chickpea Roar posted:

What do you mean? You shouldn't have to do any copy/pasting, unless I've misunderstood something about the problem.

When they said copy past the values, they meant to strip out the formulas for sorting.

E: Sorry I hadn't had my morning coffee yet and posted this because it took me a minute to understand the connection between what DRINK ME was saying in their two posts. They already said this.

# ¿ Oct 25, 2022 12:40

HootTheOwl: May 13, 2012; Hootin and shootin

it dont matter posted:

Yes that worked, thank you.

What if I also want B to turn red if it's empty? Would that be something with ISBLANK ?

Have it be red by default and only turn green when it's yes

# ¿ Oct 25, 2022 15:20

HootTheOwl: May 13, 2012; Hootin and shootin

Jack the Lad posted:

Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range?

For example I want to put BobsFaveFoods in A1 and then use =SUMPRODUCT(COUNTIF(B2:B10,A1)) instead of =SUMPRODUCT(COUNTIF(B2:B10,BobsFaveFoods)) so that I can have this across a bunch of rows/tabs and change which named range I'm referencing on the fly if possible.

Really stumped on this but probably just being stupid.

this sounds like a lambda.

# ¿ Nov 8, 2022 01:37

HootTheOwl: May 13, 2012; Hootin and shootin

Lib and let die posted:

Here's an incredibly dumb question:

Can I resize all my columns by just the length of the column header? I seem to recall doing that somehow in the past but I can't figure it out now.

grumble grumble loving salesforce export files

A macro which looks at the length of each column, counts the number of characters in the column and makes a guess based on the average kerning.

# ¿ Nov 8, 2022 20:26

HootTheOwl: May 13, 2012; Hootin and shootin

The vba code should just be one line, every column has a column width property and you can just set that to shut number, and if you want them all equal then you just feed column A's width as the argument

# ¿ Nov 9, 2022 05:38

HootTheOwl: May 13, 2012; Hootin and shootin

Kibayasu posted:

Assuming I put this in the right place it gives me a Run time 1004 error whenever I enter or delete anything in B1.

To show what I tried before I got it from here https://www.extendoffice.com/documents/excel/5326-excel-pivot-table-filter-link-to-cell.html. Specifically:
code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("Depots")
    Set xPFile = xPTable.PivotFields("Depot Filter")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub
I've replaced the names of tables and sheets with my names. That code just seems to not do anything? It didn't show an error so I guess its running properly just not doing what I think it should? There's even a GIF in the comments which shows it doing exactly what I need.

It doesn't show an error because you have told it to just keep going. On Error next means to display no error and attempt the next line.
I think your problem is that you have no error checking on your set.
What if xPTable couldn't find depots?
What if xPFile couldn't find the pivot fields?
What if the target had no test?
It's combersome but refactor your code to only advance if the value is found. This will make debugging easier.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String

    'On Error Resume Next
    On Error Goto Handling

    If Intersect(Target, Range("B1")) IsNot Nothing Then
        Application.ScreenUpdating = False
        Set xPTable = Worksheets("Sheet1").PivotTables("Depots")
        if xPTable IsNot Nothing Then
            Set xPFile = xPTable.PivotFields("Depot Filter")
            if xPFile IsNot Nothing then
                xStr = Target.Text
                xPFile.ClearAllFilters
                xPFile.CurrentPage = xStr
            end if
        end if
    end if

    Done:
        Exit Sub
    Handling:
        Application.ScreenUpdating = True
End Sub

So split the code up, and then comment out your error handling behavior
or better, use therror handling I added

HootTheOwl fucked around with this message at 18:44 on Nov 10, 2022

# ¿ Nov 10, 2022 18:41

HootTheOwl: May 13, 2012; Hootin and shootin

After you get that to compile and run (I typed it free hand, so there could by typos and syntax errors that VS would find), add a messagebox to the handling section so you can track which line failed. Just have a string defined as 'nothing' and then after every action you're worried about change the value to 'did X, now trying Y' and then you'll have it.

If it's actually truly not erroring, then you're probably not getting the correct values assigned.

# ¿ Nov 10, 2022 18:51

HootTheOwl: May 13, 2012; Hootin and shootin

Kibayasu posted:

I really appreciate the help! Though if I wasn't clear I have 0 knowledge about VBA and if I ever need it just hope that someone on the internet has posted about precisely what I need and that just copying/pasting the code will work. I wouldn't have the first idea on how to do what you've suggested here, which makes it even more awkward asking for VBA help. That's good to know about "On Error Resume Next" though, I never would have thought there would be code to not tell you the code is wrong, the only thing I could do 10 minutes ago was look at it and go "Yup, that sure is some code alright."

Anyways with your changes I'm getting a "Compile error: Syntax error" now so I guess that's progress in that I know something is actually happening. But, see above, I don't know what would be wrong. These lines have been highlighted in red, which I am assuming means that is where the issues are (which might not be a good assumption!).
code:
If Not Intersect(Target, Range("B1")) Is Nothing Then
code:
if Not xPTable Is Nothing Then
code:
if Not xPFile Is Nothing then
If this is getting beyond what free stuff on the internet should provide I can live with the basic PivotTable functions, this is basically for people who don't even know what the filter icon in a table looks like.

Happy to help.
Ok, so a syntax error means a typo. Code is very picky.
Ok, it looks like IsNot isn't supported?
which means your statements need to change. I edited the codeblocks for you. (basically you have to use NOT (variable Is Nothing), instead of (variable ISNOT nothing))

# ¿ Nov 10, 2022 20:12

HootTheOwl: May 13, 2012; Hootin and shootin

Add this to the error block, you need to get your code to signal the error

code:

If Err.Number <> 0 Then
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Chr(13) & Err.Description
    MsgBox Msg, vbMsgBoxHelpButton, "Error", Err.Helpfile, Err.HelpContext
End If

I copy and pasted that from Microsoft so it should work as is. Paste that after the application. screen handling line and before the end sub line

# ¿ Nov 18, 2022 03:16

HootTheOwl: May 13, 2012; Hootin and shootin

You're issue is this line:

code:

Set xPFile = xPTable.PivotFields("Depot Filter")

According to Microsoft, here, https://learn.microsoft.com/en-us/office/vba/api/excel.pivottable.pivotfields it doesn't say this function can return an error, so I actually don't think you've assigned your pivot table correctly. You're sanitizing the names so the issue might be there.
Can you post the whole macro? I might have to actually write this myself

# ¿ Nov 19, 2022 04:55

HootTheOwl: May 13, 2012; Hootin and shootin

axolotl farmer posted:

Is there really no straightforward way to change default ctrl-v paste? I want to paste unformatted text into excel from web pages, just the numbers without color and font.

I know about the menu you get with a right click, but I just want the ctrl-v to paste unformatted text by default.

Can't you hold shift?
Also there's a section called paste options where you can change the default
E: https://www.causal.app/excel-shortcuts/shortcut-to-paste-values-in-excel

# ¿ Dec 19, 2022 13:27

HootTheOwl: May 13, 2012; Hootin and shootin

Busy Bee posted:

I'm currently grading some of my trading cards and keeping track of it in a spreadsheet.

I have 7 columns - the 1st column is the name of the card, the 2nd column is the estimated grade of the card ranging from 1 to 10 with 10 being completely mint, and the remaining 5 columns are categorized from Ungraded to 7, 8, 9, and 10 with an average sold price for those respective grades. Essentially, any grade equal to 6 or below will fall under the "Ungraded" column.

I want to create a new column where it takes the estimated grade from the 2nd column, finds the average price in column 3, 4, 5, 6, and 7 - and then outputs that number in the new column.

What would be the best way of going about this?

For example, from the image I attached, the new column will have the following outputs:

Row 1 - $355
Row 2 - $380
Row 3 - $280
Row 4 - $235

(I accidentally did not include the "10" grade in the screenshot I shared and now I can't edit it but the formula would still be the same)

seems simple enough to just use a switch (also, oh gently caress yes excel has built in switch case?)
Put this into your new 8th column

code:

=switch($A1,7,$C1,8,$D1,9,$E1,10,$F1,$B1)

But replace:
A with the estimated grade column,
C with the 7 column
D with the 8
F with 9
G with 10
B with the Ungraded

# ¿ Jan 5, 2023 14:11

HootTheOwl: May 13, 2012; Hootin and shootin

Busy Bee posted:

That worked! Thank you!

Now I have another question. Let's say I want another column now to show the estimated price for the one grade above the estimated grade I chose. However, if the estimated grade I chose is any one between 1 to 5, the output will always be the ungraded price while if I chose an estimated grade of 6, 7, 8, or 9 - it will show the average price for the grades 7, 8, 9, and 10. I have no estimated 10 grades in my Google Sheet nor do I intend to have it.

I want this new column to show this data in case I want to take the risk of paying to get the card graded in the chance that I will receive a higher grade.

How would I go about this?

The switch function looks for a match and then returns the value when it does, with the last value being no match.
So, simply take all the grades in the function, and subtract 1.
Then add the 10 pair again (because otherwise it will go to ungraded)

code:

=switch($A1,7,$C1,8,$D1,9,$E1,10,$F1,$B1)

becomes

code:

=switch($A1,6,$C1,7,$D1,8,$E1,9,$F1,10,$F1,$B1)

# ¿ Jan 5, 2023 15:09

HootTheOwl: May 13, 2012; Hootin and shootin

tuyop posted:

Any way to add commas or spaces to a large number in a string that is the result of a calculation?

The case here is that I'm making a calculator for a scale model of the solar system for camp counselors to use. If they use a 150m parking lot, they're making a 1:148,000,000,000 scale model but Excel writes it 1:148000000000. Is there any way to add the comma separators in?

My spreadsheet is real simple

B3 there has =CONCAT("1:",B1/B2) as the formula.

Something like this:
=CONCAT("1:",TEXT(B1/B2,"#,###"))

You need to format the division output, and then concat your 1: onto it.

# ¿ Jan 11, 2023 20:37

HootTheOwl: May 13, 2012; Hootin and shootin

You should be able to use the row number or column letter as an indexer on the row/column collection object property that a workbook has to access the specific row/column and then simply assign it to the row/column in the other workbook using the assignment operator. Or a for loop to iterate through the cells of the row column to copy each one individually

In this example, everything should be self explanitory.
sourceSheet is just the name of the sheet you're copying from.
destinationSheet where you're copying to.
destinationRow is the place you want your copied row to go. If you want to put it in the same place as the original sheet, change it to sourceRow
sourceRow is where you're copying from. as long as the user types a number in the cell you're looking at it can be as simple as:

code:

If Target.Row = 1 And Target.Column = 1 Then
Dim sourceRow As Integer
sourceRow = Target(1, 1).Value

I used the worksheet_selectionChange event to check if they typed in the special box (A1) and if they did, defined my source as equal to what they typed.

Iterating over that row to copy it will look like this:

code:

For Each littleCell In Worksheets(sourceSheet).Rows(sourceRow).Cells
    Worksheets(destinationSheet).Rows(destinationRow).Cells(littleCell.Column).Value = littleCell.Value
next

HootTheOwl fucked around with this message at 14:40 on Jan 18, 2023

# ¿ Jan 18, 2023 13:17

HootTheOwl: May 13, 2012; Hootin and shootin

Chat GPT can read the MSDN just as good as anyone.
Except me, of course. I'm still worth the big bucks.

# ¿ Jan 25, 2023 00:07

HootTheOwl: May 13, 2012; Hootin and shootin

KOTEX GOD OF BLOOD posted:

I'm kind of surprised to be struggling with this as it seems like a fairly straightforward thing, but none of the tutorials online seem to have an answer.

I have a list of expenses like this:
code:
Something Awful LLC	$420.69 
Something Awful LLC	$66.66 
Something Awful LLC	$69.69 
Ronco	$42.00 
Ronco	$33.33 
Bad Dragon Dildos, Inc.	$99.99 
Bad Dragon Dildos, Inc.	$0.99
I know how to make subtotals for each in the sheet itself, like this:
code:
Recipient	Amount
Something Awful LLC	$420.69 
Something Awful LLC	$66.66 
Something Awful LLC	$69.69 
Something Awful LLC Total	$557.04 
Ronco	$42.00 
Ronco	$33.33 
Ronco Total	$75.33 
Bad Dragon Dildos, Inc.	$99.99 
Bad Dragon Dildos, Inc.	$0.99 
Bad Dragon Dildos, Inc. Total	$100.98 
Grand Total	$733.35
But what I really want to do is have this all on a separate worksheet, in a Pivot Table I guess. Like so:
code:
Something Awful LLC Total	$557.04 
Ronco Total	$75.33 
Bad Dragon Dildos, Inc. Total	$100.98
Again, this seems like a straightforward thing, but I can't figure it out

prefix any range or cell in the second worksheet with the name of the name of the first worksheet plus an exclimation point. So if you want column A from sheet 1 (named source):

code:

source!A:A

Also, when you go to insert->pivot table you're given the option to make your pivot table on a new worksheet.

# ¿ Jan 31, 2023 14:42

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 19:50

HootTheOwl: May 13, 2012; Hootin and shootin

I'm having trouble understanding what you want but a quick and dirty way to get around circular references is nice inputs to a different cell or worksheet all together.

# ¿ Feb 19, 2023 03:50

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›3 »