Excel If Function

New Contributor

I have multiple spreadsheets and am attempting a formular to paste cells using the if function.  In Spreadsheet #2, I have a table and the corresponding cells beneath each category are listed items using data validation.  In spreadsheet #1, I am running the if function for every selection of a listed item using an exact match "" to paste the appropriate table from spreadsheet #2.  My only problem is that when the table pastes, it does not have the (list) option in the cells under the categories any longer as it does in spreadsheet #2.  

3 Replies
Maybe you could explain what you are trying to do as in what you functionality and why as opposed to the specific method that isn't working, we might have a better suggestion. Also, please attach or give a link to a sample sheet(s) se we can actually see it. From what I'm piecing together you have a whole table of IF() statements that 'copy' the data from the other sheet if it isn't a match to "" but then data validation doesn't copy also. That said, cell formulas have no effect on other cells or formatting; they ONLY display the result of the embedded calculation/formula. So no, an IF() statement cannot "copy" the data validation. But even if it could you wouldn't want that (I don't think) because as soon as you change the Value of a cell with a formula you essentially overwrite that formula.

@mtarler I have attempted to save the file type as multiple different options and am being denied to insert it here.  You have alleviated my stressed question.  However, what I am trying to accomplish is to create an action plan.  I would like to have a selectable drop down option and based off of that selection, it would guide you to filling out the following steps to fill out.  These steps once followed would provide a price from the aggregated data inserted.  I can accomplish this task, but it involves multiple spreadsheets of calculations.  I am creating this spreadsheet for my sales team and want them to have limited access.  I can lock spreadsheets and calculations, but I would like for my sales team to remain in one spreadsheet.  I know this is feasible, however I am rusty in excel as it has been taken from my repertoire many years ago!  I appreciate your assistants and guidance in any way.



@Guardian1991 I don't know exactly what you want but in the attached I created a sample where you have tables for 3 options and then data validation based on each choice.  I used the FILTER() function so it requires the new Excel.  Instead of 3 tables it could be done in 1 table and lots of other options depending on what you have/need.

If you can't attach a file you can give a link to shared book in OneDrive or SharePoint or a dropbox or even attach in a PM on the site here.