1

I have a spread sheet that I have 2 drop down sets. once I get the results from both sets of drop downs I then need to update the values on the 2 items.

drop down 1 (a1) drop down 2 (b1) result1 (c1) (c1) = data in sheet2 column3 row x column 1 and 2 are for the drop downs. I would like to either enter a new value in (c1) or if that cant be done new result goes in (d1) and then make a button or something in f1 that can cause the new value in D1 to be updated in shee2 where (c1) was pointing to.

either i am searching with the wrong terms or something as I can't find any suggestions on how to do so from google.

Update: Switch and port would be dropdowns old/current cube would be retrieved data new cube would be the editable field and would put data in the current cube's source cell. old cube's source cell would be emptied.

Switch  Port    Old Cube    Switch  Port    Current New cube    Vlan
1stwest   2        b        1steast  1         x     2114        15     [ Update ]

sheet 2

switch     port   cube    vlan .....
1stwest     1      a
1stwest     2      b
1steast     1      x
1steast     2      y

I am still in the development stage on how I want to arrange all these things. I was planning on having the 1st sheet have the update button and a clickable network diagram. if you clicked on each switch picture it would take you to the sheet that is related to that switch. the first 4 to 40 lines would be a mock up of the switch ports that would use vlan column for conditional formatting and cube for labeling.

for the drop downs it would be nice to have switch drop down select data of sheet .... and then port vlan etc be used from which ever sheet was selected in the switch section. I am also not opposed to making the last sheet data only with column A being the switch B being the port C being cube D being vlan etc. and then using the specific switch sheets to pull data from that master list page.

  • 1
    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you. – DavidPostill Mar 6 '17 at 21:56
0

Since C1 is a result, you won't be able to enter data there with out breaking your spreadsheet.

You can enter data in D1, and use a macro to insert it into the Sheet 2 lists. The macro can be assigned to a button, which you can position over cell F1.

Drop downs

I'm assuming your dropdowns use Data Validation, with reference to a range in Sheet 2.

Macro

The steps to make your macro are:

  1. Get things set up right, with new data in E1, but have a different cell selected
  2. Hit 'record macro' on the developer tab
  3. With 'relative references' off, select and copy E1
  4. go to your list on Sheet 2, and select a cell in the middle of the lookup range.
  5. right click, insert cell, shift down if list is a column, paste data if it doesn't appear by itself
  6. go back to Sheet 1 Developer tab 'stop recording'

Button

Test the macro a few times, then search Google for a picture of a button.

Put button image in appropriate place, right click on it and assign your macro.

  • it should be updating the contents of the corresponding cell rather than putting a new cell in the sheet. it needs to know what originating cell c1 gets it's data from and update that specific cells value. – Kendrick Mar 7 '17 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.