Roger’s Excel Thread

[Beefsteak1138]

Originally Posted by mandy18

The spreadsheet may have been using a table.
Highlight your range (or the entire worksheet); format as a table.
After you start scrolling down, you can't see the A,B,C,D column labels anymore.

That was it. Knew it was something simple.

[drop2d]

Maybe beefy is talking about 'freeze panes'?

View->Freeze Panes->Freeze top row

Maybe?

Edit... guess not.

[Arby]

Originally Posted by rconverse

Why do you have multiple column headings in the same columns? Makes no sense.

Okay, that said, the only way to do this is with a macro. Is that okay?

Well no, theyre not exactly the same. There is year over year variance


How complex a macro? lol

[rconverse]

Originally Posted by Arby

Well no, theyre not exactly the same. There is year over year variance


How complex a macro? lol

Okay, so is the spreadsheet setup where rows 1-6 & 474-479 like totals rows and the detail is underneath? If so, there's a better way to do what you're trying to do.

If not, it won't take me long to write a quick macro and i'm stuck in the office tomorrow anyways.

[Arby]

Originally Posted by rconverse

Okay, so is the spreadsheet setup where rows 1-6 & 474-479 like totals rows and the detail is underneath? If so, there's a better way to do what you're trying to do.

If not, it won't take me long to write a quick macro and i'm stuck in the office tomorrow anyways.

Correct. Not totals necessarily, but year and date information. It's basically the same header in style, but of course year and "week of" info changes.

If it's a simple macro that you can easily do, I wouldnt say no. But please dont go thru too much trouble

[rconverse]

Originally Posted by Arby

Correct. Not totals necessarily, but year and date information. It's basically the same header in style, but of course year and "week of" info changes.

If it's a simple macro that you can easily do, I wouldnt say no. But please dont go thru too much trouble

Are you familiar with the subtotals function? If what I think your spreadsheet looks like is correct, that may be a much better solution.

Regardless, a macro isn't hard to write for this...I don't think.

[Arby]

Oh, yea I think I know subtotals. Thats the + - cells on the left?

I know what you mean, but wasnt looking for that necessarily. I think I've seen a way to do it by just scrolling, but I dont know how is all haha

[rconverse]

No, the subtotal function. It basically allows you to use filters while still looking at the correct totals for what you're filtering on.

https://support.office.com/en-us/art...0-e478765b9939

That is different than the +/- thing.

If you want, you can PM me an email address and I'll send a mock spreadsheet using the subtotal function to see if that will work for your needs.

[AJF_41]

rog in the excel thread gets me wet

[daveshookme]

Originally Posted by AJF_41

rog in the excel thread gets me wet

you too?

[rconverse]

^^ @ you two.

Arbs - I sent you a file trying to display the SUBTOTAL function as an example. Let me know if you think that's a possible solution.

If not, I have no problems writing a macro so that the new frozen panes "magically" appear when scrolling through your worksheet.

[DaveHead36]

Ok, this should be relatively easy and I've done a google search and tried 2 different formulas but neither work. One was an IFSUMERROR i think and the other was a new rule formulate cells or something.

I have column A with 4500 serial numbers. In column B I have 80 of those serial numbers that need to be designated a specific number; but I have to know where they are in column A. I guess I could copy a SN from column B and CTRL+F it in column A but that would take me forever.

There has to be a way that highlights the 80 SN in column B in column A, right? I've spent all day on this and it's driving me nuts.

[ExistenceNow]

I love this thread. I have a child's understanding of Excel, but it's a fascinating tool that I love reading about, even if I can't use it properly.

[DaveHead36]

Figured out my problem. People kept offering up all these formulas. Turns out I just needed to click the columns and select conditional formatting and highlight cell rules and duplicate values.

[kydmb99]

V-lookups and pivot tables are life changing

[rconverse]

Originally Posted by DaveHead36

Figured out my problem. People kept offering up all these formulas. Turns out I just needed to click the columns and select conditional formatting and highlight cell rules and duplicate values.

I'm just curious and trying to understand the request. My first question would have been, what's the difference with these 80 records? Was the request to basically find the 80 records where cell A# = cell B#?

[DaveHead36]

Originally Posted by rconverse

I'm just curious and trying to understand the request. My first question would have been, what's the difference with these 80 records? Was the request to basically find the 80 records where cell A# = cell B#?

Yes. Basically I had 32 pallets each with 144 computers. I had to designate locations to all 4500. But 80 got pulled and rearranged on their own pallet. So I needed to find out what pallet they came from originally. So I needed to know where the 80 serial numbers in column B were located in column A.

[Route_2]

