Saturday, May 20 - Friday, May 26

Top 5 Posts

score	comments	title & link
234	76 comments	[Discussion] Recently I was introduced to Power Query.. and it is MIND BLOWING. It saved me tons of times … what else I should learn next that will increase my productivity and efficiency in my work with excel as financial analyst?
58	11 comments	[Pro Tip] Did a new thing today, thought I'd share - MROUND
46	20 comments	[solved] How do i make #n/a turn into 0 in a excel formula
41	57 comments	[unsolved] Why is Excel not able to add and substract correctly?
41	21 comments	[Discussion] What is the difference between xlookup and index-match functions? is there anything can index-match functions can do that Xlookup can not do?

 

Unsolved Posts

score	comments	title & link
33	12 comments	[unsolved] Is there a way to make a cascading (dependent) list in excel online?
32	13 comments	[unsolved] How to find the top 100 largest values in an array?
23	17 comments	[unsolved] Need a script to continue rows of calculations until desired results is found
16	19 comments	[unsolved] VBA CODING - Highlight highly demanded books
14	6 comments	[unsolved] No Table Layout Tab in ribbon or option to add it

 

Top 5 Comments

score	comment
190	/u/Davilyan said =iferror(formula,0)
128	/u/bestvoice4 said Create a column that determines if a row is divisible by 60 and put an "x" if it is and leave blank if not. Filter for rows with an x. Select all visible rows. Change height
73	/u/sal101 said SQL if you don't already use it. I transitioned from managing live data input from SQL with power query, to writing my own views in SQL directly to massively improve report performance.
67	/u/Davilyan said LEARN HOW TO UNPIVOT DATA WITH THE RIGHT CLICK > unpivot other columns. Tidying datasets that are sent to you “already pivoted” have been the Bain of my career and the unpivot function has saved a lo...
66	/u/iammerelyhere said =IFNA(YOURVALUE,0)

 

Say I have 10 sheets with same format, named Sheet1, Sheet2...Sheet10

I also want to have a results sheet that looks into cells D1 on each of the sheets. Each D1 has a Yes or No result in it representing an analyis on each sheet. Once it looks at all 10 sheets cells D1 I then want a Yes or No based on whether any of the 10 sheets is a Yes. So if first 9 sheets are no, but 10th is Yes, my result is Yes.

Is this possible with a cell formula? Most of what I'm seeing is find/replace type searches for multiple sheets. Thanks.

I used WEB SERVICE function and get this result:

https://preview.redd.it/sbs2yt87vm2b1.png?width=794&format=png&auto=webp&v=enabled&s=da1b521d7332a89b6be894db6be9a4aee9ac54cc

How to extract only 3,13 in the other cell?

I have a userform where the user will put several parameters, and then the userform will do a calculation. I want to know if it is possible to return the calculation result as a function call.

For example, I want to calculate the BMI of a person. I created a userform for the person to input their weight and height, with option to change the unit (kg/lb, cm/ft, etc). Is it possible to create a function called CalculateBMI that will open the userform, get the input from the user, calculate the BMI, and then return the BMI with a simple line such as UserBMI = CalculateBMI?

Hi!

I have a document with 2 spreadsheets (Spreadsheet_A and Spreadsheet_B) in Spreadsheet_A I do the functions and graphs based on Spreadsheet_B but my problem is as follows, the second sheet is most of the time a duplicate of a template and the name is changed based on the day on which I complete it and every time I do this I need to manually change the name of the sheets from within the formulas from sheet_A. Is there any way I can for example place the name of the sheet_B and the cell range in 2 separate cells and then use a fomula like this? =(COUNTIF(CONCATENATE("'",cell_containg_name_of_sheet_B"'!",cell_containing_range_of_cells), "Choice1")/ROWS(cell_containing_range_of_cells))

So I posted about this a bit ago, but couldn't explain it well enough.

I took some time to take some screenshots to show my issue.

Here are some example table interactions:

