Need to do this without using VBA, A Cell with data validation, if they input a checkmark, I need that whole row copied to another tab

[USFMD82]

The reason I want ot avoid VBA is because I dont kow what version of excel people will be using and I am not sure how to force someone to enable macros when they open a file. There coudl also be various levels of security that they run into. So I am trying to send out a bid sheet, there could be hundreds of items to bid on, but on the top sheet I want the offeror to be able to filter the list based on whatever column or combination of columns. Column A will have data validation on it to make it either Blank or a Checkmark. If a check mark is selected I would like that row copied to the second sheet where they would fill in their bid. so the second sheet is supposed to only have the items they are interested in bidding.

It would be nice if I could make sheet 2 hidden until they select a cell (I would ideally make the cell look like a button) and they would see ther selections then the top page would go hidden.

I assume some combination of If Column A then Vlookup or something might work, but another consideration is if they accidentally selected items they didn't want how can they easily remove those, Or maybe on the second sheet make column A another validation that if they choose X then it removes that line, but then how could I get the other rows to "move up" as if that row was never there.

Also is there anyway to make the checkmark and the X show up as they should in the drop down instead of that funny looking u?

Thanks in advance for the help, and if there is something already out there that exists please let me know!

 

[CountTepes]

I don't think that you can do this without VBA. If anyone knows differently then please let us know.

However, The VBA code should be simple enough, that you will not need any functions which are not available on Office XP onward.
If you do have to use specific code, then there are the VBA compiler directives, so you can modify your code for versions.

VBA6, VBA7 as are VBA4 and VBA5 implicit constants in VBA which are true or false, depending on the version.

#IF VBA6 THEN ' Office XP to Office 2007

#ELSEIF VBA7 then ' Office 2010 onwards

#END IF

There is also the Application.Version which will let tell you the version of office you are running.

You can then write the code which works with VBA7

As for making sure that macros are enabled, I always use this solution.

When you have finished coding, create another worksheet and call it MacroTest
On that sheet, in big red letters, or however you want to do it, put in some text which says "TO USE THIS WORKBOOK YOU HAVE TO HAVE MACROS ENABLED"

In the workbook code area put in the code

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
Dim Sh as Worksheet
Sheet("Macrotest").Visible = XlSheetVisible
For each Sh in Me.Sheets
       If Sh.Name<>"MacroTest" then Sh.Visible = XlSheetHidden
Next

Also

Workbook_Open
Dim Sh as Worksheet
Sheet("Macrotest").Visible = XlSheetHidden
For each Sh in Me.Sheets
       If Sh.Name<>"MacroTest" then Sh.Visible = XlSheetVisible
Next

Now when you save the workbook, it will un-hide the sheet with the message and hide all the others.

If a user opens the workbook and doesn't have macros enabled, then all they will see is the warning page. If they have macros enabled, then the workbook open code runs and un-hides all the work sheets and hides the warning sheet

It's simple but effective.

 

[USFMD82]

This is good info, then I guess if we are going to do it in VBA I still need some help figuring out how to do that, any suggestions on where I can begin?

Thank you!

 

[CountTepes]

As it looks like you have a CreateBid button on sheet 1,the simple solution, is they chose their bids on sheet 1, then press CreateBid, which copies the bids across and hide the bid rows.
This code have on Sheet1 code area. If you haven't then create an ActiveX Command Button and rename it CreateBid

Sub CreateBid_Click
Dim RowNum as Long
Dim C as Range
RowNum==Sheet2.Cells("G200").End(XlUp).Row+1   ' This will give you the first Empty Row at the bottom of any list on Sheet2

Do
For Each C in Range("A3:A100")
     If not IsBlank(C.value) Then
             C,EntireRow.Copy  Sheets(Sheet2).Cells(RowNum,1)
             C.EntireRow.Hide = XlHidden
     End If
     RowNum=RowNum+1
Loop Until IsBlank(C.Offset(0,1)   '  Run down the list until "Item#" is empty

Sheet 2 Should now have Just the selected items to bid on.
If anything is there by mistake and you wish to remove it, then you need a Worksheet change event in the code area of Sheet2

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim C as Range
Application.EnableEvents=False   ' Stop this routine being activated by any changes it makes
If Target.Column=1 and IsBlank(Target) Then 
          set C = Sheets("Sheet1").Range("G:G:").Find What:=Target.Offset(0,1).Value   ' Find the Item Number you don't want on Sheet1
          C.EntireRow.Hide = XlVisible       '   and unhide it
          Target.EntireRow.Delete  ' Then delete it from Sheet2
End If
Application.EnableEvents = True  ' Re-enable events
End Sub

This should work, but I have not tested it. There is no Error checking, if for some reason it cannot find the item number on sheet 1
in which case the c.entirerow.hide will fail.

If there are errors, just put a trap into the code and step through looking in the immediates window at the values. They will usually tell you what is wrong.

 

[Peter_SSs]

Here is a suggestion that does not involve vba and should work for all Excel versions from Excel 2010 on.

1. Use "y" & "n" (or yes/no) instead of the ticks and crosses for the Data Validation for things they want to bid on - simpler.
   
   
2. Don't include the y/n on the second sheet. If they change their mind, they can go back to Sheet1 and change the y to n or just remove it to remove that item from the second sheet.

I have done this on a single sheet as you did with your sample but you should be able to adapt to 2 sheets. Post back if you want to follow this approach but need more help.

Formula in F3 is copied across and down as many rows as in the original table.

USFMD82 2020-02-20 1.xlsm
	A	B	C	D	E	F	G	H
2	Bid?	Item #	Item	State		Item #	Item	State
3	y	1	Widget1	TX		1	Widget1	TX
4		2	Widget2	AL		3	Widget3	MO
5	y	3	Widget3	MO		5	Widget5	TX
6		4	Widget4	MI		6	Widget6	FL
7	y	5	Widget5	TX		8	Widget8	FL
8	y	6	Widget6	FL		9	Widget9	CA
9		7	Widget7	GA
10	y	8	Widget8	FL
11	y	9	Widget9	CA
Sheet1

Cell Formulas
Range	Formula
F3:H11	F3	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$20)/($A$3:$A$20="Y"),ROWS(F$3:F3))),"")

 

[CountTepes]

Here is a suggestion that does not involve vba and should work for all Excel versions from Excel 2010 on....................

This looks interesting.
I must learn more about Excel functions, rater then keep reverting to VBA

 

[Peter_SSs]

This looks interesting.
I must learn more about Excel functions,

Cheers. I note that you have Excel 365 & if it is one that has the FILTER function then this task is even easier with this formula in F3 in my layout

=FILTER(B$3:B$20,$A$3:$A$20="y")

and just copy that formula across to G3 & H3. All the results shown above should just "spill" down to the other cells.