Is there a way to reverse the order of the tabbed sheets in a single workbook. I have a series of sheets tabbed by date and it's getting hard to scroll all the way to the right when I need to add a new one. Is there a function in Excel to flip them so the newer ones are on the left without clicking and dragging each one to reorder?

[sheldonlevene]

Originally Posted by Route_2

Is there a way to reverse the order of the tabbed sheets in a single workbook. I have a series of sheets tabbed by date and it's getting hard to scroll all the way to the right when I need to add a new one. Is there a function in Excel to flip them so the newer ones are on the left without clicking and dragging each one to reorder?

I don't know how you'd do this, but right clicking near(ish) the scroll arrow in the bottom left should give you a list of all sheets and then you can navigate to the end relatively quickly. Helpful with upwards of 40 or 50 tabs in a file.

Also ctrl+click will scroll all the way to the end.

[Route_2]

Originally Posted by sheldonlevene

I don't know how you'd do this, but right clicking near(ish) the scroll arrow in the bottom left should give you a list of all sheets and then you can navigate to the end relatively quickly. Helpful with upwards of 40 or 50 tabs in a file.

Also ctrl+click will scroll all the way to the end.

Thanks. Those will definitely save me some time in lieu of a way to reorder.

[Beefsteak1138]

Do you need to need to have them visible at all times? If not, you can hide the ones you don't need to see (right click on tab, you can hold down control and select multiple tabs > Hide)

[Route_2]

Originally Posted by Beefsteak1138

Do you need to need to have them visible at all times? If not, you can hide the ones you don't need to see (right click on tab, you can hold down control and select multiple tabs > Hide)

That's very helpful too. Thanks.

[rconverse]

https://www.extendoffice.com/documen...orksheets.html

If you are familiar with VBA code, the following code can do you a favor, please do as this:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Reverse the order of the worksheets

Sub ReverseSheets()
'Update 20140526
Dim xCount As Integer
xCount = Application.ActiveWorkbook.Worksheets.Count
For i = 1 To xCount - 1
    Application.Worksheets(1).Move After:=Application.Worksheets(xCount - i + 1)
Next
End Sub

3. Then press F5 key to run this code, and the order of all worksheet tabs will be reversed immediately.

Tip: Press the F5 key again, all worksheet will be ordered with the original order.

[DaveHead36]

I'm trying to figure out how to get an average on production. I have 6000 computers that need to be processed and have about 18 business days to meet the deadline. I created a worksheet that has the days date, how many computers were processed that day, how many we have left to do then the grand total of computers in a single cell off to the side.

I'm trying to have a column that shows the current pace. It needs to change with the daily end of day numbers. I am excel inept

[rconverse]

Like this?

If so then you can setup your spreadsheet like this. In cell C2, paste this formula "=IF(ISNUMBER(B2), G$1-SUM(B$2:B2), "")" and in cell D2, paste this formula "=IF(AND(ISNUMBER(B2), ISBLANK(B3)), SUM(B:B)/COUNT(B$2:B2), "")".

Copy those formulas down until the last row where there's a date. The only thing you'll need to update is in column B. As you update column B, the formulas in columns C and D will automatically recalculate. Hope it helps!

[DaveHead36]

Originally Posted by rconverse

Like this?

If so then you can setup your spreadsheet like this. In cell C2, paste this formula "=IF(ISNUMBER(B2), G$1-SUM(B$2:B2), "")" and in cell D2, paste this formula "=IF(AND(ISNUMBER(B2), ISBLANK(B3)), SUM(B:B)/COUNT(B$2:B2), "")".

Copy those formulas down until the last row where there's a date. The only thing you'll need to update is in column B. As you update column B, the formulas in columns C and D will automatically recalculate. Hope it helps!

Sort of. I need an average for a target date. Using your example, lets say I have a soft deadline on the 11th where I would like to have all systems processed. I want a running tally of the number I need to hit. Let's say it's 200 a day, but then I end up hitting 275, but then the next day I hit 180, etc. Also beyond the 11th would be a hard deadline later on like the 18th. That way I can kind of see what my pace is.

Ideally in the row of the date would be the total number of systems I would have to do per day to finish by that date.

But I am also going to incorporate your formula to get a running total on how we are doing.

Thanks in advance!

[rconverse]

I cell E1, you can add this (or whatever you want) as a title "Avg/Day to Hit Deadline" and then in cell E2, enter the formula "=IF(D2="","",C2/COUNT(A3:A$19))" and copy down.

[DaveHead36]

You're the man! I would have never figured that out on my own. Thanks so much!

[rconverse]

I just saw the whole hard and soft target dates. You'll need to use something more like this for that...

[AJF_41]

#rockhard