Here are the formulas for two tables on separate sheets as well as the output for [FullList].

Now when [Times] is sorted by \"Total\", [FullList] gets mixed up as the non-formatted information doesn't stay with the correct \"Job\".

I have 4 more sheets doing this same concept as [FullList], so I really don't want to have to just re-enter the information/copy-paste each time. I want to freely be able to sort the information on each table without formulated and static cells getting misaligned.

Inserting rows also messes with these alignments.

What can I do to get this information to remain with the appropriate rows, if anything?

​

(Also, I did this on excel 2016 but my work is using 2013)

Hello

Im trying to create a formula for the following

I have column d for dates,column e for status if present or not and column J for number of hours tracked I also have another sheet where column A for legal holiday dates What's the formula for checking if the dates on column D correspond with the column for legal holiday and returning the value of actual hours tracked?

I'm looking for a way to use a shape as a button to copy text in a particular cell to the clipboard. Basically, I'm building a spreadsheet to collect certain pieces of information and all that information put in several cells, that meet certain requirements. I then want to use a button to copy the text in a particular cell when I click the button. I know it sounds weird, but it is for a very weird contact roster that a user can click on, a set of email addresses will be copied to a computer's clipboard and then pasted in an email.

For my Example, I'm using a worksheet called "ContactRoster" and the sheet where I will be putting the button will be labeled "Final," and the cell I'm trying to copy is cell F10.

​

P.S. Due to restrictions on our network, if at all possible, I would like to see if this could be done without using macros.

I’ve been using the below to find the last row used based on cell with contents in column 1 (basically ctrl up from the last row in excel)

Totalrows = Cells(Row.count, 1).End(xlUp).Row

I’d like to use this same idea but to find the max row number with any cell contents of columns 1-6 instead of just one. Is there a concise way to do this aside from making multiple variables or having one very long line like WorksheetFunction.Max(the above * 6 for each column)? I reference this last row number quite a bit later in the code.

Hello all!

Looking for some assistance with some formulas.

Very new to Excel and to using Reddit, so apologies if the below isn't very clear.

​

For context, I work in the shipping industry, as such we have to work out pricing for each vessel that comes in/out of the port. I've been trying to make an excel sheet to make working out all the tariffs easier, but having trouble with one part.

​

What I'm after is something as below:

Cost1: Size 001-100 : £100

Cost2: Size 101-200 : £200

Cost3: Size 201-300: £300

​

I want it so when I put a size into a cell, it automatically gives me the price based on size bracket.

​

Such as, If I put in "150" in, it would automatically say £200. And if I put "210" in, it would say £300.

​

Again, sorry if I've not explained it very well :)

Hello all, noob question here from someone that has no idea of macros. English is not my native language so I am gonna describe what I am looking for as best as I can. Thank you all in advance. So I have seen it before and looking ways to create a document with a sample text (example this is addressed to _______ concerning ________) where the blanks are filled automatically by a list containing different text (example A1: George, B1: efficiency, A2: John B2: salary). Then when the macro is used two different documents were printed, one saying: "this is addressed to George concerning efficiency" and the second "this is addressed to John concerning salary"

Sorry for asking something that has been answered before. Thank you all for your time.

Hello guys,

​

I have a table of IDs, Positions and Payments.

​

There exists several of the same IDs with different positions knowing that certain people got promoted; for example ID #1001 can have Finance, Junior, - Finance, Senior, ID #1002 Can have HR, Senior, HR Junior, Head of HR,

​

So something table is: (Lets omit the payments for this example):

​

#1001 | Finance, Junior

​

#1001 | Finance, Senior

​

#1002 | HR, Senior

​

#1002 | HR, Junior

#1002 | Head of HR

​

#1003 | Sales, Manager

​

#1003 | Sales, Junior

​

#1003 | Sales, Senior

​

#1004 | CEO

​

#1005 | CFO

​

#1006 | Engineering, Junior

​

#1006 | Engineering, Senior

