Display data reference from another worksheet based on cell reference

So here is how I see your problem:

- User selects the discount percentage for the company manually from a list then moves down to the quote chart, and selects how many assets you are quoting for manually

discount_pct = myMainWs.range(dsctPctMenuRange).value '10%, 15%, 20%, ...
nbr_asset = myMainWs.range(nbrAssetMenuRange).value 1-9, 10-19, 20-29, ...

- based on the discount rate specified, and the number of assets selected for that row, go to the appropriate worksheet (based on the discount), and then take the pricing from the proper asset number row and produce this data in the "Quote" worksheet
'Lets suppose the individual worksheets are named [10 + 1-9], [10 + 10-19], ..., [20 + 20-29]
'then let's find the worksheet name

appropriateWsName = discount_pct & " + " & nbr-asset

'Get the worksheet now that we know its name

set appropriateWs = thisWorkbook.sheets(appropriateWsName)

'get that Ws

appropriateWs.Activate

' and access the right pricing
' We assume here that all worksheet are built with information at the same location on each sheet or you would need a mapping table and a more complex solution
' so on each worksheet, asset-number 1-9 would be in cell [B5], asset number 10-19 would be in cell [B6}, etc

select case nbr-asset
case "1-9" : nbr-asset-range="B5"
case "10-19" : nbr-asset-range="B6"
case "20-29" : nbr-asset-range="B7"
end select

' then copy its price from the appropriateWs to the QuoteWs

quoteWs.range(myCellRange)=appropriateWs.range(nbr-asset-range).value

Originally Posted by JYHemlin

So here is how I see your problem:

- User selects the discount percentage for the company manually from a list then moves down to the quote chart, and selects how many assets you are quoting for manually

- based on the discount rate specified, and the number of assets selected for that row, go to the appropriate worksheet (based on the discount), and then take the pricing from the proper asset number row and produce this data in the "Quote" worksheet
'Lets suppose the individual worksheets are named [10 + 1-9], [10 + 10-19], ..., [20 + 20-29]
'then let's find the worksheet name

'Get the worksheet now that we know its name

'get that Ws

' and access the right pricing
' We assume here that all worksheet are built with information at the same location on each sheet or you would need a mapping table and a more complex solution
' so on each worksheet, asset-number 1-9 would be in cell [B5], asset number 10-19 would be in cell [B6}, etc

' then copy its price from the appropriateWs to the QuoteWs

Appreciate the reply, I do apologize however as I'm new to this coding and don't follow your example that well.

For reference: the sheets are labelled easily based on the discount and the names are as follows: Base, 12.5%, 15%, 17.5%, 20%, 25%, 30%, 40%.

Now that I've dug into more code, I can define more simply what I need.

- I have a worksheet called "Company". It has the company names listed horizontally, with the discount % they qualify for above them. I have the macro searching the range to match the company name with what is selected for the quote, however how do I have it reference the cell above the name once it locates the proper cell range?

- Once the discount is selected, how do I code what worksheet the macro needs to move to in order to find the pricing? I'm guessing I will have to define the discount % it finds in the previous step above as a DIM...and then have the worksheet name to move to as that DIM?

- Once in the proper discount worksheet, how do I have the range search based on the number of assets selected on the initial "Quote" worksheet?

Okay, I've got the base of it working well (I set up reference cells to display copied information to ensure it's picking up the proper stuff).

I'm having issues grabbing the pricing based off the asset. heres my current code (definitely not pretty)

Please Login or Register  to view this content.