Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

A few off the cuff thoughts:

  • Your input sheet needs a date column
  • INDEX/MATCH the Classification, Cost Code, Hours using a concatenation of the employee's name and the date (from the header)
  • Depending on the depth of your employees list, its not wise to use names as a key (since there can be dups). I'd recommend assigning unique employee IDs.
  • Like 1

Share this post


Link to post
Share on other sites

input sheet has a date I just did not include the top of the sheet I only posted the table I am trying to pull from.

There is that concatenate word again. I was thinking something like this

=IFERROR(INDEX('Daily Report Wed'!$E$18:$E$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for classification

=IFERROR(INDEX('Daily Report Wed'!$B$18:$B$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for cost code

=IFERROR(INDEX('Daily Report Wed'!$G$18:$G$40,SMALL(IF($A$8='Daily Report Wed'!$C$18:$C$40,ROW($C$18:$C$40)-ROW($C$18)+1),1)),"") for hours

Good point on the names I have to come up with a solution for that, but for now I am going to just search by trying to match a name from the payroll sheet to the daily report. Our company I can get employee numbers, but the payroll companies we use for rent a drunks may not be as easy.

edit for syntax error

Edited by Herc

Share this post


Link to post
Share on other sites

"Daily Report Wed"

So you have daily input tabs - its not just one big tab? Maybe that's to keep it dumbed down for the guys inputting their time, but that's a clunky way to do it. It'll be a lot cleaner with a single input tab where they enter the date they worked.

Share this post


Link to post
Share on other sites

"Daily Report Wed"

So you have daily input tabs - its not just one big tab? Maybe that's to keep it dumbed down for the guys inputting their time, but that's a clunky way to do it. It'll be a lot cleaner with a single input tab where they enter the date they worked.

There is more to the sheet than just payroll its a daily report of work(completed and billable), equipment on site, subcontractors, inspections and employees.

My thoughts are if they can fill out one report without screwing it up I can automate the rest, but it is definitely proving a challenge.

its a weekly workbook and its a blank template that can be used on any of our construction projects.

Edited by Herc

Share this post


Link to post
Share on other sites

Ned I took your advice and blew up the entire workbook and redesigned it. To get around my previous problem I made drop down menus linked to drop down menus and it works great, the first menu has all the companies listed the second menu only lists the employees with each company.

I am having some problems with calculating something and my formula just isn't working like I would expect it to. Basically what I am trying to do is check 6 cells for a Yes each yes= 8 hours but there is a cap of 40 hours for the week then I am subtracting the working hours from that number(we are tracking idle time). I nested a bunch of if statements and I cant seem to figure out what I am doing wrong. It always returns a Not on Site value, is it a simple syntax error or should I approach this differently maybe with a count if? I even tried reversing my arguments from highest to lowest to the opposite and its the same thing

Here's what I have currently:

=IF(IF(IF(IF(IF(IF(IF(IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)>=40,40-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=32),32-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=24),24-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=16),16-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=8),8-F15),IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=0),0,"Not on Site")

Share this post


Link to post
Share on other sites

That's a crazy deep formula that looks over-engineered to me. It's hard to see with that many nested IFs, so I'd suggest using the Evaluate Formula tool (under the Formulas ribbon) to step through each condition. I'm guessing you're always falling into the final FALSE because you're always summing more than 0.

I'm not sure what the final FALSE is doing ("Not On Site"), but you can simplify your calc with this:

=(COUNTIF(B12:G12,"Y")*8)-F15

That'll give you the hours for each Y and subtracting out whatever you have in cell F15.

  • Like 1

Share this post


Link to post
Share on other sites

=IF(COUNTIF(B124:G124,"Y")*8>=40,40-F15,COUNTIF(B124:G124,"Y")*8-F15) this should work. Thanks Ned!

Also sent you a PM

Edited by Herc
  • Like 1

Share this post


Link to post
Share on other sites

Ned - Here's a question I've come across several times and have never been able to figure out on my own. Is it possible to put an image inside a cell? I can copy an image and put it on the sheet, but I can't confine it to a cell. If I'm in WORD, I can create a chart and put images inside certain cells and that looks great. But in WORD, I can't then create formulas to go with the images. So I'm kind of stuck between the 2. I'd like to have some images confined to certain cells and also be able to have the ability to create functions around them (not using them).

Any ideas on how to accomplish putting images in certain cells?

Share this post


Link to post
Share on other sites

Ned - Here's a question I've come across several times and have never been able to figure out on my own. Is it possible to put an image inside a cell? I can copy an image and put it on the sheet, but I can't confine it to a cell. If I'm in WORD, I can create a chart and put images inside certain cells and that looks great. But in WORD, I can't then create formulas to go with the images. So I'm kind of stuck between the 2. I'd like to have some images confined to certain cells and also be able to have the ability to create functions around them (not using them).

Any ideas on how to accomplish putting images in certain cells?

:blackdot:

I used to do this all the time... at least that's what my brain is telling me. the only physical examples I can find just have the image floating over the top, and not "in" the cell.

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

Share this post


Link to post
Share on other sites

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Share this post


Link to post
Share on other sites

I would most likely start deleting parts of the workbook. Excel can only track so many dependencies at one time. Once you delete enough, the button should work for you.

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

For resizing and moving columns or rows.

Share this post


Link to post
Share on other sites

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

Share this post


Link to post
Share on other sites

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

I don't see this. I have a "Check for Errors" dropdown, and the two options are 'Check for Errors..." and "Trace error". I clicked "Check for Errors...." and the dialog that popped up read: "The Error Check is complete for the entire sheet." There were no errors found.

Excel Mac 2011

Edited by Walking Boot

Share this post


Link to post
Share on other sites

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

I don't see this. I have a "Check for Errors" dropdown, and the two options are 'Check for Errors..." and "Trace error". I clicked "Check for Errors...." and the dialog that popped up read: "The Error Check is complete for the entire sheet." There were no errors found.

Excel Mac 2011

The bold is your problem.

Share this post


Link to post
Share on other sites

Every time I open a particular spreadsheet, I get a dialog box telling me Excel cannot calculate because of a circular reference. It never tells me where this reference is. The "Circular:" at the bottom of the screen lists a cell but there's no error in that cell to trace.

I think I'll just throw the computer out the window instead.

Go to the Formulas ribbon and under the Error Checking button there's an option to show the circular references.

I don't see this. I have a "Check for Errors" dropdown, and the two options are 'Check for Errors..." and "Trace error". I clicked "Check for Errors...." and the dialog that popped up read: "The Error Check is complete for the entire sheet." There were no errors found.

Excel Mac 2011

The bold is your problem.

I don't doubt it. My boss is an idiot sometimes.

Share this post


Link to post
Share on other sites

I feel so lonely.

=IF(A1="Lonely","PM Wet Dream","What's your problem?")

  • Like 1

Share this post


Link to post
Share on other sites

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

Share this post


Link to post
Share on other sites

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

I'll take a look when I'm back in the office. I haven't used fuzzy lookup so I'm curious.

Share this post


Link to post
Share on other sites

Let's talk Fuzzy Lookup.

I have several warehouses with say 100k items. The warehouses may have the same item but when it was originally uploaded into SAP the description might have been different.

Example:

Whse 1: pen, red, bic

Whse 2: bic pen

Whse 3: pen model #abcd

The goal is to identify all of the similar items across the different warehouses. Very watered down but a fuzzy lookup will lookup similar items based on percentage similarity. Let's say I have 100k items amongst 10 warehouses. I can perform a fuzzy lookup with a 90% accuracy and then with conditional formatting highlight the same words. At this point it would become more of a manual process to review and find the like items.

thoughts?

Here you go

I'll take a look when I'm back in the office. I haven't used fuzzy lookup so I'm curious.

I've read about the Fuzzy Lookup Add-in and never did install it, but I think this could help with problems using Vlookups, etc...

Share this post


Link to post
Share on other sites

Why do you need it confined to the cell, in particular?

If I want to do a nice visual report that say, talks about sales for each state, it would be nice to have the picture of the state in a cell of the row. Or a region. Or a country. Or if I'm running a football pool, it would be nice to put a helmet in the cell to identify the team instead of typing out the team name. I mean, there are a lot of reasons.

I do this all the time since I do a lot of prettied up external reporting for work.... The best I think you can do is to change the properties to 'Move but don't size with cells'. When you move rows/columns around it'll stay in the cell location you put it. The only downside is if you resize the row or column, it's not going to move with it. So you'd need to re-center if you re-sized.

Where is this, exactly?

Nevermind. I see it. Yeah. This is the best I've had in the past, but I was hoping for something better. Damn.

I'm confused as to why you don't want it to just lay on top.

For resizing and moving columns or rows.

I have experience in this area as I have projects in the US, Canada, and Latin America and I display the country's flag in a cell and it stays put in the cell and conforms to the cell size ......I think you need to hold down the ALT button and then stretch the image. By holding down the ALT button it will automatically align to the cell wall (or multiple cells, if you merge cells) that you want to apply it to.

BTW: If you want to improve your Excel Visual skills, I highly recommend a course called "Excel Driven Dashboards". It's an 8 hr course that I learned the above technique that walks you thru how to make advanced Excel charts and dashboards. Most of us learned Excel years ago when the chart functions were limited. But now Excel has incorporated a lot of new stuff to help you make better visual presentations. The course is interactive, where it gives you step by step instructions for each technique and example workbooks to create the chart/dashboard with a tab next to it of what it is supposed to look like when u are finished so you can check your work. Some of the techniques you learn are really cool....like in-cell Sparklines, Advanced Conditional Formatting, etc. The course allowed me to make a bunch of new KPI charts that our department was tasked with and allowed me to interact with some other Sr Managers and CEOs that I hadn't had much interaction before. It definitely opened some "doors" for me.

https://www.westerncpe.com/store/detail.aspx?id=2134528ONL

If anyone is interested, pls PM me (be patient though as it may take a couple days to respond)

  • Like 1

Share this post


Link to post
Share on other sites

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

Share this post


Link to post
Share on other sites

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

Share this post


Link to post
Share on other sites

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

Just press F4 (with the array selected in the formula bar or formula wizard) to toggle between the various relative vs. absolute references

Share this post


Link to post
Share on other sites

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

You can protect the book from being opened under the General Options when saving the book.

You can also protect specific cells -> https://support.office.com/en-us/article/Lock-cells-to-protect-them-54fedd95-28dc-4abc-a26c-6566917c1a59

Share this post


Link to post
Share on other sites

I would like to build a sheet with several layers of security along with protecting the entire workbook. Is it possible to password protect a sheet when opening and then, as an additional layer, hide a few columns where they could only be unhid via a different password? Any ideas on how to make a workbook and its data as secure as possible would be appreciated.

You can protect the book from being opened under the General Options when saving the book.

You can also protect specific cells -> https://support.office.com/en-us/article/Lock-cells-to-protect-them-54fedd95-28dc-4abc-a26c-6566917c1a59

:thumbup: Thanks as always!!

Share this post


Link to post
Share on other sites

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

Share this post


Link to post
Share on other sites

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

I hate those too. You can disable them before that step, and then re-enable afterwards.

Application.DisplayAlerts = False

your code here

Application.DisplayAlerts = True

Share this post


Link to post
Share on other sites

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

Share this post


Link to post
Share on other sites

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

Format the column as text. When you're pasting in the orders, paste special - values.

Did you know you can download data from the web into Excel automatically? I use it all the time for some reporting I do.

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba

  • Like 1

Share this post


Link to post
Share on other sites

I know I'm probably missing something easy here, but I've noticed that sometimes the more advanced I get at Excel, I tend to not remember or know some easy things.

I have a simple sheet where I just keep track rof orders. It's by far the simplest sheet I use. In one column, I copy order numbers from a web site and paste them into it. Thing is, some of these order numbers start with a zero. Now, I know that I can add a ' in front of the number and then put a zero and it will show. But I would much rather just paste them in and have the zero show up. Is there a simple way to do this? I feel like I've done it before using column properties. Does this make sense?

Format the column as text. When you're pasting in the orders, paste special - values.

Did you know you can download data from the web into Excel automatically? I use it all the time for some reporting I do.

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba

Thank you! This is what I was looking for. I knew it was in there somewhere, I just couldn't remember it. You da man, as always. :thumbup:

Share this post


Link to post
Share on other sites

When you're setting up a vlookup, where's the switch that automatically makes the array you are searching absolute instead of relational? Does this mess up a bunch of other basic functions?

Just press F4 (with the array selected in the formula bar or formula wizard) to toggle between the various relative vs. absolute references

Does this make the array default to absolute values the next time I set up a lookup?

Share this post


Link to post
Share on other sites

I do macros occasionally to manipulate data.

In one of them, I'm doing a text to columns.

Is there any way to make that annoying do you really want to do this text to columns message that pops up, not pop up?

Yes I really want to do it, that's why I built this macro like this.

I hate those too. You can disable them before that step, and then re-enable afterwards.

Application.DisplayAlerts = False

your code here

Application.DisplayAlerts = True

Thanks but that gives me a syntax error in my macro.

I tried inserting those lines before my first line and after my last line in the macro.

And I really do not know Virtual Basic.

I was using the Record Macro button in the Developer Tab to set up the macro.

EDIT - And then I closed it and reopened the macro and entered the commands again, did a run from the editor, saved it, tried it again, and it ran perfectly.

F'n computers!

Thanks Ned!

Edited by Village Idiot

Share this post


Link to post
Share on other sites

Post your code. That DisplayAlerts code shouldn't be giving you any errors.

Edited by Ned

Share this post


Link to post
Share on other sites

Post your code. That DisplayAlerts code shouldn't be giving you any errors.

I edited my post above yours.

It works now.

I probably accidentally deleted something the 1st go around trying to enter those commands.

I'm good at breaking stuff but I'm all set now.

Thanks!

Share this post


Link to post
Share on other sites

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

Share this post


Link to post
Share on other sites

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + Arrows

This will select to the last cell that contains something.

  • Like 1

Share this post


Link to post
Share on other sites

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + Arrows

This will select to the last cell that contains something.

:thumbup: Thanks!

Share this post


Link to post
Share on other sites

Is there a hotkey for grabbing all data in a worksheet, that is similar to Ctrl + arrow keys to go to the end of the last line of data? Not Ctrl+A

Example: You have 7,000 rows of data spanning 8 columns. You want to delete all rows in the worksheet except for a few rows that you've filtered.

I want to leave rows 1 through 10, but select and delete the rest of the 6,990 rows. I can select row 11 and drag all the way down the sheet, but that takes awhile. Is there a quick way to do this?

TIA

CTRL + Shift + Arrows

This will select to the last cell that contains something.

:thumbup: Thanks!

Welcome.

CTRL + Arrows will move you around without selecting. Learn to use those. Soooooooooooooooo much faster than mousing around.

  • Like 1

Share this post


Link to post
Share on other sites

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

:wall:

Share this post


Link to post
Share on other sites

=IF(COUNTIF(B124:G124,"Y")*8>=40,40-F15,COUNTIF(B124:G124,"Y")*8-F15) this should work. Thanks Ned!

Also sent you a PM

I know it's been a while, but are you still having issues? Would a simple DB be more efficient than a spreadsheet?

Share this post


Link to post
Share on other sites

This is a such a frustrating problem:

Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf.

:wall:

What data source?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.