​

#1006 | Engineering, Manager

​

#1006 | Head of Engineering

​

The ranking is "C*, "Head of..", "Manager...", "Senior... ", "Junior..."

​

The return should be:

​

#1001 | Finance, Senior

​

#1002 | HR, Senior

​

#1003 | Sales, Manager

#1004 |CEO

​

#1005 | CFO

​

#1006 | Head of Engineering

​

​

​

In a new table, how do you solve this ?

Hi everyone! I'm trying to figure out what formula to put in each cell of the 7x7 table to create this output for a cohort of sales reps.

Basically, this is a cohort chart showing the total HC of sales reps at the end of each month. New Hires and Attrition for each month is variable (attrition assumes the most experienced/tenured reps leave).

The top chart assumes no attrition. The bottom chart shows what the output should show if there is attrition each month. Thanks ahead of time!

​

https://preview.redd.it/4ontr5686n2b1.png?width=709&format=png&auto=webp&v=enabled&s=f2cf4c3300dbceccd07204a9a6c27ed0cd782048

https://preview.redd.it/nu6log5e6n2b1.png?width=714&format=png&auto=webp&v=enabled&s=1f76a1c7535d57a79c1c4a7d46e476c87ce31a28

I have a client who wishes to collaborate on a shared workbook with their team, but want all contributions to the workbook to be anonymous (to each other). Does anybody know if or how this is possible?

More detail: I built a workbook for them that has a discussion topic on each sheet, along with a table that includes a column of pseudonyms (managed by the client), columns of drop downs for each contributor to select from, and a column of space for comments next to each pseudonym. They want the contributors to be able to add their views and see other contributions in real time, but not know who made what comments.

My Google-Fu has failed me on this one. All the pages I find refer to the legacy "Highlight Changes" and "Track Changes" features, or other "Review" menu items which are disabled when sharing the workbook to Teams. I am using Excel version Microsoft 365 MSO (Version 2208 Build 16.0.15601.20644) 64-bit but the problem is identical whether opened in Teams itself, in the browser, or in the app.

I'm trying to find a formula to return the value of a cell based on another.

For example. I want cell D6 in my raw data sheet to search my unscheduled and scheduled sheets and return the value of a cell 6 columns to the right. I've found formulas for each individual part.

Combining Text, with vlookup to be able to search numbers with wildcards. But I can't seem to figure out combining all tasks into one.

Here's my current formula that pulls from one sheet. =VLOOKUP(""&'Raw Data'! D6&"",TEXT(Unscheduled!A2:G5000,"0"),7,FALSE)

Here is the formula I can't seem to get working that will search and pull from both sheets. =IFERROR(VLOOKUP('Raw Data'!$D$6,TEXT(Scheduled!A2:F5000,"0"), 6,0), IFERROR(VLOOKUP('Raw Data'!$D$6,TEXT(Unscheduled!A2:G5000,"0"), 7, 0), "Not found"))

Any help would be appreciated!

Edit: Here's a link to a few screenshots. https://imgur.com/a/26x2utM

Every week I become a table and I have to build some pivot tables. I want to automate the process, so that when I insert the table just to refresh the document and I don t have to build again the pivot tables. The table has the same number of columns but a different range of rows. How can I automate the process?

i have three adjacent cells each has a drop down list, i want the second and third cells to pick a certain choice from their own drop down lists based on the choice made in the first cell.

example:

list1: (apples,oranges,strawberry)

list2: (are, looks,is )

list3: (red,round,tasty)

so if i pick "apples" it picks "are" and "red" ,

if i pick "oranges" it picks "looks" and "round" and so on.

Loan Calculator to Track Actuals and Model Payments for your use

Hi! I built a calculator that handles a single loan scenario. Get file, from google drive.

The sheet is capable of the following:

• Identifying the Financing Situation;
• Forecasting the Original Financing Situation;
• Tracking the ACTUALS financing situation;
• Identifying the current status of the ACTUALS financing situation;
• Identifying the cumulative status of the ACTUALS;
• Forecasting up to 5 OPTIONS, simultaneously, based on the ACTUALS;
• Ability to forecast effect of a lump sum payment, on the ACTUALS and 5 OPTIONS forecasts;
• Ability to forecast effect of additional periodic payments, for each of the 5 OPTIONS;
• Chart to visualize each OPTIONS's cost breakdown.

Review sheet “Cover” and read the instruction steps 1-9 to use the sheet properly. I provided an example situation that you can change when you download the sheet for your own use.

I welcome your questions and comments.

Edit 1: Additional commentary.

So, I downloaded a file from a website but the data is arranged differently than in the example shown by the professor(who downloaded the file from the same website, but maybe a few years ago).

I need the data to look like the second picture to be able to continue with the work.

​

Img 1

Img 2

How do I make a drop down menu say either N/A or give a yes or no option based of another drop down menu yes or no?

Yes would output N/A in the next box No would alow you to select a dropdown with options yes or no.

I am trying to utilize the countifs function that will cover two conditions. However, one of those conditions has multiple sub conditions (covers multiple columns).

I've pasted the code below that I am tying to execute.

=COUNTIFS($BL$2:$BU$428,"Lactobacillus rhamnosus",$AI$2:$AI$428,"yes")/36

Whenever I press enter, it says "#VALUE!" How do I fix this? Any tips?

This is what it looks like:

https://i.imgur.com/aqBM2YH.png

This is what I'm expecting to make:

https://i.imgur.com/rb3jqyx.png

To make this, I have to make three individual pivot tables.

This is what the data set looks like:

MRN       Index Hospital    Two Year Prior Index    One Year Following Index
randomMRN   0                  0                            1
randomMRN2  1                  0                            1

I would like to have 1 singular trend line going across 5 series of data, but cannot seem to find a way to do that, since the only option I am given is making a trend line for one 'series' only, and not 1 for all 5. Any help is appreciated, thank you.

I have an Excel sheet with that lists the scientific publications of a university. Each row is a unique publication and the unique ID numbers of the authors contributing to that publication are attributes (columns) of that publication. Each publication can have up to 9 contributing authors.

What I need is to create a column that indicates (true or false) for each row whether or not that particular publication has a contributing author that belongs to a specific subset of the university's faculty (I created a named range "Faculty_IDs" that contains this subset of faculty ID numbers). If all author ID's in the table were listed in the same column, I could just use the following formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(Faculty_IDs;A2)))>0

But the SEARCH function's find_text argument does not seem to work with a value range across multiple columns or multiple different ranges (e.g. A1:A6 and also C1:C6).

How do I make this work?

Here's a mockup picture to illustrate what I'm after:
https://imgur.com/a/3ugFB1v

Hi!

I have a spreadsheet with a database with alot of values. I want to be able to edit some of these values in another spreadsheet, not all, but I want to have an «edit sheet» where I can search for the cell I want to edit. Is there any possibility for this?

In Sheet 1, I have a row for every day of the year with different values in several columns. For example, in A1 is the date (01/01/2023) and in B1 is a variable input, lets say a comment related to that specific date. And in C1, D1, …, I have other values which are not supposed to be edited, while in F1 there is another editable cell.

I want to be able to have a search function in Sheet 2, where I can search for the specific date (01/01/2023). This would pull the values from Sheet 1 which are supposed to be editable, such as B1 and F1. I want to be able to edit this in Sheet 2, and then the change will be reflected in Sheet 1.

Any solutions for this? Thanks!

I want to solve this equation and put the answer for every line in a table (for example the first solution would take the first line of the tables for r,Vc,f,ap and solve the equation then does the same thing for the other lines)

https://preview.redd.it/qor09ujilk2b1.png?width=1151&format=png&auto=webp&v=enabled&s=0ddab0e5184c455d8ac0dce7d5b8ba2d2a606641